when to ditch active record?

Question

I'm not new to Rails but I've not done anything quite so complicated as this so far, so I'm curious what a more experienced rails developer might share:

I have a sql query which joins 4 or so tables into one output:

SELECT places.id, places.name, places.email, places.website, places.blurb, external_ratings.rating, photos.data_file_name
    FROM `scannables` 
    INNER JOIN places ON scannables.place_id = places.id
    INNER JOIN locations ON places.location_id = locations.id
    LEFT JOIN external_ratings ON scannables.place_id = external_ratings.place_id
    LEFT JOIN photos ON scannables.place_id = photos.place_id
    WHERE locations.id = 2474 AND scannables.bookdate BETWEEN '2009-08-29' and date_add('2009-08-29', INTERVAL 4 DAY)
    GROUP BY scannables.place_id
    HAVING SUM(scannables.available) >= 4
    ORDER BY SUM(scannables.available) DESC, external_ratings.rating DESC

I have all the table relationships defined in the various Models, and originally had it pulling various data only using these defined relationships ( using active record ) and it worked fine except on the main query which is the largest was ungodly slow, executing multiple indv. queries. My question is, should I dump Active Record in this one case and use find_by_sql... or am I missing something?

What is the Rails way?

Thanks

Answer 1

It's more important to be pragmatic than to worry about maintaining "Rails purity." I love named scopes, associations, and all of the magic that goes with them. Of course, I prefer those to running a raw SQL query. However, if a complicated query calls for find_by_sql, then I'll use it without losing any sleep.

If you need to support multiple databases, then you might want to shoehorn this into a pure Rails approach, but that requirement is quite rare.

Answer 2

You may want to consider using sequel if you have several such queries. Otherwise, I agree with jdl.

Answer 3

Actually i didn't like those answers so i did a little digging and got lucky...

I like this much better....

Place.find(:all, 
     :joins => "INNER JOIN scannables ON scannables.place_id = places.id",
     :conditions => [ "places.location_id = ? and scannables.bookdate BETWEEN ? and ?", 2474, '2009-08-29', '2009-09-02' ],
     :group => 'scannables.place_id',
     :having => 'SUM(scannables.available) >= 4')

Its much simpler and I can still use all the nice rails machinery to get the parts that I don't need explicitly.. ie less two joins!

Although... i'm wondering if there's a way to simplify it more? The relationship is defined between scannables and places... ie has_many, belongs to... so why do I still need to specify the INNER JOIN ON? shouldn't

:joins => :scannable work?

anyway.. it didn't like that...

Answer 4

Using

:joins => :scannables

should work (note the plural), assuming you have set up a Place#has_many :scannables association. The :joins option for #find takes both strings and named associations.