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.


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' } );

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 5 | next ›
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedApr 3, '06 at 9:33a
activeApr 3, '06 at 11:23a

2 users in discussion

Luke saunders: 3 posts Will Hawes: 2 posts



site design / logo © 2021 Grokbase