Counting rows in complex sql queries

In MySQL there is a nice trick to count the resulting rows no matter how complex the sql query is.

begin the SQL statement like this:

SELECT SQL_CALC_FOUND_ROWS column1, column2, column3, etc.

and RIGHT AFTER the execution of the sql statement, execute this:

SELECT FOUND_ROWS()

And now even in queries with GROUP BY or other complex queries you can have the number of rows.

One usage could be for totalItemCount in pagination

Don’t forget to click on the green plus sign in case you like this tip. Thank you (:

As you wrote this works only in MySql… and a second SELECT is needed (database queries can be slow)…

On any request that returns an array of objects like the queryAll() you can use count() to get the number of array elements (returned rows)

Sure this is an alternative way. The above tip gives one more possibility to mysql users!

mdomba there is a problem with your solution in a particular situation:

I would like to use CSqlDataProvider and how would I get the totalItemCount since the sql statement is executed when the CSqlDataProvider object is created ?

In both my solution and yours I have to execute the same sql statement ONE more time before the

new CSqlDataProvider($sql, array( ... )

Any other more efficient way provided by Yii framework?

Note: in documentation is saying that is mandatory to provide the totalItemCount in order to get correct pagination

as I wrote… "my" solution can be used when you get an array of objects…

For CSqlDataProvider there is no other solution that I know of… and that is shown even in the documentation example - http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

You don’t need to execute the exact same query… you just need to get the count() for the same criteria as the first query…