Searching database using foriegn keys


#1

I’ve read a lot about the ORM magic of Active Record which allows you to
address a foriegn key object directly…

e.g.

I have a books and people table where books has a column called
person_id
If I get myself a Book object called mybook I get to the person object
associated with this book by saying

book.person

which is awesome and I love it.

My question, how can I say "Go and get me all the books whose person =
“Andy Griffith”. See what I mean? I want to search for a bunch of book
rows based on some value in the people table. How can I do that?
Thanks.


#2

On Mar 9, 2006, at 7:05 PM, John R. wrote:

rows based on some value in the people table. How can I do that?
person.books


– Tom M.


#3

Hi John,

My question, how can I say "Go and get me all the books whose person =
“Andy Griffith”. See what I mean? I want to search for a bunch of book
rows based on some value in the people table. How can I do that?
Thanks.

Try:

Person.find_by_name(“Andy Griffith”).books

This assumes that your people database table has a column called “name”,
otherwise simple replace name by whatever your column is called (e.g
Person.find_by_fullname).
Or if you did split firstname/lastname in your database

Person.find_by_firstname_and_lastname(‘Andy’, ‘Griffith’).books

Matt


#4

Matthias von Rohr wrote:

Hi John,

My question, how can I say "Go and get me all the books whose person =
“Andy Griffith”. See what I mean? I want to search for a bunch of book
rows based on some value in the people table. How can I do that?
Thanks.

Try:

Person.find_by_name(“Andy Griffith”).books

This assumes that your people database table has a column called “name”,
otherwise simple replace name by whatever your column is called (e.g
Person.find_by_fullname).
Or if you did split firstname/lastname in your database

Person.find_by_firstname_and_lastname(‘Andy’, ‘Griffith’).books

Matt

Thanks for both responses. I put in a simpler example than I needed to
make it easier to explain. This is very good, but what if there are
several foreign keys for the books table. e.g. I want to do a search to
find all the books that where person from the people table = Andy
Griffith, publisher from the publishers table = Westinghouse, printer
from the printers table = Bob’s printing etc.

In other words, several conditions based of foreign keys at the same
time. Exactly like conditions works for the find method except with
foriegn keys. Am i reaching here?

The only other thing I can think of is to look up each foriegn key
individually and then put the id’s into the conidtion of books.find but
that seems to be working around the ORM. Of course, using like a search
engine is abusing it to begin with…

Is that more clear? Thanks again.