Menu
I'm trying to create a named_scope that uses a join, but although the generated SQL looks right, the result are garbage. For example:
class Clip < ActiveRecord::Base
named_scope :visible, {
:joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id",
:conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
}
(A Clip is owned by a Series, a Series belongs to a Show, a Show can be visible or invisible).
Clip.all does:
SELECT * FROM `clips`
Clip.visible.all does:
SELECT * FROM `clips` INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id WHERE (shows.visible = 1 AND clips.owner_type = 'Series' )
This looks okay. But the resulting array of Clip models includes a Clip with an ID that's not in the database - it's picked up a show ID instead. Where am I going wrong?
419
You can avoid most n+1 queries in rails by simply eager loading associations. Eager loading allows you to load all of your associations (parent and children) once instead of n+1 times (which often happens with lazy loading, rails' default).
What is ActiveRecord? ActiveRecord is an ORM. It's a layer of Ruby code that runs between your database and your logic code. When you need to make changes to the database, you'll write Ruby code, and then run "migrations" which makes the actual changes to the database.
There are actually three ways to do eager loading in Rails. use preload() to fetch the data with a separate db query (just one though, avoiding N+1 queries) use eager_load() to fetch data with one large query and an outer join. use includes() to dynamically find the best solution and preload or eager load the data.
The problem is that "SELECT *" - the query picks up all the columns from clips, series, and shows, in that order. Each table has an id column, and result in conflicts between the named columns in the results. The last id column pulled back (from shows) overrides the one you want. You should be using a :select option with the :joins, like:
named_scope :visible, {
:select => "episodes.*",
:joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id",
:conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
