DBIx::Class Query Helpby duelafn (Vicar)
|on Apr 26, 2011 at 14:23 UTC||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.
A simplified schema follows:
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:
This will work for simple resultsets
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).