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

[Will Hawes]

Matt S Trout wrote:
> On Tue, Jan 10, 2006 at 09:29:14PM +0000, Will Hawes wrote:
>> 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?
> 
> prove -Ilib t/<testname>.t
> 
> or
> 
> prove -Ilib t/some*glob/*for*tests*
> 
> should do the trick. t/helperrels/16joins.t is probably the right test to
> be running; t/run/16joins.tl is the actual test code.
> 
> If that isn't enough to get you started, could you hop onto IRC or is that
> not a communication form you use? (there's bound to be at least a couple
> people who've already contributed to the tests on even if I'm not)
> 

Thanks, those tips helped a great deal. prove++ :)

I've attached a patch to 16joins.tl to add an extra test. This checks 
that a slice() of all objects returns the same number of objects as 
all() when a join is specified, which should be enough to detect the 
problem.

I'm not able to reproduce the bug with the latest branch, so maybe it's 
fixed already.
-------------- next part --------------
--- t/run/16joins.tl.orig	Wed Jan 11 10:18:00 2006
+++ t/run/16joins.tl	Wed Jan 11 10:18:12 2006
@@ -81,6 +81,13 @@
 
 is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned');
 
+# when using join attribute, make sure slice()ing all objects has same count as all()
+$rs = DBICTest->class("CD")->search(
+    { 'artist' => 1 },
+    { join => [qw/artist/], order_by => 'artist.name' }
+);
+cmp_ok( scalar $rs->all, '==', scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' );
+
 $rs = DBICTest->class("Artist")->search(
         { 'liner_notes.notes' => 'Kill Yourself!' },
         { join => { 'cds' => 'liner_notes' } });