[Catalyst] How do you structure multi-table DBIx queries?

[Dami Laurent (PJ)]

> Date: Mon, 24 Jul 2006 18:40:47 -0700 (PDT)
> From: Dennis Daupert <ddaupert at sbcglobal.net>
> Subject: [Catalyst] How do you structure multi-table DBIx queries?
> To: catalyst at lists.rawmode.org
> Message-ID: <20060725014047.60000.qmail at web82101.mail.mud.yahoo.com>
> Content-Type: text/plain; charset="iso-8859-1"
> 
> I have a number of tables that contain foreign keys to other 
> tables; I need to be able to do selects on multiple tables, 
> specifying particular columns in particular tables.
> 
> So far, while combing the DBIx examples, I haven't found a 
> way to do something like the query below. How would I do this?
> 
> SELECT quotes.id, quotes.quote, categories.category, 
> users.fname, users.lname FROM quotes INNER JOIN categories ON 
> quotes.category = categories.id INNER JOIN users ON 
> quotes.author = users.id;
> 
> I'm really trying to wrap my head around the perl data 
> structure way of representing SQL statements, but the light 
> bulb hasn't switched on yet.
> I've been looking for a guiding set of rules as to when to 
> use a hash, when to use an anonymous array, etc. Is there such?



Here is how it would look if using DBIx::DataModel.

my $rows = MySchema->ViewFromRoles(qw/Quotes category author/)
                   ->select(-columns => [qw/quotes.id quotes.quote
                                            categories.category 
                                            users.fname users.lname/]);

Each resulting row is an object belonging simultaneously to classes
"Quotes", "Categories", "Users". But in your case you probably just want
to use it as a hashref :

foreach my $row (@$rows) {
  print "$row->{quote} ($row->{category}): $row->{fname}
$row->{lname}\n";
}

All this assumes that you have previously declared your schema as
follows :

DBIx::DataModel->Schema('MySchema');

#                  Perl_Class Table      Primary_key
#                  ========== =====      ===========
MySchema->Table(qw/Quotes     quotes     id          /);
MySchema->Table(qw/Categories categories id          /);
MySchema->Table(qw/Users      users      id          /);

#                         Class      Role     Multiplicity Join
#                         =====      ====     ============ ====
MySchema->Association([qw/Quotes     quotes   *            category/],
                      [qw/Categories category 0..1         id      /]);

MySchema->Association([qw/Quotes     quotes   *            author  /],
                      [qw/Users      author   1            id      /]);