[Dbix-class] Join Three Tables With Aggregate Functions

[scott at simpzoid.com]

Dear All,

I have bit of SQL (mysql) which joins three tables to get all the records
form the first table and sums from the 2 remaining tables.  The SQL looks
like:

select  t1.*, t2_total, t3_total from t1
left join
	(select t1.id as t1_id, sum(t2.total) as t2_total from t1 left join t2 on
t2.t1_id=t1.id group by t1.id) as t2s on t1.id = t2s.t1_id
left join
	(select t1.id as t1_id, sum(t3.total) as t3_total from t1 left join t3 on
t3.t1_id=t1.id group by t1.id) as t3s on t1.id = t3s.t1_id
group by t1.id;

This works fine.

I reckon (obviously wrongly) that this translates to:

my @join = $schema->resultset('T1')->search({
	},
	{
	    +select    =>  ['id' \['SUM(T2.total)'], \['SUM(T3.total)']],
	    as           =>  [qw /id t2_total t2_total/ ],
            join         =>  ['T2', 'T3'],
	   group_by   =>  'id',
});

The search returns a resultset OK but the sums are multiplied by the
number of permutations, e.g. SUM(T2.total) is a factor of 3 high if there
3 results in T3.

I think the question I may be asking is what is the syntax to express a 3
table join, on T1 to T2 and T1 to T3 type arrangement.

Thanks,
Scott