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

[Matt S Trout]

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)

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