Scryii
(Dirk Gollnick)
October 29, 2010, 12:31pm
1
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
(Matteo Falsitta)
October 29, 2010, 12:41pm
2
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.
Scryii
(Dirk Gollnick)
October 29, 2010, 4:11pm
4
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;
Scryii
(Dirk Gollnick)
October 31, 2010, 7:10am
6
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…
Scryii
(Dirk Gollnick)
November 2, 2010, 7:53pm
8
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.