enable DBIC_TRACE=1 and run it, you can check the sql generated and see what actually happened. 2012/10/05 19:06 <sc...@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 > > > _______________________________________________ > 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 >
_______________________________________________ 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