Open stepanselyuk opened 9 years ago
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.
+1 would be useful to be able to join tables in updateAll()
. Was possible in Yii1.
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();
Hello, how to use subjects with joining tables? Ex:
I have Query which representing sql:
I need to update some fields in
ss_profiles_finances
with params of first query, in sql:But in the current implementation I can do only
which generating following sql:
Of course it is not work without joning
ss_profiles
table.