SELECT DISTINCT accno || ' - ' || description
  FROM (
        SELECT project_id, chart_id, SUM(amount) AS bal
          FROM ((
                 SELECT me.*
                   FROM acc_trans me JOIN chart chart ON chart.id = me
+.chart_id
                  WHERE ((( transdate <= ? AND transdate >= ? ) AND ch
+art.category = ? ))
               )) self
         GROUP BY project_id, chart_id
       ) q
  JOIN chart c ON q.chart_id = c.id
 WHERE bal != 0
[download]

A simplified schema follows:

acc_trans
     Column     |  Type   |         Modifiers
----------------+---------+----------------------------
 trans_id       | integer |
 chart_id       | integer | not null
 project_id     | integer |
 amount         | numeric |
 transdate      | date    | default ('now'::text)::date

chart:
   Column    |     Type     |                Modifiers
-------------+--------------+-----------------------------------------
+-
 id          | integer      | not null default nextval('id'::regclass)
 accno       | text         | not null
 description | text         |
 category    | character(1) |

project:
    Column     |  Type   |                Modifiers
---------------+---------+------------------------------------------
 id            | integer | not null default nextval('id'::regclass)
 projectnumber | text    |
[download]

At the moment, I have the following method defined in ResultSet/AccTrans.pm which constructs such a query and gives me the accounts I want:

sub accounts {
  my $self = shift;
  $self->result_source->schema->storage->dbh_do(
  sub {
    my ($storage, $dbh) = @_;
    my ($sql, @param) = @{${$self->as_query}};
    my $q1 = qq#
      SELECT project_id, chart_id, SUM(amount) AS bal
        FROM ($sql) self
       GROUP BY project_id, chart_id
    #;
    my $q2 = qq#
      SELECT DISTINCT accno || ' - ' || description
        FROM ($q1) q LEFT JOIN chart c ON q.chart_id = c.id
       WHERE bal != 0
    #;

    my $accounts = $dbh->selectcol_arrayref($q2, {}, map $$_[1], @para
+m);
    return @$accounts;
  });
}
[download]

This will work for simple resultsets

my $rs = $schema->resultset("AccTrans")->search(
   { transdate => { '>=', '2011-04-01', '<=', '2011-04-30' }, 'chart.c
+ategory' => "I" },
   { join => "chart" }
);
say for $rs->accounts;
[download]

However it fails if the starting resultset is too complicated. In the following example, as_query returns an array ref for the parameter substitution which does not get properly handled by selectcol_arrayref: Actually, the below works fine.

my $rs = $schema->resultset("AccTrans")->search(
   { transdate => { '>=', '2011-04-01', '<=', '2011-04-30' }, 'chart.c
+ategory' => [ "I", "E" ] },
   { join => "chart" }
);
say for $rs->accounts;
[download]