CDbCriteria & GROUP BY + COUNT()

hi!

i try for several hours to specify a CDbCriteria for counting entrys with the same value.

i want have something like this:




SELECT artist.name, count(artist.name)

FROM concerts

LEFT OUTER JOIN artists artist ON (artists_id=artist.id)

GROUP BY artist.name



I tried to do it with this code:




$criteria = new CDbCriteria;

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

$criteria->group='artist.name';

$criteria->select ='artist.name,count(artist.name)';



when i use this CDbCriteria i get this error:


Active record "Concert" is trying to select an invalid column "artist.name". Note, the column must exist in the table or be an expression with alias.

anyone has a idea how to use GROUP BY & COUNT() in combination with a CDbCriteria?

so… nobody else had to deal with a problem like this before?

or maybe CDbCriteria is not the right thing for my problem and i must write a own new query builder?

You can declare STAT relation in your main model and through it get artist count.

Example:




// YourMainModel.php

public function relations()

{

    return array(

        'artist' => array(self::HAS_MANY, 'Artist', 'some_id'),

        'artistCount' => array(self::STAT, 'Artist', 'some_id'),

    );

}

echo $mainModel->artistCount;



If you want to select only few fields from related model (artist) instead of a whole model, you can set proper select value in criteria. with is used to perform eager loading, so you can of course ‘preload’ artistCount too.




$criteria = new CDbCriteria();

$criteria->with = array(

    'artist' => array('select' => 'name'),

    'artistCount',

);

echo $mainModel->artist[0]->name; // already loaded with use of 'with'

echo $mainModel->artistCount; // already loaded with use of 'with'



And finally you actually don’t have to write all these code, because you can get artist count through its AR method count()




$criteria = new CDbCriteria();

$criteria->with = array(

    'artist' => array('select' => 'name'),

    'artistCount',

);

echo $mainModel->artist[0]->count(); // Artist is also AR instance and have method count()



Hope this helps a bit, and you really have to test it.

Cheers

ohh this looks like a great idea!

i wil ltest it this week

thanks!

THANKS AGAIN!!!

everything you told me was 100% correct, haha… i think i get the main idea

now i have a new problem:

how can i order the result by artistCount?

everytime i try to use the "order"-keyword i gget error messages like "unkown column name artistCount" and so on…

any ideas?

I’ve got a similar issue.




        $criteria = new CDbCriteria;

	$criteria->select = "t.*, COUNT(bid.bidID) AS bidTotal";

	$criteria->condition = "userID=:userID AND bidTotal > 0";

	$criteria->params = array(':userID'=>Yii::app()->user->userID);

	$criteria->with = 'bid';

	$criteria->group = "t.jobID";

        $models = Jobs::model()->findAll($criteria);



I’m trying to only display rows which have 1 or more bids. But it says it’s

How do I insert a condition on bidTotal? Thanks

In your query, "bidTotal" is an aggregated column that is the result of "group by" operation.

It can be used to sort the result set, but is not applicable in "where" clause that should be executed before the "group by" operation.

Take a look at this wiki for an working solution:

Searching and Sorting by Count of Related Items in CGridView

Your solution here :)