SQL slightly complex question

4 years ago

pistols
Member | 26
+
0
-

Hi,

I have quite a rookie question about nette DB

I want to do the following query

SELECT
    deposit_materials_id, SUM(amount)
FROM
    table
WHERE
    users_id = ?
GROUP BY
    deposit_materials_id

How do I forge this query in nette DB?

I tried this:

<?php
        return $this->database->table(self::TABLE_DEPOSIT_USERS)
                ->where('users_id', (int)$id)
                ->group('deposit_materials_id')
                ->sum('amount');
?>

but the final generated query is missing the group portion and returning only one result:

SELECT SUM(`amount`)
FROM `deposit_materials_users`
WHERE (`users_id` = 70)

I don't want to make a direct query because i want to keep the ref functionality to other tables

Thanks for your suggestions

4 years ago

sKopheK
Member | 207
+
0
-

Have you tried

->select('SUM(amount)')

instead? NDB can be a little clumsy case from case, sometimes different approach works.