Searching on foreing keys

Rails
1

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

2

Patrick A. 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 S.
http://blog.hasmanythrough.com

3

Josh S. wrote:

Person.reflect_on_association(:owner) returns a reflection object…

Duh. That should be Pet.reflect_on_association(:owner)

4

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

5

On 5/14/06, Patrick A. [email protected] 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)