need help with sql condition/join

rubyonrails-talk
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 Pritchard 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)