# Searching on foreing keys

**URL:** <https://www.ruby-forum.com/t/searching-on-foreing-keys/94786>\
**Category:** Rails\
**Created:** [May 14, 2006, 6:22pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786 "2006-05-14T18:22:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![patcito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/50afbb/32.png) [@patcito](https://www.ruby-forum.com/u/patcito)\
**Post date:** [May 14, 2006, 6:22pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786/1 "2006-05-14T18:22:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![patcito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/50afbb/32.png) [@patcito](https://www.ruby-forum.com/u/patcito)\
**Post date:** [May 14, 2006, 6:35pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786/2 "2006-05-14T18:35:40Z")

</div>

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](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![patcito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/50afbb/32.png) [@patcito](https://www.ruby-forum.com/u/patcito)\
**Post date:** [May 14, 2006, 6:36pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786/3 "2006-05-14T18:36:58Z")

</div>

Josh S. wrote:

> Person.reflect\_on\_association(:owner) returns a reflection object…

Duh. That should be Pet.reflect\_on\_association(:owner)

---

<div class="post-metadata">

**Author:** ![patcito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/50afbb/32.png) [@patcito](https://www.ruby-forum.com/u/patcito)\
**Post date:** [May 14, 2006, 7:20pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786/4 "2006-05-14T19:20:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![patcito](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/50afbb/32.png) [@patcito](https://www.ruby-forum.com/u/patcito)\
**Post date:** [May 14, 2006, 8:18pm UTC](https://www.ruby-forum.com/t/searching-on-foreing-keys/94786/5 "2006-05-14T20:18:06Z")

</div>

On 5/14/06, Patrick A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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)