Cakephp 3 - 转换自定义子查询时出现问题

Question

I am trying to convert my custom subquery into my cakephp 3 model syntax but I am having issues. Below is my query which is working fine if I make custom query.

$connection = ConnectionManager::get('default');
$results = $connection->execute("
    SELECT
        res.*, sa.sp_qty,
        (sa.sp_qty - SUM(received + dumped +quarantined)) as remaining_qty
    FROM (
        SELECT
            SUM(rs_receive_qty) as received,
            SUM(rs_damage_qty) as dumped,
            qs.qs_qty as quarantined,
            rs_sp_id
        FROM
            `fp_received_stock` rs 
        INNER JOIN
            fp_quarantine_stock qs ON
                qs.qs_sp_id = rs.rs_sp_id 
        WHERE
            `rs_sa_id` LIKE 'cbb31e17077e8ba0f6902d8416198dd4' 
        GROUP BY
            rs_sp_id
    ) as res
    INNER JOIN
        fp_stock_product sa ON
            sa.sp_sa_id = 'cbb31e17077e8ba0f6902d8416198dd4' AND
            sa.sp_id = res.rs_sp_id
    GROUP BY
        res.rs_sp_id
    HAVING
        remaining_qty > 0
    ")
    ->fetchAll('assoc');

echo '<pre>';
print_r($results);

If I print this query, I am getting proper data with array like below.

Array
(
    [0] => Array
        (
            [received] => 10
            [dumped] => 0
            [quarantined] => 5
            [rs_sp_id] => 9d5743062b93e0dea1f6b4220e8d599d
            [sp_qty] => 20
            [remaining_qty] => 5
        )

    [1] => Array
        (
            [received] => 5
            [dumped] => 1
            [quarantined] => 1
            [rs_sp_id] => a78606f989da4b6565aee8f6b13ab6e1
            [sp_qty] => 10
            [remaining_qty] => 3
        )

)

Now I am trying to convert this query with Cakephp 3 way and below is what I am doing at the moment.

$ReceivedStock = $this->loadModel('ReceivedStock');
$stocks = $this->loadModel('Stocks');

$subquery = $ReceivedStock->find('all');
$subquery = $subquery
    ->select([
        'received' => $subquery->func()->sum('rs_receive_qty'),
        'dumped' => $subquery->func()->sum('rs_damage_qty'),
        'quarantined' =>'qs.qs_qty',
    ])
    ->innerJoin(
        ['qs' => 'fp_quarantine_stock'],
        ['qs.qs_sp_id = ReceivedStock.rs_sp_id']
    )                                        
    ->where([
        'rs_sa_id = "cbb31e17077e8ba0f6902d8416198dd4"'
    ])
    ->group('rs_sp_id');

$stocksList = $stocks->find('all');
$stocksList
    ->select([
        'ReceivedStock__res.*',
        'sa.sp_qty',
        'remaining_qty' => 'sa.sp_qty - ' . $subquery->func()->sum('received + dumped + quarantined'),
        'ReceivedStock__res' => $subquery,
    ])
    ->innerJoin(
        ['sa' => 'fp_stock_product'],
        [
            'sa.sp_sa_id' => "cbb31e17077e8ba0f6902d8416198dd4",
            'sa.sp_id' => 'res.rs_sp_id'
        ]
    )                        
    ->group('ReceivedStock__res.rs_sp_id')
    ->having([
        'remaining_qty >' => 0
    ])
    ->toArray();

print_r($stocksList);exit;

But I am getting an error saying

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS ReceivedStock__res__*, sa.sp_qty AS sa__sp_qty, sa.sp_qty - AS `remainin' at line 1

And this is how my converted sql query looks like.

SELECT
  Stocks.sa_id AS Stocks__sa_id,
  Stocks.sa_batch_no AS Stocks__sa_batch_no,
  Stocks.sa_storage_id AS Stocks__sa_storage_id,
  Stocks.sa_supplier_id AS Stocks__sa_supplier_id,
  Stocks.sa_company_id AS Stocks__sa_company_id,
  Stocks.sa_receiving_contact_name AS Stocks__sa_receiving_contact_name,
  Stocks.sa_delivery_date AS Stocks__sa_delivery_date,
  Stocks.sa_notes_for_sl AS Stocks__sa_notes_for_sl,
  Stocks.sa_ref_document_1 AS Stocks__sa_ref_document_1,
  Stocks.sa_ref_document_2 AS Stocks__sa_ref_document_2,
  Stocks.sa_status AS Stocks__sa_status,
  Stocks.sa_created_by AS Stocks__sa_created_by,
  Stocks.sa_modified_by AS Stocks__sa_modified_by,
  Stocks.sa_created_date AS Stocks__sa_created_date,
  Stocks.sa_modified_date AS Stocks__sa_modified_date,
  ReceivedStock__res.* AS ReceivedStock__res__ *,
  sa.sp_qty AS sa__sp_qty,
  sa.sp_qty - AS remaining_qty,
  (
  SELECT
    (SUM(rs_receive_qty)) AS received,
    (SUM(rs_damage_qty)) AS dumped,
    qs.qs_qty AS quarantined
  FROM
    fp_received_stock ReceivedStock
  INNER JOIN
    fp_quarantine_stock qs ON qs.qs_sp_id = ReceivedStock.rs_sp_id
  WHERE
    rs_sa_id = "cbb31e17077e8ba0f6902d8416198dd4"
  GROUP BY
    rs_sp_id
) AS ReceivedStock__res
FROM
  fp_stock_adjustment Stocks
INNER JOIN
  fp_stock_product sa ON(
    sa.sp_sa_id = : c0 AND sa.sp_id = : c1
  )
GROUP BY
  ReceivedStock__res.rs_sp_id
HAVING
  remaining_qty > : c2

Can someone guide what I am doing wrong here ?

Answer 1

OK Guys, Eventually I have come up with solution of my own. Here below is my working code.

 $sa_id = $requestformData['sa_id'];
            // status check 
            $ReceivedStock = $this->loadModel('ReceivedStock');
            $stocks = $this->loadModel('Stocks');

            $subquery = $ReceivedStock->find('all');
            $subquery = $subquery->select([
                'received' => $subquery->func()->sum('rs_receive_qty'),
                'dumped' => $subquery->func()->sum('rs_damage_qty'),
                'quarantined' => 'if (qs.qs_qty IS NULL,0,qs.qs_qty)',
                'rs_sp_id' => 'rs_sp_id'
            ])
                ->leftJoin(['qs' => 'fp_quarantine_stock'], ['qs.qs_sp_id = ReceivedStock.rs_sp_id'])
                ->where([
                    'rs_sa_id = "' . $sa_id . '"',

                ])
                ->group('rs_sp_id'); //->toArray();

            $checkStockAdjustStatus = $this->ReceivedStock
                ->find()
                ->select([
                    'sub.received',
                    'sub.dumped',
                    'sub.quarantined',
                    'sub.rs_sp_id',
                    'sa.sp_qty',
                    'remaining_qty' => "sa.sp_qty - SUM(sub.received+sub.dumped+sub.quarantined)"

                ])
                ->innerJoin(['sa' => 'fp_stock_product'], [
                    'sa.sp_sa_id = "' . $sa_id . '"',
                    'sa.sp_id = rs_sp_id'
                ])
                ->from(['sub' => $subquery])
                ->group('rs_sp_id')
                ->having(['remaining_qty >' => 0])
                ->toArray();

Hope it helps someone who stuck at similar situation.