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”])