Hi,
I am increasingly needing to do some more complex finds involving
several table associations. I can usually find an SQL solution, but
find it hard to think these out using ActiveRecord find techniques. I
guess I am thinking in SQL terms, when perhaps there is a way of
thinking in ActiveRecord terms. Here is an example, I am guessing
this can be done without using find_by_sql
Repair has_many :notes
SELECT * FROM repairs
where exists
(select * from notes where repairs.id=notes.repair_id
and and notes.flagged)
Thanks
Tonypm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 14 Feb 2009, at 11:33, tonypm wrote:> > Hi, > > I am increasingly needing to do some more complex finds involving > several table associations. I can usually find an SQL solution, but > find it hard to think these out using ActiveRecord find techniques. I > guess I am thinking in SQL terms, when perhaps there is a way of > thinking in ActiveRecord terms. Here is an example, I am guessing > this can be done without using find_by_sql > > Repair has_many :notes > > SELECT * FROM repairs > where exists > (select * from notes where repairs.id=notes.repair_id > and and notes.flagged) >Well at a very basic level you could do Repair.find :all, :conditions => "exists (select ...)" but that doesn''t gain you much. You could write Repair.find :all, :select => ''distinct repairs.*'', :joins => :notes, :conditions => ["flagged = ?", true] or something along those lines. Fred> Thanks > Tonypm > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
tonypm wrote:> Repair has_many :notes > > SELECT * FROM repairs > where exists > (select * from notes where repairs.id=notes.repair_id > and and notes.flagged)r = Repair.find(...) notes = r.notes.find_by_flagged(true) I can''t think of a way to learn how deep the ActiveRecord DSL gets, besides read read read blogs, tutorials, books, and its documentation! I myself probably know only 30% of it! -- Phlip --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:>> Repair has_many :notes >> >> SELECT * FROM repairs >> where exists >> (select * from notes where repairs.id=notes.repair_id >> and and notes.flagged)I forgot my basic SQL! I think that''s just SELECT * FROM repairs r, notes n WHERE r.id = n.repair_id AND n.flagged = 1 right? Repair.all( :include => :notes, :conditions =>{ ''notes.flagged'' => true } ) "Find all repairs with any flagged notes". And I thought AR would handle distinct-ing that. So how to do a sub-select if you indeed need one? But my other answer lets you trivially walk back from the notes to the repairs: r.notes.find_by_flagged(true).map(&:repair) Warning: A map{} that rips another model like that can grow inefficient! -- Phlip --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Fred,
Repair.find :all, :select => ''distinct repairs.*'', :joins
=> :notes, :conditions => ["flagged = ?", true]
nice - many thanks. just needed to fix "notes.flagged"
--------------------------------------
In reality I have a generic notes model, so my usage is slightly more
complex:
In Repair I have:
has_many :repair_notes, :foreign_key=>''note_for''
has_many :flagged_repair_notes, :class_name=>"RepairNote",
:foreign_key=>''note_for'',
:conditions=>{:flagged=>true}
Then:
Repair.find :all, :select => ''repairs.*'', :joins
=> :repair_notes, :conditions => ["notes.flagged = ?", true]
Creates lovely sql
SELECT repairs.* FROM `repairs` INNER JOIN `notes` ON notes.note_for repairs.id
AND (`notes`.`type` = ''RepairNote'' ) WHERE (notes.flagged 1)
I wonder if there is a way to use the flagged_repair_notes in the
find for flagged repairs.
ps.
I have real admiration for the guys who do the ActiveRecord SQL
generation magic.
Thanks to all
Tonypm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Just to finish the story. Sorry - the distinct in the select is needed, I missed it out. Going a step further, I now have: named_scope :flagged_repairs, :select => ''distinct repairs.*'', :joins => :repair_notes, :conditions => ["notes.flagged = ?", true] So in my search, where I am building a dynamic scope (thanks to railscasts) I can do: scope.flagged_repairs.paginate(:page=>page, :per_page=>per_page) And it all appears to work!! Incredibly neat Tonypm --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
named scope is definitely the way to go. check out http://guides.rubyonrails.org/active_record_querying.html#_named_scopes for more options (especialy named scope with argument) On 14 Ún, 13:32, tonypm <tonypmar...-PkbjNfxxIARBDgjK7y7TUQ@public.gmane.org> wrote:> Just to finish the story. > > Sorry - the distinct in the select is needed, I missed it out. > > Going a step further, I now have: > > named_scope :flagged_repairs, :select => ''distinct repairs.*'', :joins > => :repair_notes, :conditions => ["notes.flagged = ?", true] > > So in my search, where I am building a dynamic scope (thanks to > railscasts) > I can do: > > scope.flagged_repairs.paginate(:page=>page, :per_page=>per_page) > > And it all appears to work!! > > Incredibly neat > > Tonypm--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---