[Dbix-class] join and where

[Fernan Aguero]

+----[ Patrik Wallstrom <pawal at blipp.com> (06.Jan.2007 20:58):
|
| On Thu, 04 Jan 2007, Fernan Aguero wrote:
| 
| > | 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);
| 
| Yes, all this is done, and all dbic schema classes are setup and
| working.
| 
| > | 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' } 
| >     ] 
| >   } );
| 
| This looks promising, but the above example gives me this error
| message:
| 
| Not an ARRAY reference at /usr/share/perl5/DBIx/Class/Storage/DBI.pm
| line 194.

my fault. But I warned you it was untested :)

This should work:

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

The error you got (not an ARRAY ref), was because the join
information within the 'from' should be passed as an array
ref (note the difference between my previous post and this
one). Also, for some reason, you have to say '-join_type'
and not 'join_type'.

| When playing around some more with it, I can get some SQL out of it,
| but nothing that really works. The documentation seems to lack a lot
| on how to search on things more complicated than a related table, but
| I would love to get something like this to work.
| 
| If I was unclear on the relations, this is how it is setup:
| 
| Item:
| __PACKAGE__->belongs_to('feed_id' => 'OPMLR::Main::Feed');
| 
| Feed:
| __PACKAGE__->has_many(items => 'OPMLR::Main::Item', 'item_id');
| __PACKAGE__->has_many('feedusers', 'OPMLR::Main::Subscription',
| 		      'feed_id');
| __PACKAGE__->many_to_many('subscribers' => 'feedusers',
|                           'user_id');
| 
| Subscription:
| __PACKAGE__->belongs_to('user_id' => 'OPMLR::Main::User');
| __PACKAGE__->belongs_to('feed_id' => 'OPMLR::Main::Feed');
|
+----]

Maybe this works? Again, untested :)

$rs = $schema->resultset('Item')->search(
 { subscription.user_id => 1 },
 { join => [ qw/subscription/],
   order_by => 'pubdate DESC',
   rows => 30
 } );

of course assuming Item and Subscription have the correct
relationships are set in the respective classes.

Fernan