CActiveDataProvider + CDbCriteria pagination count doesn't match number of records

I’m 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 expected. 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?

I would guess that it is to do with the ‘together’ parameter for the CDbCriteria (although I don’t see it in the code you give).

If ‘together’ is true then the CActiveDataProvider pulls everything together in one big query, which may mean multiple lines per model, thus giving the erroneous count that you notice. If you set ‘together’ => false, then the CActiveDataProvider should execute one query for the model table, then additional queries for the join tables, and collate all that data itself (so giving the right count, hopefully).

I tried setting together to both true and false, but there was no change in the results. Is there any way I can fetch the number of results that’s shown in the summary in the model?

I’m trying to use $dataProvider->setTotalItemCount($numberResults); right before return $dataProvider in the model. Any idea how I might do this?

Edit:

OK, it’s hacky, but here’s what I’ve done -

$numResults = count($dataProvider->getData());


    


if($numResults == 0) {


  $criteria->condition = '1=0'; 


  //I don't know how to return null <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' />


   return new CActiveDataProvider('Store', array('criteria'=&gt;&#036;criteria));


}





&#036;dataProvider-&gt;setTotalItemCount(count(&#036;dataProvider-&gt;getData()));

If the $numberResults is 0, $dataProvider->setTotalItemCount(count($dataProvider->getData())); doesn’t work, so I’m forcing a null result.

Is there a better way to do this?

Well I’ve had the same problem, and found your post on SO. I applied your hack, and have been happy with it (

$dataProvider->setTotalItemCount(count($dataProvider->getData()));

)… till I had aggregated data > 10 items. In that case, you realize that the pager in the footer is still incorrect, and the header is in fact {x MOD pageSize}, i.e. the first page would be ‘1-10 of 10’, and the last page ‘1-x of 10’.

Anyway, I digged the whole Web looking for a solution

I nearly found what I thought was the solution with

$dataProvider->pagination->setItemCount(count($dataProvider->getData()));

but it just gave another issue while solving almost nothing.

THE solution is:

(in model)

public function groupedSearch() {

  $criteria = new CDbCriteria;

  $criteria->select = '…';

  $criteria->with = array(…);

  $criteria->group = '…';

  $dp = new CActiveDataProvider($this, array('criteria'=>$criteria));

  $dp->setTotalItemCount(count($this->findAll($criteria)));

  return $dp;

}

I had similar problem when using group in query. There’s my solution:

Add class CActiveDataProviderEx to your project:

class CActiveDataProviderEx extends CActiveDataProvider {


    protected function calculateTotalItemCount() {

        $baseCriteria=$this->model->getDbCriteria(false);

	if ($baseCriteria!==null)

		$baseCriteria=clone $baseCriteria;

        //You can get real records count only in this way (when you use JOIN and GROUP BY)

	$count=count($this->model->findAll($this->getCriteria()));

	$this->model->setDbCriteria($baseCriteria);

	return $count;

    }

}

and use it instead CActiveDataProvider:

    $productCategory = new CActiveDataProviderEx('ProductCategory', ...);

Hope this helps somebody.

Thanks very much. This realy help me a lot.

Why has this bug still not been fixed?

I had the same problem.

Looking the code I’ve found the following solution.

Instead of setting the dataprovider criteria:

$dataprovider->criteria = $criteria

I set dataprovider->model criteria:

$dataprovider->model->setDbCriteria($criteria)

And it worked!

Hope it helps somebody.

Thanx for tutorial, sir.

I can solve my problems.

This works like charm

i have just added line

$dataProvider->setTotalItemCount(count(Modelname::model()->findAll($criteria)));

And it worked for me …

Thanks

Shreyas.

$criteria->together

is support pagination

  $user_id = Yii::app()->user->id ;

     User::model()->with('profile','ta_asso','tag')->findAllByAttributes(array('parent_id'=>$user_id));

        $criteria = new CDbCriteria();

        $criteria->with = array('profile','ta_asso','tagg');

		$criteria->together = true;

        $criteria->condition = "parent_id ='".$user_id."'";

        $sort = Yii::app()->request->getParam("sort");

        $date = Yii::app()->request->getParam("date");

	   

	   $tag = Yii::app()->request->getParam("tag");  

     if(!empty($sort)){ 

              $criteria->order = 'profile.firstname '.$sort ; 

            }

      if(!empty($date)){

           $criteria->order = 'create_at '.$date; 

          }

     if(!empty($tag)){

           $criteria->order = 'tagg.title '.$tag; 

          }

		  if(isset($_GET['title'])){

		  $criteria->condition="profile.firstname like :input_script OR profile.lastname like :input_last OR tagg.title like :input_tag";

          $criteria->params=array(":input_script"=>"%".$_GET['title']."%",":input_last"=>"%".$_GET['title']."%",":input_tag"=>"%".$_GET['title']."%");


		 }

		    $count=User::model()->count($criteria);

			$pages=new CPagination($count);

			$pages->pageSize=2;

			$pages->applyLimit($criteria);

			$data = User::model()->findAll($criteria);

THE solution is:

(in model)

public function groupedSearch() {

  $criteria = new CDbCriteria;

  $criteria->select = '…';

  $criteria->with = array(…);

  $criteria->group = '…';

  $dp = new CActiveDataProvider($this, array('criteria'=>$criteria));

  $dp->setTotalItemCount(count($this->findAll($criteria)));

  return $dp;

}

It is working like a charm for me.

You save my all day!

Thank you!

thanks i was also using that radius calculation querry and this one below helps me

$dp->setTotalItemCount(count($this->findAll($criteria)));