Need help with sql condition/join

Rails
1

Hi,
I’m doing a search, where I have contacts, and also groups for those
contacts. The association is like this:

contacts:
has_many :groupers, :dependent => :destroy
has_many :groups, :through => :groupers

groups:
has_many :groupers
has_many :contacts, :through => :groupers

grouper:
belongs_to :group
belongs_to :contact

My pagination code looks like this currently:
if !selected_group_id
@contacts = @current_user.contacts.paginate(:all, :page =>
params[:page], :conditions => [‘name LIKE ?’, search_term], :order =>
order_text)
else
@contacts = @current_user.contacts.paginate(:all, :page =>
params[:page],
:join => :groupers,
:conditions => [“groupers.contact_id = contact.id AND
groupers.group_id = #{selected_group_id} AND name LIKE ?”, search_term],
:order => order_text)
end

This doesn’t do what I hoped, which is find everything where name is
like, and there is a grouper with this same contact id and same group
id. Clearly I’m an sql newbie and don’t know what I’m doing here. Any
help appreciated.

thanks,
jp

2

Jeff P. wrote:

Hi,
I’m doing a search, where I have contacts, and also groups for those
contacts. The association is like this:
@contacts = @current_user.contacts.paginate(:all, :page =>
params[:page],
:join => :groupers,
:conditions => [“groupers.contact_id = contact.id AND
groupers.group_id = #{selected_group_id} AND name LIKE ?”, search_term],
:order => order_text)
end

This doesn’t do what I hoped, which is find everything where name is
like, and there is a grouper with this same contact id and same group
id. Clearly I’m an sql newbie and don’t know what I’m doing here. Any
help appreciated.

thanks,
jp

I messed with it some more and came up with something that does work:

      @contacts = @current_user.contacts.paginate(:all, :page =>

params[:page],
:joins => :groupers,
:conditions => [“groupers.contact_id = contacts.id AND
groupers.group_id = #{selected_group_id} AND name LIKE ?”,
sql_search_term],
:order => order_text)