Yii Framework: Clistview reporting incorrect number of results

I am using CActiveDataProvider with CDbCriteria to search through some related models, using multiple related models. The code to generate the results is as below:

$criteria->select = '*, ( 3959 * acos( cos( radians(' . $latitude . ') ) 
* cos( radians( latitude ) ) * cos( radians( longitude ) - 
radians(' . $longitude . ') ) + sin( radians(' . $latitude . ') ) 
* sin( radians( latitude ) ) ) ) * 1.609344 AS distance'; 
//Basically just calculating distance from an input point
$criteria->with = array('keywords', 'coupons', 'jobs');
$criteria->order = 'distance asc';
$criteria->having = 'distance < 20';
$criteria->compare('name', $this->searchTerm, true, 'AND');

$dataProvider = new CActiveDataProvider('Store', array(
'criteria'=>$criteria));

The search works fine and gets the results as exp开发者_StackOverflow中文版ected. The problem is that the CListview reports the number of results correctly, but shows pagination anyways. For example: 'Displaying 1-7 of 31 results, and pagination is shown. Clicking on pages 2, 3, 4 show no results.

Is this a bug, or am I doing something wrong?

Sometimes with a complex query, you have to manually provide the number of rows as item count, try passing the count with your dataprovider with an attribute like:

'totalItemCount'=>$count,

http://www.yiiframework.com/doc/api/1.1/CDataProvider#totalItemCount-detail

Try commenting out this line

$criteria->with = array('keywords', 'coupons', 'jobs');

and seeing if the pager and item count responds correctly. I found that this was my case, so I have to take a performance hit and let the thing lazy load.

I've had a similar mismatch between the count and the actual list of records when I tried to use a query that included a GROUP BY clause. I notice that you have a HAVING clause in your query. I imagine this could cause similar problems and a quick look at the Yii source here shows that the presence of either a GROUP BY or HAVING clause causes the counting to be handled differently than without either of those clauses.

It looks like you used $criteria->having to allow using the calculated column distance. A quick test if this is the problem would be to use $criteria->condition instead and set it equal to the complete calculation like this:

$criteria->condition = '( 3959 * acos( cos( radians(' . $latitude . ') ) 
* cos( radians( latitude ) ) * cos( radians( longitude ) - 
radians(' . $longitude . ') ) + sin( radians(' . $latitude . ') ) 
* sin( radians( latitude ) ) ) ) * 1.609344 < 20';

The condition property is used to generate the WHERE clause, which does not cause problems with count like HAVING may.

By the way, it's extremely useful to be able to inspect the actual queries that Yii is making. It can be surprising and show problems or inefficiencies. You can get Yii to output the query info at the bottom of every webpage by setting this in your protected/config/main.php:

'components'=>array(
   'db'=>array(
        // DB connection info as usual
    ),
    'log'=>array(
        'routes'=>array(
           array(
                'class'=>'CWebLogRoute',
                'levels'=>'trace',
            ),
        ),
     ),
),

I had to hack CJoinElement in CActiveFinder to fix this. The count function in CJoinElement is the one that does the real work to display the total count in the summary. This resets the group and having components of the criteria. Removing the reset fixed the problem. See https://github.com/yiisoft/yii/issues/167