Write SQL Statement in CakePhp (group by if null)

Question

So I need to write SQL statement using CakePhp ORM, but I have problem how to write in Cakephp GROUP BY IF NULL(condition).

Here is SQL Statement:
SELECT COUNT(*) FROM
(
    SELECT i.id
    FROM items i
        INNER JOIN orders o ON i.order_id = o.id
    WHERE (
    (i.type = 1 AND i.status = 50) OR ((i.type = 2 OR i.type = 4) AND i.status = 60))
        AND i.date_of_completion IS NOT NULL
        GROUP BY IFNULL(i.vessel_change_identifier, i.id)
) AS temptbl;

This is my CakePhp Query

$query = TableRegistry::get('Items')
  ->find('all')
  ->innerJoinWith('Orders', function ($q) {
    return $q->where(['Orders.id' => 'Items.order_id']);
  })
  ->Where([
    'Items.type' => 1,
    'Items.status' => 50,
  ])
  ->orWhere([
    'Items.type IN ' => [2, 4],
    'Items.status' => 60,
  ])
  ->andWhere([
    'Items.date_of_completion IS NOT' => NULL
  ]);

$query->select([
  'count' => $query->func()->count('*')
]);

Thank you!

@ndm how to use GROUP BY IFNULL(i.vessel_change_identifier, i.id) from SQL statement in CakePhp $query — JohnWayne
– JohnWayne, Commented Jul 12, 2017 at 14:30

FreeMooonSpider · Accepted Answer · 2017-07-12 14:41:46Z

1

Try to use ->ifnull()

$query = TableRegistry::get('Items')
      ->find()
      ->innerJoinWith('Orders')
      ->where([
        'Items.type' => 1,
        'Items.status' => 50,
      ])
      ->orWhere([
        'Items.type IN ' => [2, 4],
        'Items.status' => 60,
      ])
      ->andWhere([
        'Items.date_of_completion IS NOT' => NULL
      ]);
    $query
      ->group(
        $query
          ->func()
          ->ifnull([
            'Items.vessel_change_identifier',
            'Items.id'
          ])
      );
    $query->select([
      'count' => $query->func()->count('*')
    ]);

Use it and enjoy it :D

answered Jul 12, 2017 at 14:41

FreeMooonSpider

1586 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write SQL Statement in CakePhp (group by if null)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related