How to use YII's createCommand to also return total items?

Question

Let's say I do a simple query:

$products = 
 Yii::app()->db->createCommand()->setFetchMode(PDO::FETCH_OBJ)
  ->select('*')
  ->from('products')
  ->limit(9)
  ->queryAll();

Let's say there are 500 products in the database. Is there a way I can get YII to automatically return the total number (count) of products if the "limit" was included? Perhaps return an object like this:

$products->products = array( ... products ... )
$products->totalProducts = 500;

The problem is, if LIMIT is included, it will return items, and the count will therefore be 9. I want a solution whereby it will return the 9 items, but also the count of say 200 items if there were 200 items.

Answer 1

Why not an easy:

$сount = Yii::app()->db->createCommand('select count(*) from table')->queryScalar();
echo $count;

Answer 2

You'll either have to run two queries (a count(*) query without the limit and then the limited query) or you can send you can retrieve your products using CSqlDataProvider and let it do it for you. But it generally takes two queries.

Note: one of the nifty features in Yii 1.1.13 is that you can send your query builder command in to the CSqlDataProvider if you're going to be using a dataprovider. More information at on this pull request that fixed it. That way, you can both use the power of query builder while also being able to shift your data into a dataprovider. Previously you had to build your SQL statement manually or grab the queryText of the command.