We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

ORM/PHQL updates without pre-fetches

Hey,

I am trying to make a very simple update request to the database, and I can't seem to do it with the model relationships or phql.

The only line which I want to query to be DB is this one:

UPDATE `b` SET `online` = 'yes' WHERE `id` = '2'

I followed this post: https://forum.phalcon.io/discussion/606/update-just-one-model-property

and after trying everything there, I tried out PHQL - which seems to have the exact same behavior - I have to load more selects on to a query.

My Query:

$phql = "UPDATE b SET online = ?0 WHERE id = ?1";
        $result = $this->modelsManager->executeQuery($phql, array(
            0 => 'yes',
            1 => $id
        ));

I get the following SQL commands on the MySQL server If a record exists and it needs to be updated:

Query   SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='b'
Query   DESCRIBE `b`
Query   SELECT `b`.`id`, `b`.`online`, `b`.`creator`, `b`.`created`, `b`.`modifier`, `b`.`modified` FROM `b` WHERE `b`.`id` = '2'
Query   START TRANSACTION
Query   SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='users'
Query   SELECT COUNT(*) AS `rowcount` FROM `users` WHERE `users`.`id` = '2'
Query   UPDATE `b` SET `online` = 'yes' WHERE `id` = '2'
Query   COMMIT
Quit

If a record does not exist and it needs to be updated:

Query   SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='b'
Query   DESCRIBE `b`
Query   SELECT `b`.`id`, `b`.`online`, `b`.`creator`, `b`.`created`, `b`.`modifier`, `b`.`modified` FROM `b` WHERE `b`.`id` = '7'
Quit

I also need to get the amount of affected rows by the command, or if the operation was successful. $result->success returns true for 1 affected row and 0.

The other possibility is just to fetch the record, and then update it.. :/ Any ideas what I am missing?

Look at this Model Meta-data. If you schema will be "fixed" you can store a metadata in APC or some other cache. And requests like:

SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='b'

And orhers "meta" will not be fired.



7.7k

That's great, enabling the cache from memory to files helps with the schema queries.

I am still encountering the phql behavior of selecting an object prior to updating it..