[Dbix-class] Possible problem with prefetch

[Will Hawes]

luke saunders wrote:
> Is this the expected behaviour of prefetch? Apologies for not replacing 
> all the names with 'foo', 'bar' etc.
> 
> Receptor has a 'many to many' relationship with Ligand, with 
> 'natural_ligands_map' being the bridging relationship.
> 
> I want to preform a search on Receptor which prefetches all the Ligand 
> rows. So I do the following:
> 
>     my $rs = $c->config->{schema}->resultset('Receptor')->search
>         ({class4 => 'Orphan'},
>          {prefetch => {natural_ligands_map => 'ligand'}});
> 
>     my $count = $rs->count;
>     # $count is 0 here
> 
> The search returned no results despite there being 60 Receptor rows 
> which have a class4 of 'Orphan'. The reason for this is that these 
> receptors don't have any Ligands. Proved by the following :
> 
>     my $rs = $c->config->{schema}->resultset('Receptor')->search
>         ({class4 => 'Orphan'});
> 
>     my $count = $rs->count;
>     # $count is 60 here
> 
> this returns 60 receptors as expected.
> 
> Now, I want the Receptor rows even if they don't have any Ligands but 
> that doesn't appear to be possible. Is this a bug or is it the expected 
> behaviour? If it's a bug I'll knock up a test case for y'all.
> 
> Obviously not doing the prefetch solves the problem but in this case 
> that's much too expensive.
> 
> Thanks,
> Luke.
> 
> Resulting debug SQL from first search for reference (I replaced the 
> column names with * to save space):
> SELECT me.*, natural_ligands_map.*, ligand.* FROM receptors me LEFT JOIN 
> natural_ligands_2_receptors natural_ligands_map ON ( 
> natural_ligands_map.gpcrid = me.gpcrid )  JOIN natural_ligands ligand ON 
> ( ligand.ligid = natural_ligands_map.ligid ) WHERE ( ( ( class4 = ? ) ) 
> ) ORDER BY natural_ligands_map.gpcrid: `Orphan'


If I've read that correctly, you need the natural_ligands_map to 
generate LEFT JOIN rather than JOIN to get the desired behaviour.

That can be specified in your model class for the natural_ligands_map 
table. I'm not 100% on the syntax, but I think it should be something 
like this:

package Your::NaturalLigandsMap::Class';
...
__PACKAGE__->has_many( 'natural_ligands' => 
'Your::NaturalLigands::Class', 'ligid', { join_type => 'left' } );