Rails - May 2006 - Find records not in join with has_many_and_belongs_to

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"])
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060507/dccd922f/attachment.html

Paul Barry 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 Susser
http://blog.hasmanythrough.com

-- 
Posted via http://www.ruby-forum.com/.