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.
Db7238007950074e9e73b76a81910406?d=identicon&s=25 tonypm (Guest)
on 2009-02-14 12: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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-02-14 12: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
Aafa8848c4b764f080b1b31a51eab73d?d=identicon&s=25 Phlip (Guest)
on 2009-02-14 12: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
Aafa8848c4b764f080b1b31a51eab73d?d=identicon&s=25 Phlip (Guest)
on 2009-02-14 12:54
(Received via mailing list)
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!

--
   Phlip
Db7238007950074e9e73b76a81910406?d=identicon&s=25 tonypm (Guest)
on 2009-02-14 13: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
Db7238007950074e9e73b76a81910406?d=identicon&s=25 tonypm (Guest)
on 2009-02-14 13: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
43ebaaf048f056d599a7218f834179ef?d=identicon&s=25 DarkTatka (Guest)
on 2009-02-15 00: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.