Optimizing an ActiveRecord query

Question

I have the following models and want to get all the available posts that an specific user has not put a like on them.

Post Model:

class Post < ActiveRecord::Base
  has_many :likes
  has_many :users, through: :likes

  scope :available, -> { where available: true }
end

Like Model:

class Like < ActiveRecord::Base
  belongs_to :post
  belongs_to :user
end

User Model:

class User < ActiveRecord::Base
  has_many :likes
  has_many :posts, through: :likes
end

I came up with this ActiveRecord query:

Post.available - Post.available.joins(:likes).where('likes.user_id = ?', user.id)

Is there an optimized way to achieve this? Maybe even an equivalent SQL query?

Answer 1

This can be achieved with:

Post.available.where("id not in (select post_id from likes where user_id = ?)", user.id)

Answer 2

That could do it, I guess:

Post.available.where( 'NOT EXISTS (?)',
  user.likes.where('likes.post_id = posts.id')
)

You can also do this without SQL strings at all, like so:

Post.available.where(
  user.likes.where(post_id: Post.arel_table[:id]).exists.not
)

But I've seen constructs like this cause issues with parameter binding. I'm pretty sure it's a bug, but I'm not sure this trick is even in public ActiveRecord API (an so is ActiveRecord/Arel really to blame).