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

[mnichols at mojosoft.org]

Dennis Daupert <ddaupert at sbcglobal.net> writes:

> 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?

What I like about the DBIx::Class, from memory so syntax maybe
flubbed.  Also did you look at DBIx::Class::Manual::Cookbook ?


$rs=$c->model('YourDB::Quotes')->search();

while($quotes=$rs->next()) {
                          my $id=$quotes->id;                                           # grab quotes.id
                          my $quote=$quotes->quote;                              # grab quotes.quote 
                          my $categories=$quotes->category;               # this is really cool, because $categories 
                                                                                                         # can be used to get any of the data in the table categories. 
                                                                                                         # which is why I give it the table name, not the column name. 
                          my $category_id=$categories->category;     # like category. 
}


I also use schema loader, I don't know how much of that accomplishes
the coolness above.  I get the sense that it works best if the model
is really well thought out.  For example if category is not a foreign
key reference to categories then you can't use that bit of magic
above.  I don't know how it handles multi column keys, etc.  I think
if you "hand build" your schema then it maybe more forgiving or
perhaps you can cheat a little more, but I have yet to experience this for myself. 

What I don't like about it is complex where clause stuff is tricky,
and not as straight forward as sql syntax, and more prone to errors.
But I'm finding that with $c->model('YourDB')->debug(1); The learning
curve is a lot less steep.

happy hunting,

mojo