queryScalar sum with "with" o "join"

Yii 1.1 General Discussion
1

Hi all,

Please, could you give me some guidelines to make a query that SUMs all the values of a column, but has a related table in the WHERE clause?

I have this in my model:




class Gasto {

  

  // ....


  public $userName;		// public property for the CGridView filter

    

  public function attributeLabels()

  {	// included to illustrate the problem

	return array(

		'id' => 'ID',			// Id of the model

		'value' => 'Value',		// value I want to SUM

		'userId' => 'User',  	// foreign key, not shown

		'userName' => 'User',	// not in this table

	);

  }

  public function relations()

  {

	return array(

		'Usuario' => array(self::BELONGS_TO, 'User', 'userId'),

	);

  }

	

  private function getSearchCriteria(){

	$criteria=new CDbCriteria;


	$criteria->with = array('Usuario');


	$criteria->compare('id',$this->id);

	$criteria->compare('user.username', $this->userName, true);

	

	return $criteria;

  }

 

  public function search()	// this works

  {

	return new CActiveDataProvider($this, array(

			'criteria'=>$this->getSearchCriteria(),

			// ... order and pagination stuff

			));

  }

  

  public function getSum()

  {

	$criteria = $this->getSearchCriteria();

	$count = Gasto::model()->count($criteria);	// < this works too

       

       $criteria->select='SUM(value)'; // << this line wasnt here when I first posted this, but it was in my code.

	// But Here is the problem:

	$total = $this->commandBuilder->createFindCommand($this->getTableSchema(), $criteria)->queryScalar();

	

	return $total;

  }

}

Model Gasto is related with the User by its column userId.

I have the filter working in the CGridView, event the Gasto::model()->count($criteria) works, (it returns the count of the filtered items).

But commandBuilder seems to ignore the "with" property of the criteria.

I am trying with the join properties of the CDBCommand, but I am not geting anything better than this MySQL Error:




1054 Unknown column 'user.username' in 'where clause'. The SQL statement executed was: SELECT SUM(value) FROM `res_gastos` `t` WHERE user.username LIKE .....

As you can see, there is no JOIN in the query.

Thank you in advance and sorry if my english is not correct :D

2

where is your commandBuilder method its not in the dump above

3

Oh sorry, I forgot to paste this line ::)




$criteria->select='SUM(value)';

This is my getSum function




public function getSum()

{

       $criteria = $this->getSearchCriteria();

       $count = Gasto::model()->count($criteria);      // < this works too

		

	$criteria->select='SUM(value)';

        // But Here is the problem:

        $total = $this->commandBuilder->createFindCommand($this->getTableSchema(), $criteria)->queryScalar();

        

        return $total;

  }

It happens because of variable names and rewriting stuff here, sorry…

4

Got it!

I’ve debugged createFindCommand in CDBCommandBuilder, and I haven’t seen any reference to the with field of the criteria.

I don’t know if this is a bug, or if this is just normal, and the “with” field is only related with the ActiveRecord…

I’ve been able to make the query using the join field.

I didn’t liked it so much because of the need of writing the table name instead of the Model name, but I have found it very powerfull. And you can replace the table name with those {{table name}} things that I dont know exactly where and how are replaced with the actual table name.

Finally, the solution is:




private function getSearchCriteria(){

	$criteria=new CDbCriteria;

	if(!Utils::isEmpty($this->userName)){	// not empty/null/false/unset...

		$criteria->with = array('Usuario');		// << I think CGridView and CActiveDataProvider are still needing this line

		$criteria->join = 'JOIN {{users}} ON {{users}}.id = t.idUsuario';	// << The line that solved the problem

		$criteria->compare('{{users}}.username', $this->userName, true);	// << changed this line too

	}

	

	$criteria->compare('id',$this->id);

	

	// ....

	

	return $criteria;

}

public function getSum(){

	$criteria = $this->getSearchCriteria();

	$criteria->select='SUM(value)';


	return $this->commandBuilder->createFindCommand($this->getTableSchema(),$criteria)->queryScalar();

}

thanks again