Forum: Ruby on Rails need help with sql condition/join

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jeff P. (Guest)
on 2009-02-06 06:53
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
Jeff P. (Guest)
on 2009-02-06 08:37
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)
This topic is locked and can not be replied to.