Pagination And Insert

Facing with glitch of pagination

Let us imagine that we have configured view model and controller of news.

We havent pagination list (1,2,3,4,>,< etc) on page, but we have jquery callback at the end of page, which loads next page with limit of 5 news with DESC order by create_time.

we have 11 news.

in standart way it will add var $page to $_GET and works fine.

BUT!

  1. user loads 1-st page he see:

row11

row10

row9

row8

row7

  1. admin adds new row12 at news

  2. user loads page 2 to the end of page.

Pagination adds limit 5 and offset 5 to sql, and page1 is row{12-8} so he will see:

row11

row10

row9

row8

row7

row7

row6

row5

row4

row3

so the row7 will be shown twice.

As I see the solution is to send last id as offset.

but how to convert last id to real offset?

I will post part of code if you will request it. Its a bit huge with jquery abd a lot of conditions, so ask me what do you what to see

Code please

Ok, Here we go

Model News


<?php


/**

 * This is the model class for table "news".

 *

 * The followings are the available columns in table 'news':

 * @property string $id

 * @property integer $section_id

 * @property string $title_ru

 * @property string $title_en

 * @property string $text_ru

 * @property string $text_en

 * @property integer $likes

 */

class News extends CActiveRecord

{

	/**

	 * Returns the static model of the specified AR class.

	 * @param string $className active record class name.

	 * @return News the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'news';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('section_id', 'required'),

			array('title_en', 'oneOfTwoRequered', 'field'=>'title_ru'),

			array('title_ru', 'oneOfTwoRequered', 'field'=>'title_en'),

			array('title_en', 'requeredByAnother', 'field'=>'text_en'),

			array('text_en', 'requeredByAnother', 'field'=>'title_en'),

			array('title_ru', 'requeredByAnother', 'field'=>'text_ru'),

			array('text_ru', 'requeredByAnother', 'field'=>'title_ru'),

			

			array('section_id, likes', 'numerical', 'integerOnly'=>true),

			array('title_ru, title_en', 'length', 'max'=>255),

			array('id, section, section_id, title_ru, title_en, text_ru, text_en, likes, popularity, create_time, isDraft', 'safe'),

		);

	}

	

	public function requeredByAnother ($attribute, $params)

	{

		if((!empty($this->$params['field']))&&(empty($this->$attribute)))

		{

			$this->addError($attribute, Yii::t('news', 'This field is required, if {field} is not empty', array('{field}'=>News::getAttributeLabel($params['field']))));

			return false;

		}

		return true;

	}

	

	public function oneOfTwoRequered ($attribute, $params)

	{

		if((empty($this->$params['field']))&&(empty($this->$attribute)))

		{

			$this->addError($attribute, Yii::t('news', 'At least 1 of the field must be filled up properly'));

			return false;

		}

		return true;

	}

	

	

	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'section'=>array(self::BELONGS_TO, 'Sections', 'section_id'),

			'links'=>array(self::HAS_MANY, 'Links', 'news_id'),

			'newstags'=>array(self::HAS_MANY, 'NewsTags'.ucfirst((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')), 'news_id'),

			'newstags_en'=>array(self::HAS_MANY, 'NewsTagsEn', 'news_id'),

			'newstags_ru'=>array(self::HAS_MANY, 'NewsTagsRu', 'news_id'),

			'tags'=>array(self::HAS_MANY, 'Tags'.ucfirst((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')), array('tags_id'=>'id'), 'through'=>'newstags'),

		);

	}

	

	public function scopes()

	{

		return array(

			'published'=>((Yii::app()->user->isGuest)?(array('condition'=>'isDraft=0')):(array())),

			'recently'=>array(

				'condition'=>'`title_'.((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')).'` <> ""',

				'order'=>'create_time DESC, id DESC',

				'with'=>array('links','newstags'=>array('news_id'=>'t.id'),'newstags.tags'),

			),

			'popular'=>array(

				'condition'=>'`title_'.((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')).'` <> ""',

				'order'=>'popularity DESC',

				'with'=>array('links','newstags'=>array('news_id'=>'t.id'),'newstags.tags'),

			),

			'terrible'=>array('condition'=>'`section_id` = 1',),

			'comedy'=>array('condition'=>'`section_id` = 2',),

			'weird'=>array('condition'=>'`section_id` = 3',),

			'russia'=>array('condition'=>'`section_id` = 5',),

			'actual'=>array(

				'condition'=>'`title_'.((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')).'` <> ""',

			),

			'wthrelations'=>array(

				'with'=>array('links','tags'),

			),

		);

	}

	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'section_id' 	=> Yii::t('news', 'Section'),

			'section'		=> Yii::t('news', 'Section'),

			'create_time'	=> Yii::t('news', 'Create Time'),

			'title_ru'		=> Yii::t('news', 'Title Russian'),

			'title_en'		=> Yii::t('news', 'Title English'),

			'title'			=> Yii::t('news', 'Title'),

			'text_ru'		=> Yii::t('news', 'Text Russian'),

			'text_en'		=> Yii::t('news', 'Text English'),

			'tags_ru'		=> Yii::t('news', 'Tags Russian'),

			'tags_en'		=> Yii::t('news', 'Tags English'),

			'likes' 		=> Yii::t('news', 'Likes'),

			'isDraft'		=> Yii::t('news', 'Draft'),

			'popularity' 	=> Yii::t('news', 'Popularity'),

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;

		$criteria->with=array('section');

		$criteria->compare('t.id',$this->id,true);

		$criteria->compare('section.id', $this->section,true);

		$criteria->compare('t.section_id',$this->section_id);

		$criteria->compare('t.title_ru',$this->title_ru,true);

		$criteria->compare('t.title_en',$this->title_en,true);

		$criteria->compare('t.text_ru',$this->text_ru,true);

		$criteria->compare('t.text_en',$this->text_en,true);

		$criteria->compare('t.isDraft',$this->isDraft,true);

		$criteria->compare('t.likes',$this->likes);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}

continue in next post

Controller


	/**

	 * This is the default 'index' action that is invoked

	 * when an action is not explicitly requested by users.

	 */

	public function actionIndex($section=null, $id=null, $offset=null, $search=null, $lang=null, $page=1, $json=false)

	{

		$slogan_id = Yii::app()->session->get('sloganid', 0);

		if ((Slogan::model()->default()->current_language()->count())<=$slogan_id)

			$slogan_id = 0;

		$this->slogan = Slogan::getTextWthOffset($slogan_id);

		Yii::app()->session->add('sloganid', ++$slogan_id);

		$this->loadmore = 1;

//		if (isset($_GET['page'])) {$_GET['page'] = (int)$_GET['page'];}

		if ($search != null) {

			$this->actionSearch($search, $page);

			return true;

		}

		


		//if no lang folder -> redirect to folder

		if ((Yii::app()->controller->id == 'site')&&

		((Yii::app()->controller->action->id == 'index') or (Yii::app()->controller->action->id=='search') )&&

		(!preg_match('%/(ru|en)/%', Yii::app()->request->url)))

			$this->redirect('/'.Yii::app()->language.'/', 1);

		

		// Setting Page Title

		if (isset(Yii::app()->params['newsMenu'][Yii::app()->language])) {

			$titles = Yii::app()->params['newsMenu'][Yii::app()->language];

			foreach ($titles as $key=>$value)

				$titles[$key]=trim($value, '/');

			$titles = array_flip($titles);

			if (isset($titles[$section]))

				$this->pageTitle = yii::app()->name.' - '.$titles[$section];

			else 

				$this->pageTitle = yii::app()->name.' - '.Yii::t('index', 'Main');

		} else

			$this->pageTitle = yii::app()->name.' - '.Yii::t('index', 'Main');


			

			

		// if it is direct id request

		if (($id!=null)&&(!Yii::app()->request->isAjaxRequest))

		{

		$dataProvider = new CActiveDataProvider(News::model()->published()->recently(), array(

					'criteria'=>array(

						'condition'=>'t.id='.((int)$id),

						'limit'=>1,

					),

					'pagination'=>false,

			

		));

			//getting data from model

			foreach($dataProvider->getData() as $record) {

					$data = $record;

			}

			if (!isset($data))

				throw new CHttpException(404);


			Yii::app()->clientScript->registerScript('paginationactivator', "$('div#loadmoreajaxloadermanual').show()", CClientScript::POS_END);

			StringTools::googleanalitics();

						

			// render

			$this->render('application.views._news._news_row_full', array('data'=> $data, 'show_row_div'=>true, 'show_expanded'=>true));

			

			

		} else {		

			

		// escaping wrong $section

		if ((($section=='russia')&&(Yii::app()->language=='ru'))||

			(($section=='weird')&&(Yii::app()->language=='en')))

			throw new CHttpException(404, Yii::t('index', 'Page is not availible at this language'));

		if ((!in_array($section,array_keys(Yii::app()->params['sectionToId'])))and($section!=null)and($section!='popular'))

			throw new CHttpException(404);

		$model=null;

		

		//creating $model based on $section

		switch ($section) {

			case null: {$model = News::model()->published()->recently(); break;}

			case 'popular': {$model = News::model()->published()->popular(); break;}

			default: {$model = News::model()->published()->recently()->$section();}

		}

		$criteria = $model->dbCriteria;

		

		//ajax id

		if ($id!=null)

		{

			$item = new News();

			$item = $item->findByPk($id);

			if ($section == 'popular')

				$criteria->addCondition(array('`t`.`popularity` <= "'.$item->popularity.'"','`t`.`id` <> "'.$item->id.'"'));

			elseif ($section==null)

				$criteria->addCondition(array('`t`.`popularity` <= "'.$item->popularity.'"','`t`.`create_time` <= "'.$item->create_time.'"','`t`.`id` <> "'.$item->id.'"'));

			else 

				$criteria->addCondition(array('`t`.`create_time` <= "'.$item->create_time.'"','`t`.`id` <> "'.$item->id.'"'));

		}

		

		//creating pagination

		$pagination = new CPagination($model->count());

		$pagination->setPageSize(10);


		//escaping pagintion end and wrong pagination

		if ((int)$pagination->itemCount<=((int)$pagination->pageSize+(int)$pagination->offset))

			Yii::app()->clientScript->registerScript('paginationkiller', "$('div#loadmoreajaxloader').attr('killed','killed').hide(); $('div#loadmoreajaxloadermanual').hide()", CClientScript::POS_READY);

		if (((!Yii::app()->request->isAjaxRequest))&&((int)$pagination->pageSize*((int)$page-1)>(int)$pagination->offset))

			throw new CHttpException(404, Yii::t('index', 'Page is out of range'));

		$pagination->applyLimit($criteria);

		$model = News::model()->findAll($criteria);		

/*

		//creating CActiveDataProvider

		$dataProvider=new CActiveDataProvider($model, array(

			'pagination'=>$pagination,

			'criteria'=>$criteria,		

    	));

*/

		// For iOS App

		if ($json==true) 

		{

			$toJSON = array();

			foreach ($model as $item)

				$toJSON[$item->id]=array(

					'id'=>$item->id,

					'section_id'=>$item->section_id,

					'create_time'=>$item->create_time,

					'title'=>(Yii::app()->language=='ru')?($item->title_ru)<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />$item->title_en),

					'text'=>(Yii::app()->language=='ru')?($item->text_ru)<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />$item->text_en),//$item->text,

					'likes'=>$item->likes,

					'tags'=>$item->tags,

					'links'=>$item->links,

				);

			echo CJSON::encode($toJSON);

			Yii::app()->end();

		}

		if (Yii::app()->request->isAjaxRequest)

		{

			$this->renderPartial('news', array(

				'data'=>$model,

			), false, true);

			Yii::app()->end();

		}

		$this->render('news', array(

				'data'=>$model,

		));

	}}


	public function actionSearch($search, $page)

	{

		$this->loadmore = 1;

		$lang = Yii::app()->language;			

		$this->pageTitle = $this->pageTitle.' - '.Yii::t('index', 'Search');

		

 		$criteria=new CDbCriteria();

 		$criteria->addCondition('`title_'.((Yii::app()->language=='ru')?('ru')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />'en')).'` <> ""');

 		if (Yii::app()->user->isGuest)

 			$criteria->addCondition('isDraft=0');

 		

 		

		if (preg_match('/(?<=tag:).*/', $search, $match)) {

			$criteria->with =array('links', 'tags'=>array('condition'=>'title="'.$match[0].'"'));//ByAttributes(array('tags.title'=>$match[0]));

			$criteria->order='create_time DESC, popularity DESC';

		} else {

			$criteria->with =array('links', 'tags');//ByAttributes(array('tags.title'=>$match[0]));

			$criteria->select = "*, MATCH (title_$lang) AGAINST (:search) + MATCH (text_$lang) AGAINST (:search)  AS score";

			$criteria->addCondition("MATCH (title_$lang) AGAINST (:search) OR MATCH (text_$lang) AGAINST (:search)", 'AND');

			$criteria->params=array(':search'=>$search);

			$criteria->order = 'score DESC, popularity DESC';

		}

		

		//creating pagination

		//$count = News::model()->count($criteria);

		$pagination = new CPagination(News::model()->count($criteria));

		$pagination->setPageSize(10);

		

		//escaping pagintion end and wrong pagination

		if ((int)$pagination->itemCount<=((int)$pagination->pageSize+(int)$pagination->offset))

			Yii::app()->clientScript->registerScript('paginationkiller', "$('div#loadmoreajaxloader').attr('killed','killed').hide(); $('div#loadmoreajaxloadermanual').hide()", CClientScript::POS_READY);

		if (((!Yii::app()->request->isAjaxRequest))&&((int)$pagination->pageSize*((int)$page-1)>(int)$pagination->offset))

			throw new CHttpException(404, Yii::t('index', 'Page is out of range'));

		$criteria->together=true;

		$pagination->applyLimit($criteria);

		$model = News::model()->findAll($criteria);


		// For iOS App

		if ($json==true)

		{

			$toJSON = array();

			foreach ($model as $item)

				$toJSON[$item->id]=array(

					'id'=>$item->id,

					'section_id'=>$item->section_id,

					'create_time'=>$item->create_time,

					'title'=>(Yii::app()->language=='ru')?($item->title_ru)<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />$item->title_en),

					'text'=>(Yii::app()->language=='ru')?($item->text_ru)<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />$item->text_en),//$item->text,

					'likes'=>$item->likes,

					'tags'=>$item->tags,

					'links'=>$item->links,

				);

			echo CJSON::encode($toJSON);

			Yii::app()->end();

		}

		if (Yii::app()->request->isAjaxRequest)

		{

			$this->renderPartial('news', array(

				'data'=>$model,

			), false, true);

			Yii::app()->end();

		}

		$this->render('news', array(

			'data'=>$model,// $dataProvider,

			'search'=>$search,

			'searchtag'=>(isset($match)&&isset($match[0]))?$match[0]:null,

		));

	}


	

view

in layout at the end of content div


<?php IF (isset($this->loadmore)&&($this->loadmore!=0)):?>

<div id="loadmoreajaxloader" style="display:none;" page="<?php echo (isset($_GET['id'])?"1":"2");?>" <?php echo (isset($_GET['search'])?('search="'.CHtml::encode($_GET['search']).'"')<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />''));?>><center><img src="/images/ajax-loader.gif" /></center></div>

<div id="loadmoreajaxloadermanual" style="display:none;"><a href="#"><?php echo Yii::t('index', 'Load More') ?></a> </div>

<script type="text/javascript">

//<![CDATA[

$(window).scroll(function()

{

    if($(window).scrollTop() == $(document).height() - $(window).height())

    {

    	loadmore();

    }

});

//]]>

news.php


<?php

$this->widget('application.widgets.CNewsList', 

	array(

		'data'=>$data, 

		'search'=>isset($search)?$search:null, 

		'searchtag'=>isset($searchtag)?$searchtag:null

	)

);

	Yii::app()->clientScript->registerScript("likedislike_init", "makeWorkable_init();");

	Yii::app()->clientScript->registerScript('paginationactivator', "$('div#loadmoreajaxloadermanual').show()", CClientScript::POS_END);


?>

CNewsList


<?php

class CNewsList extends CWidget

{

	var $view='application.views._news._news_row_full';

	var $data;

	var $search=null;

	var $searchtag=null;

	

	public function init()

	{

		parent::init();

	}

	

	public function run()

	{

		StringTools::googleanalitics();

		if ((isset($this->data))&&($this->data!=null))

			foreach ($this->data	 as $key=>$item)

				$this->render($this->view, array(

					'data'=>$item,

					'show_row_div'=>true,

					'register_vote_js'=>true,

					'search'=>$this->search,

					'searchtag'=>$this->searchtag,

				));

	}

}


?>

js loadmore


function loadmore(){

    if (($('div#loadmoreajaxloader').attr('loading') == 'loading') || ($('div#loadmoreajaxloader').attr('killed') == 'killed')) {

    	$('div#loadmoreajaxloader').hide();

    	$('div#loadmoreajaxloadermanual').hide();

    	return false;    	

    }

    var up = '';

    if ($('div#loadmoreajaxloader').attr('search'))

    	up = '?search='+$('div#loadmoreajaxloader').attr('search')+'&page='+$('div#loadmoreajaxloader').attr('page');

    else

    	up = '?page='+$('div#loadmoreajaxloader').attr('page');

    $('div#loadmoreajaxloader').show();

    $('div#loadmoreajaxloader').attr('loading', 'loading');

    $.ajax({

    url: up,

    success: function(html)

    {

        if(html)

        {

        	makeWorkable_release();

            $("#postswrapper").append(html);

            $('div#loadmoreajaxloader').hide();

            $('div#loadmoreajaxloader').attr('page', parseInt($('div#loadmoreajaxloader').attr('page'))+1);

            if ($('div#loadmoreajaxloader').attr('killed')!='killed')

            	$('div#loadmoreajaxloader').attr('loading','');

            makeWorkable_init();

        }else

        {

            $('div#loadmoreajaxloader').html('');

            $('#loadmoreajaxloadermanual').hide();

        }

    }

    });

}


$(document).ready(function(){

    $('#loadmoreajaxloadermanual > a').click(function(){

    	loadmore();

    })

});