[Dbix-class] Join Three Tables With Aggregate Functions
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
Re: [Dbix-class] Join Three Tables With Aggregate Functions
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
Re: [Dbix-class] Join Three Tables With Aggregate Functions
Hi Scott,
The cookbook has a section that should help you form subqueries in your
search:
http://search.cpan.org/dist/DBIx-Class-0.08121/lib/DBIx/Class/Manual/Cookbook.pod#Correlated_subqueries
Note how a subquery can return a column max (or sum in your case) while
referencing the outer search's table via its 'me' alias, which will get
you your join.
Cheers,
--Trevor
On 10/05/2012 03:02 AM, sc...@simpzoid.com wrote:
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
[Dbix-class] Can't install 08201
Hi all - I'm trying to install the latest version of DBIx::Class via cpanm. I'm running perl 5.14 under Ubuntu 12.04. The installation fails with: -- Working on DBIx::Class Fetching http://search.cpan.org/CPAN/authors/id/F/FR/FREW/DBIx-Class-0.08201.tar.gz ... OK Configuring DBIx-Class-0.08201 ... N/A ! Configure failed for DBIx-Class-0.08201. See /home/alanh/.cpanm/build.log for details. Build.log says: snip *** Module::AutoInstall configuration finished. Unknown function is found at Makefile.PL line 199. Execution of Makefile.PL aborted due to runtime errors. And on line 199 of Makefile.PL we have: WriteAll(); Any pointers? Thank you! - Alan ___ 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
