DAO: Partial reset of a query

Hi to everybody!

If you use DAO and build a query with many options: JOIN, ORDER, LIMIT etc. You execute the query and then e.g. you need to get the overall count of rows. So you need to reset LIMIT and OFFSET parts (and aesthetically the ORDER BY part).

But you have no smart ways to do it (as I realized learning CDbCommand class). And if for limit and offset you can use the following trick:

		$this->listQuery->limit = -1;

		$this->listQuery->offset = -1;

You cannot use anything like this for the ORDER BY part due to


			$sql.="\nORDER BY ".$query['order'];

in CDbCommand class.

What’s the mean to use isset($query[‘order’]) instead of !empty($query[‘order’]) in CDbCommand::buildQuery()? We could avoid these problems.

Moreover I have to use



		$this->listQuery->select(new CDbExpression("COUNT(*)"));

to make the query be rebuilt at least in SELECT part. But it doesn’t help with ORDER BY (due to mentioned isset()). And you also cannot reset WHERE, GROUP BY and HAVING parts.

In the described situation I have 2 ways:


[*]To use -1 for limit and offset. To ignore ORDER BY part. It doesn’t affect performance in this case, and you can redefine this part in other cases.

[*]To create another query and copy the necessary parts to it. That’s no good for queries with JOIN parts (it seems you get some risks)!


If I don’t understand something, or you have better ideas, please comment.

No, we cannot ignore ORDER BY in queries with an aggregate function like COUNT(*) (without GROUP BY). Any advanced DB denies it, and that’s right.

The way 2 entail the problem with quotes.

So if I try to clone:

$q->from = $this->listQuery->from;

$this->listQuery->from is returned quoted.

So now I don’t have any ways to clone CDbCommand query command.

It’s very sad, that we can not reset ‘where’ clause.