[Scryii]

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

[zaccaria]

Maybe is a bug in ActiveRecord.

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

[Antonio Ramirez]

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.

[Scryii]

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.

[Antonio Ramirez]

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;

[Scryii]

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

[Antonio Ramirez]

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...

[Scryii]

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

[Antonio Ramirez]

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