Forum: Ruby on Rails searching on foreing keys

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.
997433f165140d58f52b8c0d1d005dc1?d=identicon&s=25 Patrick Aljord (Guest)
on 2006-05-14 18:22
(Received via mailing list)
Hey all,
I'm using a simple search function. It's working
great except for foreign keys.
I have one table pets (id,name,owner_id)
and another table people(id,name)
owner_id being a foreign key of pet pointing to people name.

here it is on the pet controller:
@paginator, @pets= paginate(:pets, :conditions =>["name OR owner_id
like ?","%"+params[:filter]+"%"], :include =>person,:order_by =>
@sort_by,:per_page => default_per_page )

it searches great in name but not in owner_id. I mean it searches
through
people id instead of the person name it's referring. Any idea how to
make it search through the foreign key?

thanx in advance

Pat
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-05-14 18:35
Patrick Aljord wrote:
> Hey all,
> I'm using a simple search function. It's working
> great except for foreign keys.
> I have one table pets (id,name,owner_id)
> and another table people(id,name)
> owner_id being a foreign key of pet pointing to people name.
>
> here it is on the pet controller:
> @paginator, @pets= paginate(:pets, :conditions =>["name OR owner_id
> like ?","%"+params[:filter]+"%"], :include =>person,:order_by =>
> @sort_by,:per_page => default_per_page )
>
> it searches great in name but not in owner_id. I mean it searches
> through
> people id instead of the person name it's referring. Any idea how to
> make it search through the foreign key?

The convention in Rails is to name your foreign key after the class of
the associated model, not the role it plays. You can tell Rails the
class of the model with :class_name, and to use a different key with the
:foreign_key option on the association message. You have to say both
because the :class_name option sets the foreign key, so you have to
override that explicitly.

Pet
  belongs_to :owner, :class_name => "Person", :foreign_key => owner_id

If you already have your association set up that way, then maybe your
search code isn't doing the right thing to find the foreign key.
Person.reflect_on_association(:owner) returns a reflection object that
includes information about the association name, associated class,
foreign key, etc. - that should be where you look to find the correct
foreign key.

--
Josh Susser
http://blog.hasmanythrough.com
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-05-14 18:36
Josh Susser wrote:
> Person.reflect_on_association(:owner) returns a reflection object...

Duh.  That should be Pet.reflect_on_association(:owner)
997433f165140d58f52b8c0d1d005dc1?d=identicon&s=25 Patrick Aljord (Guest)
on 2006-05-14 19:20
(Received via mailing list)
k, got it working now.
but I have several foreign keys and I can only search one at a time like
this:
@paginator, @pets= paginate(:pets, :conditions =>["owner_id
like ?","%"+params[:filter]+"%"], :include =>person,:order_by =>
@sort_by,:per_page => default_per_page )

I have other foreign keys like color_id and race_id. I can search
through each of them but can't figure out the right syntax. Here is
what I tried:

@paginator, @pets= paginate(:pets, :conditions =>["owner_id
like ?","%"+params[:filter]+"%" OR "color_id
like ?","%"+params[:filter]+"%" OR "race_id
like ?","%"+params[:filter]+"%" ], :include =>person,:order_by =>
@sort_by,:per_page => default_per_page )

but I get errors. any idea of the right way to do this?

thanx in advance

Pat
997433f165140d58f52b8c0d1d005dc1?d=identicon&s=25 Patrick Aljord (Guest)
on 2006-05-14 20:18
(Received via mailing list)
On 5/14/06, Patrick Aljord <patcito@gmail.com> wrote:
> @paginator, @pets= paginate(:pets, :conditions =>["owner_id
> like ?","%"+params[:filter]+"%" OR "color_id
> like ?","%"+params[:filter]+"%" OR "race_id
> like ?","%"+params[:filter]+"%" ], :include =>person,:order_by =>
> @sort_by,:per_page => default_per_page )
>
found out, it was:
@paginator, @pets= paginate(:pets, :conditions =>["owner_id
 like ? or color_id like ?","%"+params[:filter]+"%"
,"%"+params[:filter]+"%" , :include =>person,:order_by =>
@sort_by,:per_page => default_per_page)
This topic is locked and can not be replied to.