CODEWITHSUNDEEP
phpyii2
borj
borj

Reputation: 99

How to convert this MySQL query to Yii2 ActiveQuery format?

This is my MySQL query

SELECT dc.`dc_key`
,@delivered_qty := (SELECT IFNULL(SUM(dd.`qty`),0)
    FROM `delivery_detail` dd
    WHERE dd.`item_key` = 1
    AND dd.dc_key = dc.dc_key
) AS delivered_qty
,@borrowed_qty := (SELECT IFNULL(SUM(bd.`qty`),0)
    FROM `borrow_detail` bd
    WHERE bd.`item_key` = 1
    AND bd.`voided` = 0
    AND bd.`returned` = 0
    AND bd.`dc_key` = dc.`dc_key`
) AS borrowed_qty
, (@delivered_qty - @borrowed_qty) AS available_qty
FROM `delivery_catalog` dc
WHERE (@delivered_qty - @borrowed_qty) > 0
LIMIT 1

This is my result without the limit query result

This is what I have tried so far but to no good.

$SQL = ''
. 'SELECT dc.`dc_key` '
. ',@delivered_qty := (SELECT IFNULL(SUM(dd.`qty`),0) '
. '    FROM `delivery_detail` dd '
. '    WHERE dd.`item_key` = :item_key '
. '    AND dd.dc_key = dc.dc_key '
. ') AS delivered_qty '
. ',@borrowed_qty := (SELECT IFNULL(SUM(bd.`qty`),0) '
. '    FROM `borrow_detail` bd '
. '    WHERE bd.`item_key` = :item_key '
. '    AND bd.`voided` = 0 '
. '    AND bd.`returned` = 0 '
. '    AND bd.`dc_key` = dc.`dc_key` '
. ') AS borrowed_qty '
. ', (@delivered_qty - @borrowed_qty) AS available_qty '
. 'FROM `delivery_catalog` dc '
. 'WHERE (@delivered_qty - @borrowed_qty) > 0 '
//. 'LIMIT 1 '
. '';

$rawData = DeliveryDetail::findBySql($SQL, [':item_key' => $item_key])->ALL();

print_r($rawData);

return;

It returns an empty array.

I want to utilized the query builder of yii2 for a clean code.

Upvotes: 0

Views: 334

Answers (1)

ineersa
ineersa

Reputation: 3445

$SQL = ''
. 'SELECT dc.`dc_key` '
. ',@delivered_qty := (SELECT IFNULL(SUM(dd.`qty`),0) '
. '    FROM `delivery_detail` dd '
. '    WHERE dd.`item_key` = :item_key '
. '    AND dd.dc_key = dc.dc_key '
. ') AS delivered_qty '
. ',@borrowed_qty := (SELECT IFNULL(SUM(bd.`qty`),0) '
. '    FROM `borrow_detail` bd '
. '    WHERE bd.`item_key` = :item_key '
. '    AND bd.`voided` = 0 '
. '    AND bd.`returned` = 0 '
. '    AND bd.`dc_key` = dc.`dc_key` '
. ') AS borrowed_qty '
. ', (@delivered_qty - @borrowed_qty) AS available_qty '
. 'FROM `delivery_catalog` dc '
. 'WHERE (@delivered_qty - @borrowed_qty) > 0 '
//. 'LIMIT 1 '
. '';

$rows = \Yii::$app->db->createCommand($SQL)
            ->bindParam(":item_key", $item_key)
            ->queryAll();

Should work. You can use datareader via query() if result set is big.

To use query builder you need to rewrite this query, which actually can be done without variables in columns.

I think this can be rewritten to:

SELECT dc.`dc_key` ,
IFNULL(SUM(dd.`qty`),0) as delivered_qty,
IFNULL(SUM(bd.`qty`),0) as borrowed_qty
LEFT JOIN `delivery_detail` dd ON dd.`item_key` = :item_key AND dd.dc_key = dc.dc_key 
LEFT JOIN `borrow_detail` bd ON 
         bd.`item_key` = :item_key 
    AND bd.`voided` = 0 
    AND bd.`returned` = 0 
    AND bd.`dc_key` = dc.`dc_key` 
FROM `delivery_catalog` dc
GROUP BY dc.`dc_key`
HAVING delivered_qty - borrowed_qty > 0

(@delivered_qty - @borrowed_qty) AS available_qty this can be done in PHP, and such query can be converted to query builder without problems.

Upvotes: 1

Related Questions