Rails named_scopes with joins

Question

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?

Answer 1

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' "
}