Yii Framework Forum: CDbCriteria & GROUP BY + COUNT() - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CDbCriteria & GROUP BY + COUNT() Rate Topic: -----

#1 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 41
  • Joined: 03-May 11

Posted 04 November 2011 - 06:53 PM

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?
0

#2 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 41
  • Joined: 03-May 11

Posted 06 November 2011 - 10:59 AM

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?
0

#3 User is offline   ManInTheBox 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 75
  • Joined: 17-June 11

Posted 06 November 2011 - 12:52 PM

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
0

#4 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 41
  • Joined: 03-May 11

Posted 06 November 2011 - 03:00 PM

ohh this looks like a great idea!
i wil ltest it this week

thanks!
0

#5 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 41
  • Joined: 03-May 11

Posted 12 December 2011 - 03:30 PM

View PostManInTheBox, on 06 November 2011 - 12:52 PM, said:

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


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?
0

#6 User is offline   auzadventure 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 20-August 15

Posted 22 August 2015 - 07:23 AM

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

Quote

Unknown column 'bidTotal' in 'where clause


How do I insert a condition on bidTotal? Thanks
0

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,062
  • Joined: 16-February 11
  • Location:Japan

Posted 22 August 2015 - 06:22 PM

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
0

#8 User is offline   Arun Ab 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 18-February 13

Posted 10 August 2016 - 08:02 AM

View Postauzadventure, on 22 August 2015 - 07:23 AM, said:

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


Your solution here :)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users