Rails - Find records that are not present another join table

I'm trying to write a ActiveRecord statment where I'm looking for all records where the id isn't presen开发者_Go百科t in another table...

Whats the syntax?

 @events =  Event.find(:all, :include => :personals, 
                  :conditions => ["event.id != ? ", @user.personal.event_id ])

Where personals is a join table that has the user_id and a event_id....

SO i'm essentially trying to find every event record that the user hasn't added to their own set of personal records....

Is there a better way to write this.... not null or something?

The other answers don't scale well with hundreds of thousands or more records. Here's a pure SQL solution.

Rails 5+:

Event.left_outer_joins(:personals).where(personals: {event_id: nil})

This retrieves all Events that do not have an associated Personal.

Rails 3+:

Event.joins('LEFT JOIN personals ON event.id = personals.event_id').where('personals.event_id IS NULL')

The solution presented by Nuby is as correct as ever. Here's the same query in a modern syntax, which can be used in Rails 3 and higher:

Event.where.not(id: @user.event_ids)

I assume the relationships are as follows:

User: 
has_many :personals
has_many :events, :through => :personals

Event:
has_many :personals
has_many :users, :through => :personals

Personal:
belongs_to :event
belongs_to :user

My solution would be to use the auto-created association function for User "event_ids" that lists all of the event ids in User.events. The query to get what you're trying to do should be:

Event.find(:all, :include => :personals, :conditions => ["events.id NOT IN (?)", @user.event_ids])

And, unless you actually need the join, you can simplify:

Event.find(:all, :conditions => ["events.id NOT IN (?)", @user.event_ids])

The key is the SQL command, "NOT IN (x)". Also, note the "events.id" is plural for the model name, according to SQL convention.

Hope this helps...

Best I've got so far:

events = Event.left_joins(:personals)
events.where.not(personals: { event_id: @user.personal.event_id }).or(
  events.where(personals: { event_id: nil })
)

Produces SQL:

SELECT "events".* FROM "events" 
LEFT OUTER JOIN "personals" 
ON "personals"."event_id" = "events"."id" 
WHERE ("personals"."event_id" != $1 OR "personals"."event_id" IS NULL)