[Dbix-class] Problem using slice() with join and order_by

[Matt S Trout]

On Tue, Jan 10, 2006 at 01:32:54PM +0000, Will Hawes wrote:
> I have the following model class in a Catalyst application:
> 
> package My::Model::DBIC::Link;
> use base 'My::Model::DBIC::Base'; # contains connection etc
> __PACKAGE__->table('link');
> __PACKAGE__->add_columns(qw/id from_user to_user/);
> __PACKAGE__->set_primary_key('id');
> __PACKAGE__->belongs_to(from_user => 'My::Model::DBIC::User');
> __PACKAGE__->belongs_to(to_user => 'My::Model::DBIC::User');
> 
> My search is as follows:
> 
> $rs = My::Model::DBIC::Link->search(
>   {
>     from_user => '9696'
>   }
> );
> 
> $rs->count() # returns 24
> $rs->slice(2,3) # returns a list containing two My::Model::DBIC::Link 
> objects

Hmm, slice was added because of its familiarity to CDBI users so it's not
amazingly well-tested as a DBIC method; I'd probably do

my @users = My::Model::DBIC::Link->search({ from_user => '9696' }, { offset => 1, rows => 2 });

> I want to order my results based on a column in a related table, so 
> tried the following:
> 
> $rs = My::Model::DBIC::Link->search(
>   {
>     from_user => '9696'
>   },
>   {
>     join => [qw/to_user/],
>     order_by => 'to_user.name'
>   }
> };
> 
> $rs->count() # returns 24
> $rs->slice(2,3) # returns an empty list
> 
> The DBI trace shows that with the "join" and "order_by" attributes 
> present, the call to slice() results in the following SQL being 
> executed, i.e. DBIx::Class tries to join the related table twice:
> 
> SELECT me.to_user, me.id, me.from_user FROM link me  JOIN user to_user 
> ON ( to_user.id = me.to_user )  JOIN user to_user ON ( to_user.id = 
> me.to_user ) WHERE ( from_user = ? AND to_user.id IS NOT NULL ) LIMIT 10
> 
> This happens under 0.04001 and 0.04999_01. Is it something I'm doing 
> wrong, or a bug?

It's almost certainly a bug. If you can do a test case as a patch against
the current development branch[0] I'll see about fixing it.

[0] http://dev.catalyst.perl.org/repos/bast/branches/DBIx-Class-resultset/

-- 
     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/ +