[Dbix-class] D::C generates unneccessary duplicate joins..

[Toby Corkindale]

On Wed, Jul 05, 2006 at 04:44:29PM +0100, Matt S Trout wrote:
> Toby Corkindale wrote:
> > Hi,
> > I'm trying to work out how to optimise a query. D::C is generating twice as
> > many joins as neccessary, which adds up to hurt performance in extended
> > cases of the example I give below.
> > 
> > I've played around with from=> somewhat, but there's probably a simpler way,
> > right?
> > 
> > For the simplified case, we have tables like so:
> > 
> > table objects( id integer, .. )
> > table objmetadata ( object integer, metadata integer )
> > table metadata ( id integer, value .. )
> > 
> > So, there's a many-to-many relationship betweeen objects and metadata.
> > 
> > An example query wants to select objects which have metadata.value=foo and 
> > metadata.value=bar.
> > 
> > One method is to do this:
> > Using a objects->search(
> >     { metadata.value => foo, metadata_2.value => bar },
> >     { join => [ { objmetadata => metadata }, {objmetadata => metadata } ] }
> >     );
> > 
> > This results in SQL which joins objects to objmetadata *twice*, ie:
> 
> That's because you specifically asked it to do that. If you only want it to 
> join to objmetadata once, you should be doing
> 
> { join => { objmetadata => [ qw/metadata metadata/ ] } }

Ahh.. I didn't realise you could use join like that.

Thanks - I love it where there's a simple solution :)

-Toby

-- 
Turning and turning in the widening gyre/The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold/Mere anarchy is loosed upon the world
(gpg --keyserver www.co.uk.pgp.net --recv-key B1CCF88E)