In Rails 3 how can I select items where the items.join_model.id != x?

In my Rails models I have:

class Song < ActiveRecord::Base
  has_many :flags
  has_many :accounts, :through => :flags
end

class Account < ActiveRecord::Base
  has_many :flags
  has_many :songs, :through => :flags
end

class Flag < ActiveRecord::Base
  belongs_to :song
  belongs_to :account
end

I'm looking for a way to create a scope in the Song model that fetches songs that DO NOT have a given account associated with it.

I'开发者_如何学Pythonve tried:

Song.joins(:accounts).where('account_id != ?', @an_account)

but it returns an empty set. This might be because there are songs that have no accounts attached to it? I'm not sure, but really struggling with this one.

Update

The result set I'm looking for includes songs that do not have a given account associated with it. This includes songs that have no flags.

Thanks for looking.

Am I understanding your question correctly - you want Songs that are not associated with a particular account?

Try:

Song.joins(:accounts).where(Account.arel_table[:id].not_eq(@an_account.id))

Answer revised: (in response to clarification in the comments)

You probably want SQL conditions like this:

Song.all(:conditions =>
  ["songs.id NOT IN (SELECT f.song_id FROM flags f WHERE f.account_id = ?)", @an_account.id]
)

Or in ARel, you could get the same SQL generated like this:

songs = Song.arel_table
flags = Flag.arel_table

Song.where(songs[:id].not_in(
  flags.project(:song_id).where(flags[:account_id].eq(@an_account.id))
))

I generally prefer ARel, and I prefer it in this case too.

If your where clause is not a typo, it is incorrect. Code frequently uses == for equality, but sql does not, use a single equals sign as such:

Song.joins(:accounts).where('account_id = ?', @an_account.id)

Edit:

Actually there is a way to use activerecord to do this for you, instead of writing your own bound sql fragments:

Song.joins(:accounts).where(:accounts => {:id => @an_account.id})