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.
8dad1ec4d769734583f45fbbee5cd009?d=identicon&s=25 Jeff Pritchard (jeffpritchard)
on 2009-02-06 05: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
8dad1ec4d769734583f45fbbee5cd009?d=identicon&s=25 Jeff Pritchard (jeffpritchard)
on 2009-02-06 07:37
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)
This topic is locked and can not be replied to.