call_list_ability.rb

class CallListAbility
  include CanCan::Ability

  def initialize(user)
    if user.admin
      can :manage, CallList, :account => { :id => user.account.id }
    else
      can :create, CallList

      # call list has one account through a campaign
      # call list has many users through it's assigned call
      # call list has one user (the manager) through the campaign
      # a user has many followers (followers is polymorphic association with a thing and a user)
      # call list has many followers through the campaign
      
      # Goal:
      # Find all the Call Lists that meet any of these conditions:
      # 1. The user is managing the call list (:user)
      # 2. The user is following the call list (:followers)
      # 3. The user is following the associated manager (:user => :followers)
      # 4. The user is assigned a call on the list (The :users association)
      #
      # Problem
      # The following join is generated incorrectly. It should generate once for :users => :followers
      # then generate a new join table when :followers is joined. The query is generated
      # essentially correct, but instead of using a unique join table for each conditions, it only
      # uses one join table. See the generated SQL.
      
      call_list_scope = CallList.includes(:account, :users).
        includes(:user => :followers).
        includes(:followers).
        where(:accounts => { :id => user.account.id })

      ors = %w(users.id campaigns.user_id followers_users.id followers_call_lists.id)
      ors = ors.collect {|c| "#{c} = :id" }.join(" OR ")

      call_list_scope = call_list_scope.where [ors, {:id => user.id}]

      can :read, CallList, call_list_scope do |call_list|
        call_list_scope.exists? :id => call_list.id
      end

      can :update, CallList, :user => { :id => user.id }
      can :destroy, CallList, :user => { :id => user.id }
    end
  end
end

generated_query.sql

SELECT "call_lists"."id" AS t0_r0, "call_lists"."description" AS t0_r1, "call_lists"."finish_by" AS t0_r2, "call_lists"."campaign_id" AS t0_r3, "call_lists"."created_at" AS t0_r4, "call_lists"."updated_at" AS t0_r5, "accounts"."id" AS t1_r0, "accounts"."name" AS t1_r1, "accounts"."created_at" AS t1_r2, "accounts"."updated_at" AS t1_r3, "accounts"."public" AS t1_r4, "accounts"."hash_key" AS t1_r5, "accounts"."avatar" AS t1_r6, "users"."id" AS t2_r0, "users"."name" AS t2_r1, "users"."email" AS t2_r2, "users"."phone" AS t2_r3, "users"."time_zone" AS t2_r4, "users"."account_id" AS t2_r5, "users"."created_at" AS t2_r6, "users"."updated_at" AS t2_r7, "users"."api_key" AS t2_r8, "users"."locale" AS t2_r9, "users"."public" AS t2_r10, "users"."settings" AS t2_r11, "users"."provider" AS t2_r12, "users"."uid" AS t2_r13, "users"."avatar" AS t2_r14, "users_call_lists"."id" AS t3_r0, "users_call_lists"."name" AS t3_r1, "users_call_lists"."email" AS t3_r2, "users_call_lists"."phone" AS t3_r3, "users_call_lists"."time_zone" AS t3_r4, "users_call_lists"."account_id" AS t3_r5, "users_call_lists"."created_at" AS t3_r6, "users_call_lists"."updated_at" AS t3_r7, "users_call_lists"."api_key" AS t3_r8, "users_call_lists"."locale" AS t3_r9, "users_call_lists"."public" AS t3_r10, "users_call_lists"."settings" AS t3_r11, "users_call_lists"."provider" AS t3_r12, "users_call_lists"."uid" AS t3_r13, "users_call_lists"."avatar" AS t3_r14, "followers_users"."id" AS t4_r0, "followers_users"."name" AS t4_r1, "followers_users"."email" AS t4_r2, "followers_users"."phone" AS t4_r3, "followers_users"."time_zone" AS t4_r4, "followers_users"."account_id" AS t4_r5, "followers_users"."created_at" AS t4_r6, "followers_users"."updated_at" AS t4_r7, "followers_users"."api_key" AS t4_r8, "followers_users"."locale" AS t4_r9, "followers_users"."public" AS t4_r10, "followers_users"."settings" AS t4_r11, "followers_users"."provider" AS t4_r12, "followers_users"."uid" AS t4_r13, "followers_users"."avatar" AS t4_r14, "followers_call_lists"."id" AS t5_r0, "followers_call_lists"."name" AS t5_r1, "followers_call_lists"."email" AS t5_r2, "followers_call_lists"."phone" AS t5_r3, "followers_call_lists"."time_zone" AS t5_r4, "followers_call_lists"."account_id" AS t5_r5, "followers_call_lists"."created_at" AS t5_r6, "followers_call_lists"."updated_at" AS t5_r7, "followers_call_lists"."api_key" AS t5_r8, "followers_call_lists"."locale" AS t5_r9, "followers_call_lists"."public" AS t5_r10, "followers_call_lists"."settings" AS t5_r11, "followers_call_lists"."provider" AS t5_r12, "followers_call_lists"."uid" AS t5_r13, "followers_call_lists"."avatar" AS t5_r14 FROM "call_lists" 

LEFT OUTER JOIN "campaigns" ON "campaigns"."id" = "call_lists"."campaign_id" 
LEFT OUTER JOIN "accounts" ON "accounts"."id" = "campaigns"."account_id" 
LEFT OUTER JOIN "todos" ON "todos"."call_list_id" = "call_lists"."id" 
LEFT OUTER JOIN "users" ON "users"."id" = "todos"."user_id" 
LEFT OUTER JOIN "campaigns" "campaigns_call_lists_join" ON "campaigns_call_lists_join"."id" = "call_lists"."campaign_id" 
LEFT OUTER JOIN "users" "users_call_lists" ON "users_call_lists"."id" = "campaigns_call_lists_join"."user_id" 
LEFT OUTER JOIN "followings" ON "followings"."followable_id" = "users_call_lists"."id" AND "followings"."approved" = 't' AND "followings"."followable_type" = 'User' LEFT OUTER JOIN "users" "followers_users" ON "followers_users"."id" = "followings"."user_id" 
LEFT OUTER JOIN "campaigns" "campaigns_call_lists_join_2" ON "campaigns_call_lists_join_2"."id" = "call_lists"."campaign_id" 
LEFT OUTER JOIN "followings" "followings_call_lists_join" ON "followings_call_lists_join"."followable_id" = "campaigns_call_lists_join_2"."id" AND "followings_call_lists_join"."followable_type" = 'Campaign' 
LEFT OUTER JOIN "users" "followers_call_lists" ON "followers_call_lists"."id" = "followings_call_lists_join"."user_id" AND "followings"."approved" = 't' 

WHERE 
  "accounts"."id" = 1 AND (xsers.id = 2 OR campaigns.user_id = 2 OR followers_users.id = 2 OR followers_call_lists.id = 2)

gistfile1.txt

Here is the fix:

Replace the last LEFT OUTER JOIN with:

LEFT OUTER JOIN "users" "followers_call_lists" ON "followers_call_lists"."id" = "followings_call_lists_join"."user_id" AND "followings_call_lists_joins"."approved" = 't'

user.rb

# PROBLEM: conditions set in the association are not translated to a join table when used
has_many :followers, :through => :followings, :class_name => "User", 
      :conditions => { :followings => { :approved => true }}, :source => :user

# FIX
has_many :followers, :through => :followings, :class_name => "User", :source => :user