can this be done without using find_by_sql

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

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

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!

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!

Fred,

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

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

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

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)