[Dbix-class] Join Three Tables With Aggregate Functions

[Hailin Hu]

enable DBIC_TRACE=3D1 and run it, you can check the sql generated and see
what actually happened.
2012/10/05 19:06 <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=3Dt1.id group by t1.id) as t2s on t1.id =3D 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=3Dt1.id group by t1.id) as t3s on t1.id =3D t3s.t1_id
> group by t1.id;
>
> This works fine.
>
> I reckon (obviously wrongly) that this translates to:
>
> my @join =3D $schema->resultset('T1')->search({
>         },
>         {
>             +select    =3D>  ['id' \['SUM(T2.total)'], \['SUM(T3.total)']=
],
>             as           =3D>  [qw /id t2_total t2_total/ ],
>             join         =3D>  ['T2', 'T3'],
>            group_by   =3D>  '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
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20121006/bcc=
16da0/attachment.htm