You're dead on Will - that makes it work perfectly.<br><br>Thanks for your help.<br><br><div><span class="gmail_quote">On 4/3/06, <b class="gmail_sendername">Will Hawes</b> <<a href="mailto:info@whawes.co.uk">info@whawes.co.uk
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">luke saunders wrote:<br>> Is this the expected behaviour of prefetch? Apologies for not replacing
<br>> all the names with 'foo', 'bar' etc.<br>><br>> Receptor has a 'many to many' relationship with Ligand, with<br>> 'natural_ligands_map' being the bridging relationship.<br>><br>> I want to preform a search on Receptor which prefetches all the Ligand
<br>> rows. So I do the following:<br>><br>> my $rs = $c->config->{schema}->resultset('Receptor')->search<br>> ({class4 => 'Orphan'},<br>> {prefetch => {natural_ligands_map => 'ligand'}});
<br>><br>> my $count = $rs->count;<br>> # $count is 0 here<br>><br>> The search returned no results despite there being 60 Receptor rows<br>> which have a class4 of 'Orphan'. The reason for this is that these
<br>> receptors don't have any Ligands. Proved by the following :<br>><br>> my $rs = $c->config->{schema}->resultset('Receptor')->search<br>> ({class4 => 'Orphan'});<br>><br>> my $count = $rs->count;
<br>> # $count is 60 here<br>><br>> this returns 60 receptors as expected.<br>><br>> Now, I want the Receptor rows even if they don't have any Ligands but<br>> that doesn't appear to be possible. Is this a bug or is it the expected
<br>> behaviour? If it's a bug I'll knock up a test case for y'all.<br>><br>> Obviously not doing the prefetch solves the problem but in this case<br>> that's much too expensive.<br>><br>> Thanks,<br>> Luke.
<br>><br>> Resulting debug SQL from first search for reference (I replaced the<br>> column names with * to save space):<br>> SELECT me.*, natural_ligands_map.*, ligand.* FROM receptors me LEFT JOIN<br>> natural_ligands_2_receptors natural_ligands_map ON (
<br>> natural_ligands_map.gpcrid = me.gpcrid ) JOIN natural_ligands ligand ON<br>> ( ligand.ligid = natural_ligands_map.ligid ) WHERE ( ( ( class4 = ? ) )<br>> ) ORDER BY natural_ligands_map.gpcrid: `Orphan'<br>
<br><br>If I've read that correctly, you need the natural_ligands_map to<br>generate LEFT JOIN rather than JOIN to get the desired behaviour.<br><br>That can be specified in your model class for the natural_ligands_map<br>
table. I'm not 100% on the syntax, but I think it should be something<br>like this:<br><br>package Your::NaturalLigandsMap::Class';<br>...<br>__PACKAGE__->has_many( 'natural_ligands' =><br>'Your::NaturalLigands::Class', 'ligid', { join_type => 'left' } );
<br><br>_______________________________________________<br>List: <a href="http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class">http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class</a><br>Wiki: <a href="http://dbix-class.shadowcatsystems.co.uk/">
http://dbix-class.shadowcatsystems.co.uk/</a><br>IRC: irc.perl.org#dbix-class<br>SVN: <a href="http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/">http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/</a><br></blockquote>
</div><br>