[Dbix-class] order_by interfering with has_many fetch

[Darin McBride]

On Monday November 16 2015 7:20:17 PM Peter Rabbitson wrote:
> On 11/16/2015 07:16 PM, Darin McBride wrote:
> > On Monday November 16 2015 10:08:13 AM Peter Rabbitson wrote:
> >> On 11/16/2015 06:20 AM, Darin McBride wrote:
> >>> On Sunday November 15 2015 7:09:05 PM Darin McBride wrote:
> >>> So, the column is:
> >>> 
> >>> __PACKAGE__->add_columns(
> >>> 
> >>>       name                    => { data_type => 'varchar', size => 30,
> >>> 
> >>> is_nullable => 0 },
> >>> 
> >>> So, explicitly not nullable.
> >>> 
> >>> I've added this code as the next executable line after all the columns:
> >>> 
> >>> __PACKAGE__->add_unique_constraint(name => ['name']);
> >>> 
> >>> And now my search (which has grown since last time - I'm now prefetching
> >>> sitterauths, too, since I'm going to need a field from there for each
> >>> empire>
> >>> 
> >>> returned) looks like this:
> >>>           $planet_rs =
> >>>           
> >>>               Lacuna->db->resultset('Map::Body')->
> >>>               search(
> >>>               
> >>>                      {
> >>>                      
> >>>                          'sitterauths.sitter_id' => $real_empire->id,
> >>>                          'me.class' => { '!=' =>
> >>> 
> >>> 'Lacuna::DB::Result::Map::Body::Planet::Station' },
> >>> 
> >>>                      },
> >>>                      {
> >>>                      
> >>>                          join => { empire => 'sitterauths' },
> >>>                          prefetch => { 'empire', 'sitterauths' },
> >>>                          order_by => ['me.name'],
> >>>                      
> >>>                      });
> >> 
> >> Please use the resultset exactly as defined above, execute the following
> >> and get me its result:
> >> 
> >> 
> >> use Devel::Dwarn;
> >> $Data::Dumper::Maxdepth = 3;
> >> Dwarn [
> >> 
> >>    $planet_rs->result_source
> >>    
> >>               ->schema
> >>               
> >>                ->storage
> >>                
> >>                 ->_extract_colinfo_of_stable_main_source_order_by_portion
> >>                 (
> >>                 
> >>                   $planet_rs->_resolved_attrs
> >>                 
> >>                 )
> >> 
> >> ]
> 
> What does this say then:
> 
> Dwarn { $planet_rs->result_source->unique_constraints }
> 
> Something screwy is going on with the metadata layer...


{
  primary => [
    "id"
  ]
}

This makes it look like this line:

# tell DBIC that this is unique so it can be sorted properly when dealing
# with has_many, prefetch, and order_by
__PACKAGE__->add_unique_constraint(name => ['name']);

isn't doing anything.  Though, when I change it so the second parameter isn't 
an array reference anymore, I do get a nasty error message telling me I messed 
it up, and nothing else happens (it dies), so I'm pretty sure it's being 
called before the above Dwarn :)

As I'm reading the docs more, I also notice that prefetch is another name for 
join, so I'm removing the explicit join to just rely on the prefetch for 
telling DBIC to perform the join.  Not that this seems to affect the output at 
all, I'm just being clear so that if I paste the code in again in the future, 
you'll know why it has changed.

I also updated to DBIC 0.082820 on my dev system just in case it made any 
difference.  You probably knew it wouldn't, but it can't hurt.  (If it does 
make a difference, I'll have to upgrade the servers, too.)

> 
> > (It's not defined as a unique constraint in the db though, I was going to
> > add that later, since I suspect we'll get some minor speed improvement
> > from it.)
> It is not relevant whether the DB itself has it. It is perfectly fine to
> "lie" to DBIC as long as the thing exists logically.

That's what I figured, which is why I wasn't going to worry about it quite yet.  
I like consistency, but things like this are regularly not consistent while 
still developing :)