CDbCriteria deleteAll problems

Archives Yii 1.0.x Discussions
1

Hi everybogy!

I’m trying to delete some records, using a criteria and the deleteAll function, but I’m facing two different problems…

  1. With no table alias:



$cri = new CDbCriteria;

        

        if ($users !== '') {

            $cri->condition = 'project_id = :project_id and user_id != :user_id AND user_id NOT IN (:users )';

            $cri->params = array(':project_id' => $this->project_id, ':user_id' => 1, ':users' => $users);

        } else {

            $cri->condition = 'project_id = :project_id and user_id != :user_id';

            $cri->params = array(':project_id' => $this->project_id, ':user_id' => user()->id);

        }

        

       

        ProjectUser::model()->deleteAll($cri);

In this case, no delete is executed… I don’t know why… or what I’m doing wrong, but I don’t see any deletes in my log file… Am I doing it right?

  1. If I do the same, but using an alias, I get a sql error:



$cri = new CDbCriteria;

        $cri->alias = 'pu';

        if ($users !== '') {

            $cri->condition = 'pu.project_id = :project_id and pu.user_id != :user_id AND pu.user_id NOT IN (:users )';

            $cri->params = array(':project_id' => $this->project_id, ':user_id' => 1, ':users' => $users);

        } else {

            $cri->condition = 'pu.project_id = :project_id and pu.user_id != :user_id';

            $cri->params = array(':project_id' => $this->project_id, ':user_id' => user()->id);

        }

        

       

        ProjectUser::model()->deleteAll($cri);

In this case I have noticed that the alias ‘pu’ is not written in the query after the column name… is this a bug, or just is not allowed to use aliases on deletes?

Thank you very much in advance!

2

Can’t see, how the alias should affect that you don’t see any DELETE statement at all in case 1). So maybe double check, it’s probably some different reason. You should have a SQL executed in both cases.

3

Hi there,

Tracing backwards the command I have seen not relation with ‘alias’ on the creation of the deletecommand:




public function createDeleteCommand($table,$criteria)

	{

		$this->ensureTable($table);

		$sql="DELETE FROM {$table->rawName}"; // it gets the name from its schema

		$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;

	}

So maybe this is the reason why it fails on the second scenario. I will stick with ‘t’ ALIAS which is by default. For the first scenario, I recommend you that you log the SQL statements to what is actually executed and then double check with your DB administrator the query created. Here is a good article on how to view your SQL queries: http://www.yiiframework.com/wiki/58/sql-logging-and-profiling-in-firebug-yii-1-1/