Incrementing a field in CakePHP

Let’s say you have an application, where users can place votes for their favorite products and you’d like to increment the current number of votes by one.

It’s very easy by using updateAll:

$this->Product->updateAll(
array(
  'Product.vote' => 'Product.vote+1'
),
array('Product.id' => 40));

You may want to restrict Product model by using unbindModel, containable, etc.

Also, you don’t have to pass in the second argument to updateAll, if you wish to update all records in your table.

  • Chowchow

    This has a problem. If your model used a var var $belongsTo = array, then a LEFT JOIN will be included in your generated update statement. The error message is something like:

    UPDATE `files_folders` AS `FilesFolders` LEFT JOIN `course_files` AS `CourseFile` ON (`FilesFolders`.`account_id` = `CourseFile`.`id`) SET `FilesFolders`.`deleted` = 0 WHERE `FilesFolders`.`instance_id` = ‘8’

    How can i remove the “LEFT JOIN” statement? Please reply

  • http://teknoid.wordpress.com teknoid

    @Chowchow

    Is the JOIN really a problem, or simply an “extra” chunk of the query?
    Using unbindModel or setting recursive to -1, should fix the issue … as already mentioned in the post.

  • http://pepa.info/freelance Petr ‘PePa’ Pavel

    I believe Chowchow didn’t like the JOINS attached to his query.

    I wrote a piece of code that adds support for recursive = -1 to updateAll() by overriding updateAll() in AppModel.
    http://blog.pepa.info/php-html-css/cakephp/getting-rid-of-joins-in-updateall-query/

  • http://teknoid.wordpress.com teknoid

    @Petr ‘PePa’ Pavel

    Excellent. Thank you for sharing… if I am not lazy enough today, I’ll try to work this into the core as a patch (hope you don’t mind).

    Cheers.

  • http://pepa.info/freelance Petr ‘PePa’ Pavel

    Hi Teknoid, you bet I wouldn’t mind :-)

    On the other hand, I can imagine a better way to modify the core. Like skipping $this->_getJoins in DboMysqlBase if $conditions['recursive'] == -1
    …and something similar to other data sources (they don’t use _getJoins).

    It would take a while to find and test a solution for all 7 files (dbo_db2.php, dbo_firebird.php, dbo_mssql.php, dbo_mysql.php, dbo_oracle.php, dbo_sqlite.php, dbo_source.php). I know I should come up with a patch but I just don’t have the time. Sorry.

    So maybe in the end, it’s better to submit a workaround like mine than to do nothing at all :-)

    Petr

  • Adrenalin

    @Petr ‘PePa’ Pavel thank you for sharing, my update sql look so much cleaner now ;)

  • kodegeek

    Thanks, helps alot

  • kicaj

    Thanks!