ActiveRecord::update() and ActiveRecord::updateAll() with joining tables.

[yiisoft]

ActiveRecord::update() and ActiveRecord::updateAll() with joining tables.

[mdmunir]

IMO, Much better if we have method update() and delete() at object Query.

// delete
(new Query())
    ->from('order')
    ->where(['status' => 'expire'])
    ->delete();

// update join
(new Query())
    ->from('order o')
    ->leftJoin('invoice i','i.order_id = o.id')
    ->where(['i.status' => 'draft'])
    ->update([
        'o.status' => 'paid',
        'i.status' => 'paid'
    ]);

// delete from relation
$order = Order::findOne($id);
$order->getInvoices()->delete();

[stepanselyuk]

Maybe it will be usefull:

public static function updateAllUsingQuery( $attributes, Query $query, $params = [ ] )
    {

        $params = array_merge( $params, $query->params );

        if (is_null( $query->join )) {
            return static::updateAll( $attributes, $query->where, $params );
        }

        $command = static::getDb()->createCommand();
        $builder = static::getDb()->getQueryBuilder();

        $tableAlias = '{{t}}';

        $sql = $builder->update( static::tableName(), $attributes, $query->where, $params );

        $join = $builder->buildJoin( $query->join, $params );
        $sql = preg_replace( '/^UPDATE (.+) SET/', 'UPDATE $1 ' . $tableAlias . ' ' . $join . ' SET', $sql );

        if ($tableAlias != '') {

            // fixing SET statement
            $sql = preg_replace_callback(
                '/^(.+ SET)(.+?)(WHERE .+)?$/',
                function ( $m ) use ( $tableAlias ) {

                    $m[ 2 ] = preg_replace(
                    // search fields without specified table
                        '/(?<!\.)(?:\[\[|`)(\w+)(?:\]\]|`)(?!\.)/',
                        $tableAlias . '.[[$1]]',
                        $m[ 2 ]
                    );

                    return $m[ 1 ] . $m[ 2 ] . ( isset( $m[ 3 ] ) ? $m[ 3 ] : '' );
                },
                $sql
            );

        }

        $command->setSql( $sql )->bindValues( $params );

        return $command->execute();
    }

In my system the main table usually has alias t, like in CDbCriteria in Yii1.

[nirvana-msu]

+1 would be useful to be able to join tables in updateAll(). Was possible in Yii1.