atman atta Wed Jun 15 14:00:16 -0400 2011

Subject: Table joins...how to get 'other' table field values?

Hello,

I need to do a join across another table and I would like to obtain values from the other table.

In the examples:

1 # fetch all books joining their corresponding authors
2 $join = 'LEFT JOIN authors a ON(books.author_id = a.author_id)';
3 $book = Book::all(array('joins' => $join));
4 # sql => SELECT `books`.* FROM `books`
5 # LEFT JOIN authors a ON(books.author_id = a.author_id)

How would I get authors.* properties? Book::all returns a Book object, however I want to maybe access authors.author_name.

Thank you!


Daniel Lowrey Thu Jun 16 01:24:51 -0400 2011

When you perform a join in this manner you need to add a custom select clause to access the joined properties. So, for example:

$join = 'LEFT JOIN authors a ON(books.author_id = a.author_id)';
$sel = 'books.*, authors.name AS author_name';
$books = Book::all(array('joins' => $join, 'select'=>$sel));
foreach ($books as $b) {
    echo "Book: $b->title | Author: $b->author_name" . "<br />\n";
}
Nanne Huiges Thu Jun 16 07:39:51 -0400 2011

If you have defined a relation between the book and the author, you could also add an include statement in your fetch. You would have 2 queries instead of 1, but in some environments these can be quicker. Anyway, it should not matter too much.

$books = Book::all(array('include'=>array('author'));

This would first query books once, and then query authors with a "IN (?,?,?.....)" query.

atman atta Mon Jun 20 16:14:46 -0400 2011

Perfect, thanks again!

(1-3/3)