I’m trying to do the following query (which works using the ConnectionManager) with the query builder:
select sum(p.amount * p.quantity) as amount, i.id, i.name, sum(quantity) as item_count, sum(p.amount * p.quantity) / (select sum(p.amount * p.quantity) from purchases as p where p.project_id = :projectId) * 100 as project_percentage, sum(p.amount * p.quantity) / sum(quantity) as average from purchases as p, items as i, categories as c, receipts as r where p.project_id = :projectId and i.category_id = c.id and p.item_id = i.id and p.receipt_id = r.id and c.id in :categoryIds and r.purchase_date between :fromDate and :toDate group by i.id order by amount desc
(Maybe the above is not the best sql-query either, my skills are limited)
I ran into a problem on line 5, where I try to get the project_percentage, and cannot for the life of me figure out how calculate using the query builder. The intention is to get how many percent one item’s total amount is of the project’s total amount.
This is what I have now:
$table = TableRegistry::get('Purchases'); $query = $table->find(); $subQuery = $table->find() ->select(['sum' => 'sum(Purchases.amount * Purchases.quantity)']) ->where(['Purchases.project_id' => $data['projectId']]) ;
The above gets the total amount of the project.
$query ->contain([ 'Categories', 'Receipts', 'Items' ]) ->select([ 'amount' => 'sum(Purchases.quantity * Purchases.amount)', 'item_count' => 'sum(quantity)', 'id' => 'Items.id', 'name' => 'Items.name', 'average' => 'sum(Purchases.amount * Purchases.quantity) / sum(Purchases.quantity)', 'project_percentage' => 'sum(Purchases.amount * Purchases.quantity) / ' . $subQuery . ' * 100' ]) ->where([ 'Purchases.project_id' => $data['projectId'], ]) ->group('Items.id') ;
I know the project_percentage line is (very) wrong, but I’m lost here.