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

[Will Hawes]

Matt S Trout wrote:
> 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/
> 

Sorry but I can't see where to start when it comes to patching your 
tests. I can't see how to run them independently of Module::Build 
either. If you can suggest which files you'd like patched I'll give it a 
stab. Alternatively, would a standalone test case with its own SQLite db 
be any good?