Yii Framework Forum: Why count() of Active Record has so odd behaviour? - Yii Framework Forum

Jump to content

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

Why count() of Active Record has so odd behaviour? odd behaviour of count() Rate Topic: ***** 1 Votes

#1 User is offline   Andrey G. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 25-August 09
  • Location:Russia

Posted 22 April 2010 - 07:56 AM

Hi.

Well, i need next SQL:
SELECT COUNT(DISTINCT(requestId)) FROM `offer` `t` WHERE ((t.cid=17) AND (t.status<>'deleted')) AND ((marker='unread') AND ((created>=:start) AND (t.created<=:stop))) ORDER BY created DESC


To get info for pagination. And right now i can't do it with AR, cause COUNT(DISTINCT(requestId)) is always replaced with COUNT(*).

Probably the right solution is using 'select' property of DBCriteria for count, but i'm not sure about hidden problems that can occur.

I found that 'patch' here

	public function createCountCommand($table,$criteria) {
		$this->ensureTable($table);
		if($criteria->group){
			$select=is_array($criteria->group) ? implode(', ',$criteria->group) : $criteria->group;
			$criteria->select = 'COUNT(DISTINCT (' . $select . '))';
			$criteria->distinct = false; // ADDED

		} else if ($criteria->distinct) {
			$select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select;
			$criteria->select = 'COUNT(DISTINCT(' . $select . '))';
			$criteria->distinct = false; // ADDED
		} else {
			$criteria->select='COUNT(*)';
		}

		return $this->createFindCommand($table, $criteria);
	}



So my question is - what problems can occur with that solution? Looks like there is no real problem, but who knows?!

Qiang and rest team...what is your answer? :)
Just Do It!
0

#2 User is offline   ppy 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 21-May 09

Posted 28 April 2010 - 02:24 AM

up.

urgent for us.
maybe we should make patch and post it as issue in code.google?
0

#3 User is offline   xrx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 11-November 09
  • Location:Bosnia And Herzegovina

Posted 28 April 2010 - 08:37 AM

IMHO the best solution (for mysql at least) would be using SQL_CALC_FOUND_ROWS in query and SELECT FOUND_ROWS() after that to obtain exact row count for that query. It's much faster and it's reliable ;)
0

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 29 April 2010 - 01:34 AM

Should be fixed in SVN now.
0

#5 User is offline   Andrey G. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 25-August 09
  • Location:Russia

Posted 29 April 2010 - 02:34 AM

View PostMike, on 29 April 2010 - 01:34 AM, said:

Should be fixed in SVN now.



Well, i just looked at trunk and found next "solution":
public function createCountCommand($table,$criteria,$alias='t')
	{
		$this->ensureTable($table);
		if($criteria->alias!='')
			$alias=$criteria->alias;
		$alias=$this->_schema->quoteTableName($alias);
		if($criteria->distinct)
		{
			if(is_array($table->primaryKey))
			{
				$pk=array();
				foreach($table->primaryKey as $key)
					$pk[]=$alias.'.'.$key;
				$pk=implode(', ',$pk);
			}
			else
				$pk=$alias.'.'.$table->primaryKey;
			$sql="SELECT COUNT(DISTINCT $pk)";
		}
		else
			$sql="SELECT COUNT(*)";
		$sql.=" FROM {$table->rawName} $alias";
		$sql=$this->applyJoin($sql,$criteria->join);
		$sql=$this->applyCondition($sql,$criteria->condition);
		$command=$this->_connection->createCommand($sql);
		$this->bindValues($command,$criteria->params);
		return $command;
	}


Well, it's not correct solution, because in our case 'requestId' is not PrimaryKey. It's ForeignKey! So as i understand, that 'fix' will not solve existing problems. Why so many restrictions?
Just Do It!
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 29 April 2010 - 02:49 AM

It's not really a restriction, but rather a question of how to create a more general solution. So what's your proposal?

I thought about optionally setting the DISTINCT column name in distinct:

is_bool($criteria->distinct) : Current behavoir
is_string($criteria->distinct): Use this as column name

0

#7 User is offline   Andrey G. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 25-August 09
  • Location:Russia

Posted 29 April 2010 - 04:27 AM

View PostMike, on 29 April 2010 - 02:49 AM, said:

It's not really a restriction, but rather a question of how to create a more general solution. So what's your proposal?

I thought about optionally setting the DISTINCT column name in distinct:

is_bool($criteria->distinct) : Current behavoir
is_string($criteria->distinct): Use this as column name



Hmmm...looks like that solution would be more universal! What core-team will say? :)
Just Do It!
0

#8 User is offline   Junior - df9 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 416
  • Joined: 24-May 09
  • Location:Brazil

Posted 29 April 2010 - 05:19 AM

View PostAndrey G., on 29 April 2010 - 04:27 AM, said:

Hmmm...looks like that solution would be more universal! What core-team will say? :)


hey, guys,

"SELECT COUNT(1)"

is faster than

"SELECT COUNT(*)"

regards!!

:)
______________________________________
Junior
df9.com.br
Linux Registered User #364954
GNU/Linux: together we're ready!
0

#9 User is offline   ppy 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 21-May 09

Posted 30 April 2010 - 02:23 AM

View PostAndrey G., on 29 April 2010 - 04:27 AM, said:

Hmmm...looks like that solution would be more universal! What core-team will say? :)


So, quang, will you fix it?
0

#10 User is offline   Andrey G. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 25-August 09
  • Location:Russia

Posted 04 May 2010 - 02:42 AM

View Postppy, on 30 April 2010 - 02:23 AM, said:

So, quang, will you fix it?


I hoped that some fixes will be at 1.1.2, but looks like not. So is it planned to fix or just trashed?
Just Do It!
0

#11 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 04 May 2010 - 02:53 AM

You could open a ticket as a reminder for the dev team. I understand that these kind of changes need careful planning instead of doing a rushed implementation that makes problems later.
0

#12 User is offline   Andrey G. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 25-August 09
  • Location:Russia

Posted 06 May 2010 - 03:14 AM

Ok, looks like dev-team is reading forum time from time, that's why i opened ticket:
http://code.google.c...Stars%20Summary
Just Do It!
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