Forum: Ruby on Rails Find records not in join with has_many_and_belongs_to

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Paul B. (Guest)
on 2006-05-07 20:18
(Received via mailing list)
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"])
Josh S. (Guest)
on 2006-05-07 21:30
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
This topic is locked and can not be replied to.