FIXED: Querybuilder for sql with subquery

Need Help
1

hi,
how would the querybuilder look like for the following sql ?

SELECT
x.qty,
a.*
FROM
items a
LEFT JOIN (
SELECT
item_id,
SUM(quantity) qty
FROM
orderitems b
group by (item_id)
) x
ON
x.item_id = a.id

2

Looks like that the following is doing what I expected :slight_smile:

   $query = $this->Items->find();
    $query->select(['totQty' => $query->func()->sum('case when Orders.Ordertype_id = 1 then Orderitems.quantity else -Orderitems.quantity end')])
        ->leftJoinWith('Orderitems')
        ->leftJoinWith('Orderitems.Orders')
        ->group(['Items.id'])
        ->enableAutoFields(true);

Result

SELECT
    (
        SUM(
            CASE WHEN Orders.Ordertype_id = 1 THEN Orderitems.quantity ELSE - Orderitems.quantity
        END
    )
) AS totQty,
Items.id AS Items__id,
Items.user_id AS Items__user_id,
Items.shortname AS Items__shortname,
Items.longname AS Items__longname,
Items.description AS Items__description,
Items.created AS Items__created,
Items.modified AS Items__modified
FROM
    items Items
LEFT JOIN orderitems Orderitems ON
    Items.id =(Orderitems.item_id)
LEFT JOIN orders Orders ON
    Orders.id =(Orderitems.order_id)
GROUP BY
    Items.id