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

[Toby Corkindale]

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:
SELECT me.stuff..
FROM objects me
    LEFT JOIN objmetadata om ON ( -- first time
        om.object = me.id
    )
    JOIN metadata metadata ON (
        metadata.id = om.metadata
    )
    LEFT JOIN objmetadata om_2 ON ( -- second time
        om_2.object = me.id
    )
    JOIN metadata metadata_2 ON (
        metadata_2.id = om_2.metadata
    )
....


Now, ideally that SQL query would only join to objmetadata once, and look like
this:
SELECT me.stuff..
FROM objects me
    JOIN objmetadata om ON (
        om.object = me.id
    )
    JOIN metadata metadata ON (
        metadata.id = om.metadata
    )
    JOIN metadata metadata_2 ON (
        metadata_2.id = om.metadata
    )
....

Is this possible to generate from inside D::C? How can I go about doing it?


I've tried experimenting with from=> to achieve this..
Something which I thought *should* work, looked like the following..
Unfortunately, it doesn't quite fit into the from=> format rules, and so
doesn't work. Is it possible to fix it, if this method is the way forward?

from => [
    { me => 'objects' },
    [
        [
            { om => 'objmetadata', -join_type => 'inner' },
            { 'me.id' => 'om.object' },
        ],
        [
            { metadata => 'metadata', -join_type => 'inner' },
            { 'om.metadata' => 'metadata.id' },
        ],
        [
            { metadata_2 => 'metadata', -join_type => 'inner' },
            { 'om.metadata' => 'metadata_2.id' },
        ],
    ],
],



Cheers,
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)