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.