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

[Matt S Trout]

Dennis Daupert wrote:
> 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?

Read through the DBIx::Class documentation, notably the Manual::* stuff and 
the ResultSet docs. You'll also want to read the SQL::Abstract docs for the 
WHERE clause syntax

Something like $c->model('DB::Quotes')->search($where, { prefetch => [ 
qw/category user/ ] }) should cause it to retrieve the data in a single query 
assuming your quote has relationships called 'category' and 'user' defined to 
the appropriate tables.

Please note that DBIx::* is a generic namespace for extensions to DBI; 
DBIx::Class is one specific package in that namespace - so you need to specify 
this or people who aren't paying attention will give you irrelevant answers 
(such as the DBIx::DataModel answer downthread :)

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +