[Dbix-class] join and where

[Fernan Aguero]

+----[ Patrik Wallstrom <pawal at blipp.com> (04.Jan.2007 18:30):
|
| I am still trying to wrap my head around DBIx::Class, and I am trying
| to create a little app with it. Currently I have four tables, "feed",
| "item", "user" and "subscription". The "subscription" table contains
| a primary key consisting of user_id and feed_id.

Patrik,

from what you say it's not clear if you already have your
dbix::class (dbic) schema classes set up ... I would assume
you have and that you're able to get this far (from the
DBIx::Class POD):

# Connect to your database.
use DB::Main;
my $schema = DB::Main->connect($dbi_dsn, $user, $pass, \%dbi_params);

| From that I woule like to retrieve the items in pubdate order, like
| this in SQL:
| 
| select
|     i.item_id,i.enclosure
| from
|     item i
| inner join
|     subscription s on i.feed_id=s.feed_id
| where
|     s.user_id = 1
| order by i.pubdate
| desc limit 30;

[untested, caveat emptor]

$rs = $schema->resultset('Item')->search(
  { 's.user_id' => 1 },
  { 
    alias => 'i', 
    order_by => 'i.pubdate DESC',
    rows => 30,
    from => [ 
      { 's' => 'subscription', join_type => 'inner' }, 
      { 'i.feed_id' => 's.feed_id' } 
    ] 
  } );

Basically, if you're not (yet) writing your relationships
into your schema classes (using has_many, many_to_many and
belongs_to), you can still do joins by writing your own
FROM clause. This is documented here:
http://search.cpan.org/~bricas/DBIx-Class-0.07003/lib/DBIx/Class/ResultSet.pm#from
 
| I really can't figure out how to do it from the artist/cd examples in
| cookbok. Subscription has not a direct relation with item. 

but why then are you joining on feed_id? To me this looks
like a relation.

| The
| subscription class is properly setup as a has_many with belongs_to.
|
+----]

HTH,

Fernan