Find records not in join with has_many_and_belongs_to


#1

I have a User and Topic model. A user subscribes to a topic, so there
is a
many-to-many relationship between User and Topic. So my User model
object
is using has_many_and_belongs_to :topics and vice versa. I want to find
all
the topics that a user has not subscribed to. This is what I’ve got:

@user = User.find(params[:id])
@topics = Topic.find(:all,
  :conditions => ["id not in (select topic_id from topics_users 

where
user_id = ?)", @user.id])

I’ve been told that this kind of NOT IN (SUBQUERY) is an inefficient way
of
getting the data, and that using a outer join with an “is null” criteria
is
the way to go. So now I’ve got this:

@user = User.find(params[:id])
@topics = Topic.find(:all,
  :joins => "LEFT OUTER JOIN topics_users ON topics.id =

topics_users.topic_id "+
“AND topics_users.user_id = #{@user.id}”,
:conditions => [“topics_users.topic_id is null”])

Is there a cleaner way of doing this? First of all, the biggest
ugliness of
it is the fact that you have to embed the user.id in join string. It
doesn’t open up a SQL injection vunerability in this case, but it would
be
nice to be able to do this:

@user = User.find(params[:id])
@topics = Topic.find(:all,
  :joins => ["LEFT OUTER JOIN topics_users ON topics.id =

topics_users.topic_id "+
“AND topics_users.user_id = ?”, @user.id],
:conditions => [“topics_users.topic_id is null”])

This would be nice too, to get rid of some of the SQL and be able to
specific a table to outer join and then send it some conditions:
@user = User.find(params[:id])
@topics = Topic.find(:all,
:outer_joins => [“topic_users”, [“user_id = ?”, @user.id]],
:conditions => [“topics_users.topic_id is null”])


#2

Paul B. wrote:

I have a User and Topic model. A user subscribes to a topic, so there
is a many-to-many relationship between User and Topic. So my User model
object is using has_many_and_belongs_to :topics and vice versa. I want to find
all the topics that a user has not subscribed to.

Unless you have like a kajillion topics and there are performance
problems, it’s far easier to do this in Ruby code.

non_topics = Topic.find(:all) - user.topics


Josh S.
http://blog.hasmanythrough.com