[Dbix-class] join and where

[Patrik Wallstrom]

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.

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');

> 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

Thanks for the tip.

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

As Brandon said, it's an implied relation.

-- 
patrik_wallstrom->foodfight->pawal at blipp.com->+46-733173956