PHQL Update With IFNULL or NOW() - Discussion

Created Oct '17	Last Reply Oct '17	Replies 1	Views 630	Votes 0

I'm having some trouble performing an PHQL UPDATE query.

I'd like to perform a bulk update on a model, setting statusId to 1 and dateAssigned to NOW() only where dateAssigned is currently NULL.

Any advice, solution, or insight as to what is going on? I'm on Phalcon 2.0.10

What I want

This sets statusId correctly, but never sets dateAssigend

$phql = 'UPDATE \Domain\Model\User SET statusId = :statusId:, dateAssigned = IFNULL(dateAssigned, NOW()) WHERE id IN (1, 2, 3)';
$manager->createQuery($phql)->execute(array(
    'statusId' => 1
));

Simlified version

This sets statusId correctly, but never sets dateAssigned

$phql = 'UPDATE \Domain\Model\User SET statusId = :statusId:, dateAssigned = NOW() WHERE id IN (1, 2, 3)';
$manager->createQuery($phql)->execute(array(
    'statusId' => 1
));

Simplified version

This sets both correctly. But not what I want to do

$phql = 'UPDATE \Domain\Model\User SET statusId = :statusId:, dateAssigned = :dateAssigned: WHERE id IN (1, 2, 3)';
$manager->createQuery($phql)->execute(array(
    'statusId' => 1,
    'dateAssigned' => date('Y-m-d H:i:s')
));

Phalcon when doing UPDATE query actually does SELECT WHERE and then check the SET part and sets model values in foreach loop. I guess IFNULL etc thing can't be really translated anyhow to PHP with OOP. HOWEVER you can just use model events for your case pretty much, like beforeValidatonOnUpdate.

Pretty much when doing this in phql this will be never bulk update - it will select all records anyway and just foreach and update each. You could ask what's the point - the point is this is ORM - and to have full objects and all events fired and make it possible to stop operation in any time we want.

So you have two options - use raw query(just access db service) or model events. If you need performance just use raw query. If you need full models, and events, validation etc fired then use model devents to set this dateAssigned value.