http://www.perlmonks.org?node_id=901361

duelafn has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I am trying to construct a query in DBIx::Class.

I am dealing with financial data. Each transaction has a chart and may have a project. If I generate a income statement over a period I typically want to list only the charts and projects which have nontrivial activity over the period. The query below does this. The innermost query selects the all applicable transactions within the period. The middle query computes a balance when grouped by project and chart. The outermost query returns the desired list of active accounts (I use a similar query to list the active projects). Note that some accounts may see activity, yet end the period with zero balance - I want to exclude these from my report.

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

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 |

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; }); }

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;

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;

My Questions: Can I perform the query I want using DBIx::Class alone? If I can not, how can I properly perform queries based on possibly complex resultsets? Other suggestions or hints?

Update: Turns out selectcol_arrayref works fine and (as a result) my chunky nested query could work (I had some accidental stringification of the category parameter). Someone gave the hint necessary to perform most of the query in DBIx::Class alone. So, problem solved (unless there are further hints for moving my call to "unique" unto the database).

Thanks,
    Dean