yiiyii2yii-cactiverecordcactiverecord

incrementing value of column value with updateAll()


This works very well:

   $test = self::updateAll(
                        array( 'left_nr' => '3',
                               'right_nr' => '3', ),
                        'left_nr>=:left_nr AND right_nr>=:right_nr',
                        array(
                            ':left_nr' => $parent->left_nr,
                            ':right_nr' => $parent->right_nr,
                            //':new_left_nr' => 'left_nr + 2',
                            //':new_right_nr' => 'right_nr + 2'

                        )
                    );

but what I want is:

   $test = self::updateAll(
                        array( 'left_nr' => ':new_left_nr',
                               'right_nr' => ':new_right_nr', ),
                        'left_nr>=:left_nr AND right_nr>=:right_nr',
                        array(
                            ':left_nr' => $parent->left_nr,
                            ':right_nr' => $parent->right_nr,
                            ':new_left_nr' => 'left_nr + 2',
                           ':new_right_nr' => 'right_nr + 2'

                        )
                    );

When I try that code, I get following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was: UPDATE test_nested_set SET left_nr=:yp0, right_nr=:yp1 WHERE left_nr>=:left_nr AND right_nr>=:right_nr

How can I achieve it?


Solution

  • Your code is wrong, you can achieve this goal with something like updateCounters() like below:

    <?php
    
    $test = self::model()->updateCounters(
        array(
            'left_nr' => 2, // this is equal to => left_nr = left_nr+2
            'right_nr' => 2
        ), 
        'left_nr >= :left_nr AND right_nr >= :right_nr',
        array(
            ":left_nr" => $parent->left_nr,
            ":right_nr" => $parent->right_nr,
        )
    );
    
    ?>
    

    Yii defines UpdateCounters() :

    Updates one or several counter columns. Note, this updates all rows of data unless a condition or criteria is specified.