# Can this be done without using find\_by\_sql **URL:** \ **Category:** Rails\ **Created:** [February 14, 2009, 12:34pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322 "2009-02-14T12:34:21Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 12:34pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/1 "2009-02-14T12:34:21Z")
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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 12:45pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/2 "2009-02-14T12:45:29Z")
On 14 Feb 2009, at 11:33, tonypm wrote: > 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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 12:46pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/3 "2009-02-14T12:46:05Z")
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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 12:54pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/4 "2009-02-14T12:54:18Z")
Frederick C. 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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 1:21pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/5 "2009-02-14T13:21:19Z")
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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 14, 2009, 1:33pm UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/6 "2009-02-14T13:33:37Z")
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 ---
**Author:** ![tonypm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a587f6/32.png) [@tonypm](https://www.ruby-forum.com/u/tonypm)\ **Post date:** [February 15, 2009, 12:21am UTC](https://www.ruby-forum.com/t/can-this-be-done-without-using-find-by-sql/160322/7 "2009-02-15T00:21:48Z")
named scope is definitely the way to go. check out > **[Active Record Query Interface — Ruby on Rails Guides](https://guides.rubyonrails.org/active_record_querying.html)** > > Active Record Query InterfaceThis guide covers different ways to retrieve data from the database using Active Record.After reading this guide, you will know: How to find records using a variety of methods and conditions. How to specify the order,... for more options (especialy named scope with argument)