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

[stepanselyuk]

Hello, how to use subjects with joining tables? Ex:

I have Query which representing sql:

SELECT `t`.* FROM `ss_profiles_finances` `t` LEFT JOIN `ss_profiles` `profile` ON `t`.`profile_id` = `profile`.`id` WHERE `profile`.`login` = 'user1455'

I need to update some fields in ss_profiles_finances with params of first query, in sql:

UPDATE `ss_profiles_finances` `t` LEFT JOIN `ss_profiles` `profile` ON `t`.`profile_id` = `profile`.`id` SET `t`.`settings`=IF( `t`.`settings` IS NULL, 31, `t`.`settings` | 1 ) WHERE `profile`.`login` = 'user1455'

But in the current implementation I can do only

// $query was cached and serialized in previous operation (another request)
$rows = $this->getModel()->updateAll(
     [
         'settings' => $helperAddFlag( $opts[ 2 ], $this->getModel() )
     ],
            $query->where,
            $query->params
);

which generating following sql:

UPDATE `ss_profiles_finances` SET `settings`=IF( `settings` IS NULL, 31, `settings` | 1 ) WHERE `profile`.`login` = 'user1455'

Of course it is not work without joning ss_profiles table.

[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.

[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();