find and has_one relationships

[Rupert Voelcker]

I've got a bit stuck with using find in Ruby so was hoping for some
expert guidance as this is driving me nuts!

If I've got the following 2 models:

class Member < ActiveRecord::Base
has_one :site_account
end

class SiteAccount < ActiveRecord::Base
belongs_to :member
end

What this represents is a pile of members: some have site access and
some don't

I need to get at all the members who have site access and those that
don't and can't work out the correct find statements to use

If I go to an individual member using

m = Member.find(1)

I can see if they have a site account by checking

m.site_account.nil? # false if they have a site account and true if
they don't

However what I want is a list of all who don't hgave an account and I
don't understand why I can't seem to refer to site_account in a find
statement to get a list of all members who don't have a site account
without getting an error telling me that

Mysql::Error: Unknown column 'site_account' in 'where clause': SELECT *
FROM members WHERE (site_account). I'd asssumed that a clever join
statement would be constructed in the backgroud to let me do this but it
doen't seem to. I was hoping to use something along the lines of:

m = Member.find(:all, :conditions => what_goes_in_here???)

Am I trying something that's not possible or is it just that the syntax
is evading me? Any help hugely appreciated.

Cheers

Rupert

--
Posted via http://www.ruby-forum.com/.

[John Browning]

I'm not expert, but site_account is a table, not a column. And a standard join won't work because you're looking for stuff that's *not* in the table. Don't know if this will work, but perhaps something along the lines of :conditions => "id not in (select id from site_account)"

alternatively you might be able do it in rails with something like Member.find(:all).delete_if { |m| !m.site_account.nil? }

Hope this of some help...

..................................................................................

John Browning

[harper]

Article.with_scope(:find => { :conditions => "blog_id = 1 })

((taken from api.rubyonrails))

in the model of your choice, define the find method to select the
members of your choice, and walla. (you can use with_exclusive_scope if
you don't want to ruin the find method for that model)
it would be along the lines of

Member.with_scope(:find => {:conditions => 'site_account_id !=
nil/0/whatever-you-defined'})

or something like that.
hope it helps. check out the api docs, they are pretty self-explanitory.

harp

[Josh Susser]

Perhaps you have the foreign key in the wrong table. You could change
things so that:

class Member < ActiveRecord::Base
belongs_to :site_account
end

class SiteAccount < ActiveRecord::Base
has_one :member
end

Then it's easy to check member.site_account_id to see if it's nil. The
cost is having a site_account_id field in every member row, even for
those members that don't have a site account. And potentially it might
change how you do your joins significantly. But it's an option to
consider.

--
Josh Susser
http://blog.hasmanythrough.com

[Rupert Voelcker]

John Browning wrote:
> I'm not expert, but site_account is a table, not a column. And a
> standard join won't work because you're looking for stuff that's
> *not* in the table. Don't know if this will work, but perhaps
> something along the lines of :conditions => "id not in (select id
> from site_account)"
>
> alternatively you might be able do it in rails with something like
> Member.find(:all).delete_if { |m| !m.site_account.nil? }
>
> Hope this of some help...

That worked a treat - thanks John :)

....I tried to suss out the with_scope idea but I couldn't get it to do
it, so you've saved me from having to go down Josh's route which is wat
I reckon I would have had to have done had it not been for your
suggestion.

Thanks all!!