Find some users who are NOT in this group

activerecord_scoped_query1.rb

#ActiveRecord scoped query left outer join users to members
  scope :not_in_group, lambda { |group|  
      join_clause = User.send(:sanitize_sql_array, 
        ["LEFT OUTER JOIN memberships ON memberships.user_id = users.id WHERE memberships.group_id is not ?", group ])  
        User.select("distinct(users.id), users.*").joins(join_clause)
  }

I might be oversimplifying, but it should be a little easier:

User.find(:all, :conditions => ["group_id != ?", 1], :joins =>[:groups])

Thanks! That would be much simpler. I will try and let ya know.

I re-read your tweet and saw it was a has_many :through, and that's based on a has and belongs to many, so it might not work.

OK, so it doesn't seem to work for what I need.

Your query produces an inner join on memberships.
SELECT "users".* FROM "users" INNER JOIN "memberships" ON "users"."id" = "memberships"."user_id" INNER JOIN "groups" ON "groups"."id" = "memberships"."group_id" WHERE (group_id != 1)

Mine produces a left outer join, which is what I believed I needed.
SELECT distinct(users.id), users.* FROM "users" LEFT OUTER JOIN memberships ON memberships.user_id = users.id WHERE memberships.group_id <> 1 or memberships.group_id is null

My intent was to list all users who are not already members of this group and include members who are not in a group at all.

Thanks again for the suggestion.

Heh. Would have helped for me to read your second comment too :-) No worries. I'm just happy that I'm finally getting past all the basic Rails/ActiveRecord stuff and now actually modelling some useful data.

Ah the "who are not in a group at all." changes it quite a bit, so you will need the left join, you're quite right :)