can this be done without using find_by_sql

rubyonrails-talk
1

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

2

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

3

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!

4

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!

5

Fred,

Repair.find :all, :select => 'distinct repairs.*', :joins => :notes, :conditions => ["flagged = ?", true]

nice - many thanks. just needed to fix "notes.flagged"

6

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

7

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)