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

ModelsManager::executeQuery doing find() behind the scenes ?

Hi there,

so we use this query in one of our periodicaly executed scripts:

$manager->executeQuery("UPDATE Robomaniac\Robot\Robot SET actionPoints=actionPoints+1 WHERE actionPoints<actionPointsLimit");

The point in doing this, is that I don't want the modelsmanager to do a find() and than crawl through every result, hydrating and than updating the model (which it does, according to the mysql replication bin log). I could do that by myself.

Is there a way that phalcon/the ORM just translates the query above to a simple update query like shown below, replacing the the full qualified class name with the table name and the fields according to the column map ?

UPDATE robots SET action_points=action_points+1 WHERE action_points<action_points_limit

Greetings and thanks for your help



145.0k
edited Oct '16

No, find() is doing executeQuery behind the scenes :)

This is just how phalcon PHQL works. It's hydrating all models to make sure that events are working correctly when updating models(as well firing them).

Just update query in PHQL always mean find all of them and iterate over them and use update method. This is how ORM works. You know what ORM even is ? How otherwise it supposed to fire your events for example ? As well it's done to use your setters and execute any fancy code which can be there.

Just use raw sql to do what you want here.



79.0k
edited Oct '16

That's the infamous N+1 query problem which is common for every ORM out there I guess. That is a so called 'performance antipattern'.

That's why I use my custom ORM-like (dynamic) models, so I have best of both worlds - flexibility, performance and ease of use.



3.2k

Saying that it is like that in every ORM is just complete bullshit.

just to cite from the Doctrine DQL documentation:

DQL UPDATE statements are ported directly into a Database UPDATE statement and therefore bypass any locking scheme, events and do not increment the version column. Entities that are already loaded into the persistence context will NOT be synced with the updated database state. It is recommended to call EntityManager#clear() and retrieve new instances of any affected entity.

If I'd need the events to be fired, I would just iterate through a resultset by myself.

But yeah, now I know it works that way in phalcon. Going to do a raw sql statement in this case.



145.0k

Doctrine is Data Mapper, phalcon is using active record.