Re: DBIx::Class Query Help

You can reduce this to a single query by filtering the groups returned by GROUP BY with a HAVING clause, i.e.

SELECT project_id, chart_id, SUM(amount)
        FROM ($sql) self
       GROUP BY project_id, chart_id
       HAVING SUM(amount) != 0
[download]

One world, one people

Comment on Re: DBIx::Class Query Help Download Code

Replies are listed 'Best First'.
Re^2: DBIx::Class Query Help by duelafn (Parson) on Apr 26, 2011 at 15:20 UTC
Ah, fabulous that does help a lot. Aside from having to remove duplicates in perl, the following does exactly what I was after: `sub accounts { my $self = shift; my $rs = $self->search({}, { select => ["project_id", "chart.accno", "chart.descript +ion", "SUM(amount)"], as => ["project_id", "accno", "description", "bal"] +, group_by => ["project_id", "chart.accno", "chart.descript +ion"], having => { 'SUM(amount)' => { '!=', 0 } }, join => [ "chart" ], }); $rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); unique map "$$_{accno} - $$_{description}", $rs->all; }` [download] Good Day, Dean	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: DBIx::Class Query Help
by duelafn (Parson) on Apr 26, 2011 at 15:20 UTC

Ah, fabulous that does help a lot. Aside from having to remove duplicates in perl, the following does exactly what I was after:

sub accounts {
  my $self = shift;
  my $rs = $self->search({},
           { select   => ["project_id", "chart.accno", "chart.descript
+ion", "SUM(amount)"],
             as       => ["project_id", "accno", "description", "bal"]
+,
             group_by => ["project_id", "chart.accno", "chart.descript
+ion"],
             having   => { 'SUM(amount)' => { '!=', 0 } },
             join     => [ "chart" ],
           });
  $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
  unique map "$$_{accno} - $$_{description}", $rs->all;
}
[download]

Good Day,
Dean

[reply]
[d/l]