Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Is it possible to use a transaction for the \Phalcon\Mvc\Model\Query ?

I want to update some records in an transaction to like that:

$query = $di->get('modelsManager')->createQuery('UPDATE UserAccounts SET UserAccounts.last_login = UserAccounts.current_login');
$query->execute();

but the official document said that: An UPDATE statement performs the update in two phases:

  • First, if the UPDATE has a WHERE clause it retrieves all the objects that match these criteria,
  • Second, based on the queried objects it updates/changes the requested attributes storing them to the relational
  • database
  • This way of operation allows that events, virtual foreign keys and validations take part of the updating process. In summary, the following code:

2.12. Phalcon Query Language (PHQL) 165 Phalcon PHP Framework Documentation, Release 1.3.0

<?php
$phql = "UPDATE Cars SET price = 15000.00 WHERE id > 101";
$success = $manager->executeQuery($phql);

is somewhat equivalent to:

<?php
$messages = null;
$process = function() use (&$messages) {
    foreach (Cars::find("id > 101") as $car) {
        $car->price = 15000;
        if ($car->save() == false) {
            $messages = $car->getMessages();
            return false;
        }
    }
    return true;
};
$success = $process();

THAT'S NOT TRUE in a transaction! and the foreach clause did a great cost.



9.7k

@cpalcon is there has an option to disable the query to use the ORM Intergration (Events etc.) for some single query, make the query DIRECT convert to the low level SQLstatement?



84.1k
Accepted
answer
edited Sep '14

UPDATE and DELETE statements in PHQL are executed inside an implicit transaction:

$query = $di->get('modelsManager')->createQuery('UPDATE UserAccounts SET UserAccounts.last_login = UserAccounts.current_login');
$query->execute();

However, you must remember that PHQL is a high level abstraction, Phalcon ORM has to call events and perform validations defined in models, so it's expected to take more steps than send a simple SQL Update to the database server. You can use the database component to execute statements in a low-level manner: http://docs.phalconphp.com/en/latest/reference/db.html



9.7k

UPDATE and DELETE statements in PHQL are executed between and implicit transaction:

```php $query = $di->get('modelsManager')->createQuery('UPDATE UserAccounts SET UserAccounts.lastlogin = UserAccounts.currentlogin'); $query->execute(); ```

However, you must remember that PHQL is a high level abstraction, Phalcon ORM has to call events and perform validations defined in models, so it's expected to take more steps than send a simple SQL Update to the database server. You can use the database component to execute statements in a low-level manner: http://docs.phalconphp.com/en/latest/reference/db.html

did the implicit transaction begin before update the first record and commit after all records being updated?



9.7k
edited Sep '14

Yes, check the code: https://github.com/phalcon/cphalcon/blob/2.0.0/phalcon/mvc/model/query.zep#L2685

thanks, I got it. another question, can the PHQL convert to a low level sql statement ? if it can, I can start a transaction and execute the low level sql to decrease the cost. I mean $sql = $query->getLowLevelSQL() ?



84.1k

No, currently that feature is not supported. You can always use the low-level database abstraction along with a transaction.



9.7k

No, currently that feature is not supported. You can always use the low-level database abstraction along with a transaction.

OK, I just worry about the foreach clause on a large records set.



9.7k

No, currently that feature is not supported. You can always use the low-level database abstraction along with a transaction.

the forum RichText Editor make my browser no response when I type some code in php..., that is a little uncomfortable. I often lost my input after the browser stucked. what is the problem. (Internet Explorer 11, Windows 8, also occurred on Firefox 31)