Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

DBIx::Class Query Help

by duelafn (Priest)
on Apr 26, 2011 at 14:23 UTC ( #901361=perlquestion: print w/ replies, xml ) Need Help??
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). SimonClinch 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

Comment on DBIx::Class Query Help
Select or Download Code
Re: DBIx::Class Query Help
by SimonClinch (Chaplain) on Apr 26, 2011 at 14:42 UTC
    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

    One world, one people

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

      Good Day,
          Dean

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://901361]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (8)
As of 2014-11-23 18:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (134 votes), past polls