Clever Geek Handbook

When should you make an active recording?

I'm not new to Rails, but I haven't done anything that hard so far, so I'm curious what a more experienced rails developer might share:

I have a sql query that concatenates 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 relationships with tables defined in different models and initially it was fetching different data only using those specific relationships (using active record) and it worked fine except for the main query, which is the largest, was illogical slow. performing several incl. requests. My question is, should I reset Active Record in this case and use find_by_sql ... or am I missing something?

What is Rails?

thank

+2


source to share


4 answers


I actually didn't like these answers, so I messed up a bit and got lucky ...

I like it 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 good rail gears to get parts I don't explicitly need ... i.e. less than two connections!



Although ... I'm wondering if there is a way to simplify it? The relationship is defined between scanned and locations ... i.e. has_many, belongs ... so why do I still need to specify INNER JOIN ON? should not

:joins => :scannable work?

anyway .. he didn't like it ...

+4


source


It's more important to be pragmatic than worrying about keeping Rails "clean". I love the named areas, the associations and all the magic that goes with them. Of course, I prefer that the raw SQL query is executed. However, if a complex query calls find_by_sql, I will use it without losing sleep.



If you need to support multiple databases, you may need to translate this into a pure Rails approach, but this requirement is quite rare.

+11


source


Using

:joins => :scannables

should work (note the plural), assuming you created the association Place # has_many: scannables. The: joins option for #find accepts both strings and named associations.

+2


source


You might want to use sequel if you have multiple queries like this. Otherwise I agree with jdl.

0


source






More articles:


All Articles