activerecord rails find pages who have no groups

Question

Hi guys.. I have a the following situation: pages has many groups.

Now i am trying to find the first page who hasn't got a group with activerecord. I tried things like this:

Page.find(:first, :joins => :groups, :select => 'DISTINCT `pages`.*')

but of course no luck so far.

Answer 1

One solution could be to add a counter_cache for the groups.

In Group.rb

belongs_to :page, :counter_cache => true

Then, you need to create a migration

def self.up
  add_column :pages, :groups_count, :integer, :default => 0

  Page.reset_column_information
  Page.find(:all).each do |p|
    Page.update_counters p.id, :groups_count => p.groups.length
  end
end

def self.down
  remove_column :pages, :groups_count
end

So, now you could do:

Page.first(:conditions => { :groups_count => 0 })

Answer 2

This is more an SQL problem than ActiveRecord. What you need is to join the 2 tables but select those who are not referenced in the join table.

Page.first(:include => :groups, :conditions => ["`#{Group.table_name}`.id IS NULL"]
# The opposite query would be
Page.first(:include => :groups, :conditions => ["`#{Group.table_name}`.id IS NOT NULL"]
# Note that this slightly different than just: 
Page.first(:include => :groups)
# This produces a IN query, rather than join.

Also :joins does not work because it creates a INNER JOIN, as opposed to OUTER JOIN which is required in this case.

Answer 3

Something like:

Page.find(:all, 
          :select => "pages.*, count(groups.page_id) group_count", 
          :joins => :groups, 
          :group => "pages.id having group_count = 0)