CSqlDataProvider and Pagination

Yii 1.1 General Discussion
1

Hi all,

I’m having trouble getting this working properly.

Im using CSqlDataProvider to select a number of houses closest from a latitude/longitude.

It’s all working fine except the pagination doesn’t happen properly.

The pagination shows the correct number of pages but each page shows every single house instead of what i’ve specified.

here is some relevant code:

// Select count of below query

$countSql=Yii::app()->db->createCommand(‘SELECT count(*) FROM (SELECT *,( 3959 * acos( cos( radians(’.$center_lat.’) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(’.$center_lng.’) ) + sin( radians(’.$center_lat.’) ) * sin( radians( latitude ) ) ) ) AS distance FROM tbl_forsale HAVING distance < ‘.$radius.’ ORDER BY distance) AS T;’)->queryScalar();

// Get the listings

$sql=‘SELECT * FROM (SELECT *,( 3959 * acos( cos( radians(’.$center_lat.’) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(’.$center_lng.’) ) + sin( radians(’.$center_lat.’) ) * sin( radians( latitude ) ) ) ) AS distance FROM tbl_forsale HAVING distance < ‘.$radius.’ ORDER BY distance) AS T;’;

$dataProvider=new CSqlDataProvider($sql, array(

‘totalItemCount’=>$countSql,

  'sort'=&gt;array(


'attributes'=&gt;array(


   'price', 'distance',


),

),

‘pagination’=>array(

'pageSize'=&gt;5,

),

));

$this->widget(‘zii.widgets.CListView’, array(

'dataProvider'=&gt;&#036;dataProvider,


'emptyText'=&gt;'There are 0 listings in view.',


'enablePagination'=&gt;true,


'enableSorting'=&gt;false,


'id'=&gt;'list-view-listings',


'itemView'=&gt;'_list',


'template'=&gt;'{sorter}{items}{pager}',


'htmlOptions'=&gt;array('class'=&gt;'list-view-listings'),

));

Any help would be appreciated thx

2

I had same problem. My mistake was that I put ; on the end of my SQL statement, but Yii pagination puts LIMIT 10 at end of sql statement. So my sql statement after generation done bay YII was SELECT … WHERE…; LIMIT 10. So LIMIT 10 didn’t go into into sql statement :)

You can check your sql at the end of page if you put in config/main:

‘log’=>array(

		'class'=&gt;'CLogRouter',


		'routes'=&gt;array(


			array(


				'class'=&gt;'CFileLogRoute',


				'levels'=&gt;'error, warning',


			),


			// uncomment the following to show log messages on web pages


			


			array(


				'class'=&gt;'CWebLogRoute',


                                    'levels'=&gt;'trace',


			),


			


		),


	),
3

Hey stipe,

Thanks for the response…and the answer.

I was worried about no one being able to find the solution to my problem.

You hit it on the nail.

Thank you very much.

4

Try adding totalItemCount’=>$count, $count->total number of items in table

5

Hi stipe and Firebat it´s posible to find a simple solution to your problems but first I´ll make some recomendation to your code. Please try to avoid as much as yo can to use sql statement and try to employ all the posibilities the framework provides. First Study in deep the ORM Active Record, read all about it…

I can send you some links that can be helpfull.

It´s important to point out that config/main has nothing to do with what you´re trying to do…

When you do that make a new filter keeping framework structure and set the pagination options later…

Please read all the material about the framework and after that begin coding…

Dont stop posting keep comunication that we can colaborate to keep us programming as good as it can…

we´re comunicating…