I need to be able to perform multiple LEFT JOINs on sub-SUM queries. For example:
SELECT C.Name, O.TotalOrderAmt, R.TotalRefundAmt FROM Customers AS C LEFT JOIN (SELECT CustomerID, Sum(Amount) AS TotalOrderAmt FROM Orders GROUP BY CustomerID) O ON C.id = O.CustomerID LEFT JOIN (SELECT CustomerID, Sum(Amount) AS TotalRefundAmt FROM Refunds GROUP BY CustomerID) R ON C.id = R.CustomerID (Note: the above is air SQL, may not be perfectly valid, but you should get the idea.) According to this thread (http://groups.google.com/group/web2py/ browse_thread/thread/8d50fd87d0f2e233), this is apparently not possible using the DAL. This happens to be a situation I run into regularly (ie, it's not a fringe case for me, but I may be a fringe user :) ). Has there been any progress toward adding this functionality to the DAL? If not, are there any tips on how to use executesql and still retain some of the nice DAL functionality after the query has been run? I'm thinking I would want to use as_dict=True, but I have not found a lot of documentation on that feature.
