Rails has_many conditions

Question

    c = "(f.profile_id = #{self.id} OR f.friend_id = #{self.id})"
    c += AND + "(CASE WHEN f.profile_id=#{self.id} THEN f.friend_id ELSE f.profile_id END = p.id)"
    c += AND + "(CASE WHEN f.profile_id=#{self.id} THEN f.profile_rejected ELSE f.friend_rejected END = 1)"
    c += AND + "(p.banned = 0)"

I need this to be used in a has_many relationship like this:

    has_many :removed_friends, :conditions => ???

how do i set there the self.id?, or how do i pass there the id? Then i want to use the will_paginate plugin:

    @profile.removed_friends.paginate(:page => 1, :per_page => 20)

Thanks for your help

EDIT:

 class Profile < ActiveRecord::Base
    has_many :friendships
    has_many :removed_friends, :class_name => 'Profile', :through => :friendships, :conditions => 
        "(friendships.profile_id = #{self.id} OR friendships.friend_id = #{self.id})"
        "AND (CASE WHEN friendships.profile_id=#{self.id} THEN friendships.profile_rejected ELSE friendships.friend_rejected END = 1)" + 
        "AND (p.banned = 0)"
  end


class Friendship < ActiveRecord::Base
  belongs_to :profile
  belongs_to :removed_friend, :class_name => 'Profile', :foreign_key => "(CASE WHEN friendships.profile_id = #{self.id} THEN friend_id ELSE profile_id END)"
end

Answer 1

Use single quotes to enclose the condition:

class Profile < ActiveRecord::Base
  has_many :friendships
  has_many :removed_friends, :class_name => 'Profile', :through => :friendships, 
                             :conditions => '
    ( friendships.profile_id = #{self.id} OR 
      friendships.friend_id = #{self.id}
    ) AND
    (CASE WHEN friendships.profile_id=#{self.id} 
          THEN friendships.profile_rejected 
          ELSE friendships.friend_rejected 
     END = 1
    ) AND 
    (p.banned = 0)'
end

Answer 2

You might want to break this down into a series of named scopes that can be applied in stages instead of all at once. As an example, extract the banned part:

class Friend < ActiveRecord::Base
  named_scope :banned, lambda { |*banned| {
    :conditions => { :banned => banned.empty? ? 1 : (banned.first ? 1 : 0) }
  }}
end

@profile.friends.removed.banned(false).paginate(:page => 1, :per_page => 20)

Using heavy-duty conditions in relationships is bound to cause trouble. If possible, try denormalizing the table, creating derivative columns that have "easy" versions of the data, or other things to make querying it easier.

Answer 3

You really have two relationships here. You have:

• A rejected friendship from the profile_id side
• A rejected friendship from the friend_id side

I don't know why both sides can reject a friendship, and maybe you need to look at your model for a little bit here (which side is requesting it? Would it be better to consider that the requestor CANCELLED the request instead of saying it was rejected from the profile side?)

At any rate, I would model this as the two separate relationships that they are:

class Profile
  has_many :rejected_friendships, :conditions => 'friendships.profile_rejected = 1'
  has_many :canceled_friendships, :foreign_key => 'friend_id', :conditions => 'friendships.friend_rejected = 1'

  named_scope :banned, lambda do |*banned| 
      { :conditions => {:banned => banned.empty? ? 1 : (banned.first ? 1 : 0) } }
  end

  has_many :rejected_friends, :class_name => 'Profile', :through => :rejected_friendships
  has_many :canceled_friends, :class_name => 'Profile', :through => :canceled_friendships

  def removed_friends
    (self.rejected_friends.banned(false).all + self.canceled_friends.banned(false).all).uniq
  end
end

This is somewhat undesirable because removed_friends is not a relationship anymore so you can't do things like Profile.removed_friends.find(:all, :conditions => {:name => "bleh"}) anymore, but this is a pretty complicated case. That condition is quite complex.