Is there an ActiveRecord way to do this SQL query?

Question

Have Addresses and Lists with many-to-many relationship, as shown below.

Sometimes need all the Lists an Address is not in.

Using the find_by_sql query shown, and it works great. But is there a way to do it without using direct SQL?

class List
  has_many :address_list_memberships
  has_many :addresses, :through => :address_list_memberships
end


class Address
  has_many :address_list_memberships, :dependent => :destroy
  has_many :lists, :through => :address_list_memberships

  # Lists that this Address is not in
  def Address.lists_not_in(address_id)
    sql = %Q|
SELECT
  l.*
FROM
  lists l
WHERE
  l.id
NOT IN
(
  SELECT
    l.id
  FROM
    addresses a, lists l, address_list_memberships alm
  WHERE
    a.id = alm.address_id AND l.id = alm.list_id
  AND
    a.id = #{address_id}
)
|
    List.find_by_sql(sql)
  end
end

Answer 1

I would do this as a scope in List

class List
  named_scope :without_address, lambda { |address_id| { :joins => 'inner join address_list_memberships alm on alm.list_id = lists.id', :conditions => ['alm.address_id <> ?', address_id]}}
end

Now you can call List.without_address(4), and you can call scopes on top of that.

As Matchu points out, you can do it without writing out the join SQL:

class List
  named_scope :without_address, lambda { |address_id| { :joins => :address_list_memberships, :conditions => ['address_list_memberships.address_id <> ?', address_id]}}
end

And make sure your join table has indices!

In a migration:

add_index "address_list_memberships", "address_id"
add_index "address_list_memberships", "list_id"

For other ways you can format the named_scope, see Sam Saffron's gist: http://gist.github.com/162489

Answer 2

WHERE (address_list_memberships.address_id <> 13896)

is going to be expensive on a database with 21849 Addresses and 1483 Lists.

Flip your logic:

def lists_not_in
  List.all - self.lists
end

That way you are only subtracting one array from another instead of checking each record in the database to see if it's in the list.

Answer 3

You are not going to get the flexibility you get with direct SQL from ActiveRecord, in particular, it is not going to be possible for you to craft the not in clause in active record.

If you want to get a little bit more control you could try using Sequel http://sequel.rubyforge.org/ or just hand crafting.

Note, the solution you have is risky cause you are allowing for a sql injection. (a.id = #{address_id})