Yii Framework Forum: Count in AR - Yii Framework Forum

Jump to content

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

Count in AR Property 'group' Rate Topic: -----

#1 User is offline   Scryii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 30-September 10
  • Location:Germany, Munich

Posted 29 October 2010 - 07:31 AM

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
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 29 October 2010 - 07:41 AM

Maybe is a bug in ActiveRecord.

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

#3 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 29 October 2010 - 09:49 AM

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.
¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
0

#4 User is offline   Scryii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 30-September 10
  • Location:Germany, Munich

Posted 29 October 2010 - 11:11 AM

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

#5 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 29 October 2010 - 11:25 AM

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;


¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
0

#6 User is offline   Scryii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 30-September 10
  • Location:Germany, Munich

Posted 31 October 2010 - 02:10 AM

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
0

#7 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 31 October 2010 - 05:02 AM

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...
¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
0

#8 User is offline   Scryii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 30-September 10
  • Location:Germany, Munich

Posted 02 November 2010 - 02:53 PM

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 :rolleyes:

Thx for discussion !




Scryii
0

#9 User is offline   Antonio Ramirez 

  • Elite Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 1,448
  • Joined: 04-October 10

Posted 02 November 2010 - 03:11 PM

Thanks to you fpor sharing. Great to see you've got it.
¿How long would it take for you to understand that you own nothing in this world?

www.ramirezcobos.com
www.2amigos.us
www.github.com/tonydspaniard
www.github.com/2amigos


Posted Image
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