Can this be done without using find_by_sql

Rails
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

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

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!

–
Phlip

4

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

5

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

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

for more options (especialy named scope with argument)