[Dbix-class] Possible problem with prefetch

[luke saunders]

You're dead on Will - that makes it work perfectly.

Thanks for your help.

On 4/3/06, Will Hawes <info at whawes.co.uk> wrote:
>
> 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' } );
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060403/2c261ef9/attachment.htm