CActiveRecord->count() DISTINCT

Yii 1.1 General Discussion
1

Hi everyone, I’m new to Yii and I have a problem with CActiveRecord->count().

My code is as follows:


		

$criteria = new CDbCriteria(array(

 'select' => '*',

 'with' => array(

   'model' => array(

     'select' => FALSE,

     'joinType' => 'LEFT OUTER JOIN',

     'condition' => 'model.id = :id',

     'params' => array(':id' => 2,),

   ),

   'attributes' => array(

     'select' => 'value',

     'joinType' => 'LEFT OUTER JOIN',

   ),

 ),

));


$criteria->addInCondition('attributes.value', array_values(array('Colour' => 'Red',  'Plug' => 'UK',)));

			

$type = ProductType::model()->find($criteria);


echo $type->count($criteria);

My problem is that this last line calls the following SQL:


SELECT COUNT(DISTINCT `t`.`id`) FROM `product_type`...

Yet I need the query not to include the DISTINCT keyword. I’m not completely sure why this is happening seeing as I don’t set ‘distinct’ in my criteria.

Does anyone have any ideas?

Thanks

2

I’m not actually sure what the problem is, invoking DISTINCT on a primary key doesn’t make any difference because a primary key is already distinct/unique. CDbCommandBuilder::createCountCommand() is what CActiveRecord::count() invokes and if you really don’t want the distinct, you can specify the count select manually just before you call CActiveRecord::count().




$criteria->select = 'COUNT(`t`.`id`)';

echo $type->count($criteria);
3

Hi

Well I get duplicate records due to my joins, this may mean that my database structure isn’t perfect, yet I would like to count these duplicate records.

Thanks

4

Hi

I was just wondering if anyone had any solutions?

Thanks

5

i encountered a similar problem. only to discover a strange behavior.

this my relation


'trip'=>array(self::BELONGS_TO, 'Holidays', 'tour'),

Initially this was my search




public function search()

	{

		// @todo Please modify the following code to remove attributes that should not be searched.


		$criteria=new CDbCriteria;

		

		$criteria->with = array('trip'=>array('select'=>'id, title'));

		$criteria->together = true; 

		

		$criteria->condition = 't.tour = :tid';

		$criteria->params = array(':tid' => $_GET['id']);

		

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

		$criteria->compare('trip.tour',$this->tour, true);

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

		$criteria->compare('starrate',$this->starrate,true);

		$criteria->compare('comfort',$this->comfort,true);

		$criteria->compare('departure',$this->departure,true);

		$criteria->compare('priceperson',$this->priceperson,true);

	        $criteria->compare('singlesupplement',$this->singlesupplement,true);

		$criteria->compare('deposit',$this->deposit,true);

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

With the above as is it could only return a single row. Then i realized when i comment the following 3 columns it worked and return the number of rows i expected based on my query condition. i don’t understand how the 3 columns could be problematic. There are of decimal(10,2)




public function search()

	{

		// @todo Please modify the following code to remove attributes that should not be searched.


		$criteria=new CDbCriteria;

		

		$criteria->with = array('trip'=>array('select'=>'id, title'));

		$criteria->together = true; 

		

		$criteria->condition = 't.tour = :tid';

		$criteria->params = array(':tid' => $_GET['id']);

		

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

		$criteria->compare('trip.tour',$this->tour, true);

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

		$criteria->compare('starrate',$this->starrate,true);

		$criteria->compare('comfort',$this->comfort,true);

		//$criteria->compare('departure',$this->departure,true);

		//$criteria->compare('priceperson',$this->priceperson,true);

	        //$criteria->compare('singlesupplement',$this->singlesupplement,true);

		$criteria->compare('deposit',$this->deposit,true);

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

The 3 columns fields are all of decimal data type decimal(10,2)

6

What U need is to group your criteria, something like $criteria->group = ‘t.id’, this will save U from duplicating and also provide correct counting.

1 Like