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)