Owners has_one :widget and Widgets belongs_to :owner. When creating a
new widget, I’m giving the option for the user to specify the owner by
using a drop down select. The select is populated by pulling a list of
owners.
What would be the best way to populate the select with owners who do not
have a widget?
I’m assuming my controller would look something like this for Widgets:
–
def new
@widget = Widget.new
@widgets = Widget.find(:all)
@owners = Owners.find(:all, :conditions => ‘…this is where I go
blank…’)
end
I’m assuming that it has to go in some kind of condition. Am I supposed
to run the owners find through some sort of block to validate for each
widget I’ve found in @widgets?
Thanks as always for any help in advance.
On Jan 12, 2010, at 9:46 AM, Steve C. wrote:
I’m assuming my controller would look something like this for Widgets:
I’m assuming that it has to go in some kind of condition. Am I
supposed
to run the owners find through some sort of block to validate for each
widget I’ve found in @widgets?
@owners = Owner.all(:conditions => ‘widget_id IS NULL’)
Would do it. Assuming that it’s correct to say that if widget_id is
null they have no widget – this wouldn’t catch instances where say an
owner had widget, the widget was removed and the owners widget_id
wasn’t set back to null.
@owners = Owner.all.reject{|o| o.widget.nil? }
Would also do it. This has the drawback that the DB is going to
return all the owners and then filter them in ruby. Won’t be as
speedy as the first option.
You probably want to add an :order option to whichever one you use to
get a consistent output.
-philip
Philip H. wrote:
@owners = Owner.all(:conditions => ‘widget_id IS NULL’)
Would do it. Assuming that it’s correct to say that if widget_id is
null they have no widget – this wouldn’t catch instances where say an
owner had widget, the widget was removed and the owners widget_id
wasn’t set back to null.
@owners = Owner.all.reject{|o| o.widget.nil? }
Would also do it. This has the drawback that the DB is going to
return all the owners and then filter them in ruby. Won’t be as
speedy as the first option.
You probably want to add an :order option to whichever one you use to
get a consistent output.
-philip
Thanks, Philip! I’ve yet to see the Owner.all vs. Owner.find(:all). Is
that just a shorthand way of saying the same thing?
Marnen Laibow-Koser wrote:
The proper SQL query would be
SELECT * from owners o left join widgets w on (w.owner_id = o.id)
WHERE w.id is null
Deriving an AR find from that is left as an exercise. 
Thanks for the homework! I’ll get to work on that.
Philip H. wrote:
On Jan 12, 2010, at 9:46 AM, Steve C. wrote:
I’m assuming my controller would look something like this for Widgets:
I’m assuming that it has to go in some kind of condition. Am I
supposed
to run the owners find through some sort of block to validate for each
widget I’ve found in @widgets?
@owners = Owner.all(:conditions => ‘widget_id IS NULL’)
No, you’ve got it backwards. Widget belongs_to :owner, so that won’t
work.
The proper SQL query would be
SELECT * from owners o left join widgets w on (w.owner_id = o.id)
WHERE w.id is null
Deriving an AR find from that is left as an exercise. 
Would do it. Assuming that it’s correct to say that if widget_id is
null they have no widget – this wouldn’t catch instances where say an
owner had widget, the widget was removed and the owners widget_id
wasn’t set back to null.
Nope.
@owners = Owner.all.reject{|o| o.widget.nil? }
Would also do it. This has the drawback that the DB is going to
return all the owners and then filter them in ruby. Won’t be as
speedy as the first option.
Yup. It will work, but it’s completely inappropriate to do this sort of
query in the app.
You probably want to add an :order option to whichever one you use to
get a consistent output.
-philip
Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Owner.find(:all). Is
that just a shorthand way of saying the same thing?
Yes. I haven’t looked, but my guess is it’s implemented as a
named_scope setup by default.
Marnen - Thanks for picking up on the snafu. Read it backwards
Steve C. wrote:
Thanks for the homework! I’ll get to work on that.
Finally got it to work! Here’s what I ended up with:
–
@owners = Owner.find_by_sql("SELECT * FROM Owners
LEFT JOIN Widgets
ON (Widgets.owner_id = Owners.id)
WHERE Widgets.owner_id IS NULL
")
I noticed that you used a letter to call the table later in the sql
query. Is that a shorthand method or something that I should do to make
it better syntax wise?
Eg. …
“SELECT * FROM Owners o
LEFT JOIN Widgets w
ON (w.owner_id = o.id)
WHERE w.owner_id IS NULL”
Steve C. wrote:
Wow. I just stumbled, (after I figured out what AR meant - sorry!) that
I could use :include inside of my find_by…
Rails surprises me everyday - I’m loving it. Thanks for the guidance.
I’m still trying to figure out who to put it to use. I’m assuming I’ll
have to pass in an :include and a :condition.
I think I figured it out.
@owners = Owner.find(:all, :include => :widgets, :conditions =>
‘Widgets.owner_id IS NULL’)
Now this seems to work, but can I refactor the :conditions at all?
Wow. I just stumbled, (after I figured out what AR meant - sorry!) that
I could use :include inside of my find_by…
Rails surprises me everyday - I’m loving it. Thanks for the guidance.
I’m still trying to figure out who to put it to use. I’m assuming I’ll
have to pass in an :include and a :condition.
On Tue, Jan 12, 2010 at 1:03 PM, Philip H. [email protected]
wrote:
back to null.
No, I don’t think that this will work given the database schema
described.
Widget belongs_to :owner
Owner has_one :widget
means that the widgets table has an owner_id colum , the owners table
doesn’t have a widget_id field.
The has_one relationship is really just a has_many which adds a limit
of 1 to the query.
@owners = Owner.all.reject{|o| o.widget.nil? }
Would also do it. Â This has the drawback that the DB is going to return
all the owners and then filter them in ruby. Â Won’t be as speedy as the
first option.
This will work, but as you point out it can be inefficient if there
are a lot of owners.
While it’s possible to get this down to a single SQL query with the
given db design, using an outer join and a carefully crafted
conditions clause, it might be simpler just to reverse the
relationship, as long as there is really a 1-1 cardinality
relationship, it could just as well be
Owner belongs_to :widget
Widget has_one :owner
with the concommittant db table changes.
Then
@owners = Owner.all(:conditions => ‘widget_id IS NULL’)
would work.
–
Rick DeNatale
Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
WWR: http://www.workingwithrails.com/person/9021-rick-denatale
LinkedIn: Rick DeNatale - Developer - IBM | LinkedIn