Yii solves DeleteAll connection table deletion error reporting problem

This article mainly introduces the solution to the error problem when Yii uses DeleteAll to delete concatenated tables. It analyzes the relevant SQL statements and the causes and correction methods of the errors. Friends in need can refer to it. I hope to be helpful.

The details are as follows:

When deleting data, we often encounter the conditions for determining the deletion of data. Today, we use Yii's CDbCriteria to generate related conditions. The database reports an error when deleting in batches.

The page code is:

$criteria=new CDbCriteria;
$criteria->join = ' LEFT JOIN {{positions}} p ON p.zpo_id=t.zpo_id ';
$criteria->addCondition("p.zpo_type=1");
$criteria->addCondition("t.zpl_content_id in ($id)");
PosLog::model()->deleteAll($criteria);

The error SQL is:

DELETE FROM `zd_pos_log` LEFT JOIN zd_positions p ON p .zpo_id=t.zpo_id WHERE (p.zpo_type=1) AND (t.zpl_content_id in (76))

The correct SQL statement should be:

DELETE t FROM `zd_pos_log` t LEFT JOIN zd_positions p ON p.zpo_id=t.zpo_id WHERE (p.zpo_type=1) AND (t.zpl_content_id in (76))

Trace back to the Yii base class:

framework/db/schema/CDbCommandBuilder.php#166

public function createDeleteCommand($table,$criteria)
{
  $this->ensureTable($table);
  $sql="DELETE FROM {$table->rawName}";
  $sql=$this->applyJoin($sql,$criteria->join);
  $sql=$this->applyCondition($sql,$criteria->condition);
  $sql=$this->applyGroup($sql,$criteria->group);
  $sql=$this->applyHaving($sql,$criteria->having);
  $sql=$this->applyOrder($sql,$criteria->order);
  $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
  $command=$this->_connection->createCommand($sql);
  $this->bindValues($command,$criteria->params);
  return $command;
}

Solution. Modify the base class method:

public function createDeleteCommand($table,$criteria,$alias='t')
{
  $this->ensureTable($table);
  $alias=$this->_schema->quoteTableName($alias);
  if(empty($criteria->join)){
    $sql="DELETE FROM {$table->rawName}";
  }else{
    $sql="DELETE $alias FROM {$table->rawName} $alias";
  }
  $sql=$this->applyJoin($sql,$criteria->join);
  $sql=$this->applyCondition($sql,$criteria->condition);
  $sql=$this->applyGroup($sql,$criteria->group);
  $sql=$this->applyHaving($sql,$criteria->having);
  $sql=$this->applyOrder($sql,$criteria->order);
  $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
  $command=$this->_connection->createCommand($sql);
  $this->bindValues($command,$criteria->params);
  return $command;
}

Related recommendations:

##Yii2 implements rbac permission control

Detailed explanation of Yii operating mechanism and routing

How to load Yii’s own verification code function

The above is the detailed content of Yii solves DeleteAll connection table deletion error reporting problem. For more information, please follow other related articles on the PHP Chinese website!