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
if(isset($query['order']))
$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->setText('');
after
$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:
[list=1]
[*]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)!
[/list]
If I don’t understand something, or you have better ideas, please comment.