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

[Matt S Trout]

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/ ] } }

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +