Count in AR

Hello,

using the following code:

$row=new BlogStat; //valid AR


$crit = new CDbCriteria();


$crit->condition = 'id > :id';


$crit->params = array(':id'=>0);


$count = $row->count($crit);


echo $count;

I get the right # of rows in the database.

Adding the group property:

    $row=new BlogStat;


$crit = new CDbCriteria();


$crit->condition = 'id > :id';


$crit->params = array(':id'=>0);


$crit->group = 'page';


$count = $row->count($crit);


echo $count;

I only get 1

Use the sql statement in mysql admin:

    SELECT * FROM `table` WHERE id > 0 GROUP BY page

I get the right # of rows (in mycase 634)

Any idea, where I’m wrong ?

Thx.

Scryii

Maybe is a bug in ActiveRecord.

Set on logging, and check which kind of query is performing Yii.

wow… interesting…

Could you do it this way to see what happens? -just curiosity, I think I know what could happen.

$row=new BlogStat;

$crit = new CDbCriteria();

$crit->select = '{{blogstat}}.*';

$crit->condition = '{{blogstat}}.id > :id';

$crit->params = array(':id'=>0);

$crit->group = '{{blogstat}}.page';

$count = $row->count($crit);

echo $count;

I know you can do it without the table alias, but I normally do it this way. Please, share results.

Thx for ideas.

I used the log component and got the following SQL query in case 1:

SELECT COUNT(*) FROM table t WHERE id > :id

Perfect, that’s right.

But in Case 2 the query looks like:

SELECT COUNT(DISTINCT page) FROM table t WHERE id > :id GROUP BY page

Hmmm.

Any idea, why the DISTINCT is in the query ?

However, when I copy and paste the sql query …

Oh … just a moment -> I have an idea where I could be wrong …

Scryii

P.S.: When I include the table alias, I get an exception.

forget the table alias then (should be t -I forgot about that, is by default)… I think is the ‘select’ criteria is what fails, I thought that by forcing the select criteria to be just ‘*’ would be ok.

$row=new BlogStat;

$crit = new CDbCriteria();

$crit->select = '*';

$crit->condition = 'id > :id';

$crit->params = array(':id'=>0);

$crit->group = 'page';

$count = $row->count($crit);

echo $count;

The default value of ‘select’ is always ‘*’

There is a other Property called ‘distinct’ - here you can set distinct true / false

However, I’m afraid I was wrong with my sql statement (it’s mote complex)

I give you the solution when I found it

First I have to handle my JOIN problem

Scryii

Yeah… but, your criteria is ‘DISTINCT page’ not ‘*’ as it is set by default… this is why i was saying ‘forcing’.

Anyway, I wait for your solution. Will be nice to see…

So,

my SQL statement was wrong, it was not a yii problem.

Actually I needed the following SQL statement:

        	SELECT COUNT(*) AS CNT, page FROM `table` WHERE page > 0 group by page ORDER BY CNT DESC

And now it’s simple to put it in a yii statement:

      	$crit = new CDbCriteria();


      	$crit->select = 'COUNT(*) AS CNT,page';


      	$crit->condition = 'page > :page';


      	$crit->params = array(':page'=>0);


      	$crit->group = 'page';


      	$crit->order = ‘CNT DESC’;


      	$rows=$ar->findAll($crit);

Next time I will double-check the sql statements first

Thx for discussion !

Scryii

Thanks to you fpor sharing. Great to see you’ve got it.