Forum: Ruby on Rails can this be done without using find_by_sql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
tonypm (Guest)
on 2009-02-14 13:34
(Received via mailing list)
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
Frederick C. (Guest)
on 2009-02-14 13:45
(Received via mailing list)
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
Phlip (Guest)
on 2009-02-14 13:46
(Received via mailing list)
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
Phlip (Guest)
on 2009-02-14 13:54
(Received via mailing list)
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
tonypm (Guest)
on 2009-02-14 14:21
(Received via mailing list)
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
tonypm (Guest)
on 2009-02-14 14:33
(Received via mailing list)
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
DarkTatka (Guest)
on 2009-02-15 01:21
(Received via mailing list)
named scope is definitely the way to go. check out
http://guides.rubyonrails.org/active_record_queryi...
for more options (especialy named scope with argument)
This topic is locked and can not be replied to.