Calculating percentage using Query Builder

Need Help
1

Hi

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. :slight_smile: