[Dbix-class] Result Sets of Joins

[Marc Mims]

* William Reardon <wdr1 at pobox.com> [080929 08:40]:
> Excuse me if this is an obvious question, but I'm still learning the
> DBIx::Class way & haven't had any luck finding answers...
>
> Coming from the common DBI/hand-crafted sql route, I'm used to the world
> where I could join a many-to-one table and easily access columns from
> either table in the result.  E.g.:  SELECT author.name, book.title
> FROM..., getting a hashref of the result, them doing something like:
>
> print $row->{name}, " by ", $row->{title}, "\n"

Something like this should work:

# example 1
my $rs = $schema->resultset('Book')->search(
    {'author.user_id' => 1},
    {prefetch => 'author'}
);
while ( my $book = $rs->next ) {
    print $book->title, " by ", $book->author->name, "\n";
}

Is user_id the PK for the author table?  If so, you could do this:

# example 2
my $author = $schema->resultset('Author')
    ->find($user_id, {prefetch => 'books'});
for my $book ( $author->books ) {
    print $book->title, " by ", $author->name, "\n";
}

I'm assuming that Books has a belongs_to relationship to Author and
Author has a has_many relationship to Books. By getting a Book resultset
in example 1, the prefetch of author results in a single object.  In
example 2, you get a single Author object that includes a Books
resultset.

Both examples make a single call to the database.

	-Marc