Select2 Infinite Scroll With Remote Data in Yii

I was able to set up the infinite scroll of select2 in Yii. I am glad to share it with you. The demo and source code can be found here. However, select2 for Yii can be found here which uses a widget.

The controller action is shown below

public function actionSearch() 
	 {
		if(Yii::app()->request->isAjaxRequest)
		{
			if (isset($_GET['term'])&& !empty($_GET['term'])) 
			{
			 	// http://www.yiiframework.com/doc/guide/database.dao	
				$search = "SELECT SQL_CALC_FOUND_ROWS pm.title as text, pm.poem_id as id,bio.pix as pix,n.penname as name FROM pty_poem pm
				LEFT JOIN pty_biodata bio ON pm.poet_id = bio.poet_id LEFT JOIN pty_name n ON pm.poet_id = n.poet_id  WHERE category IN ( Select cat.value from pty_category cat where cat.category LIKE CONCAT('%', :title, '%')) Limit 5 ";
				
				$command = Yii::app()->db->createCommand($search);
				$command->bindValue(":title", '%'.$_GET['term'].'%', PDO::PARAM_STR);
				$result =$command->queryAll();	
				
				$total_rows = Yii::app()->db->createCommand('SELECT FOUND_ROWS() AS total')->queryRow();				
				$total = $total_rows['total'];

				 echo CJSON::encode(array('result'=>$result,'total'=>$total));
				 Yii::app()->end();
			}
				
		 } else
				throw new CHttpException(403, "The requested page does not exist");    	
	}

The SQL_CALC_FOUND_ROWS is a MySQL function that selects all the rows that can be found given that there is no limit. But it does not give you the result. Another query has to be executed with FOUND_ROWS() to retrieve the found rows executed before it. This is needed in order to know the total number of rows searchable for any search string. The client code would use this to know if it should continue sending request for more pages.

Assuming that you downloaded and installed the Yii select2, you can have this below

<?php		
		echo CHtml::textField('test','',array('id'=>'test','style'=>'height:30px;width:350px;'));					
			
		$this->widget('ext.select2.ESelect2',
                      array(
                           'selector' => "#test",
                           'options'  => array(                              
                               'placeholder'        => 'Search poems by category',
                               'quietMillis' => 200,
							   'minimumInputLength'=>3,
                               'ajax'               => array(
                                   'url'      => $this->createUrl("poem/search"),
                                   'dataType' => 'json',
								   'type'=>'GET',
                                   'data'     => 'js: function(term,page) {
                                                        return {
                                                            term: term,
															page: page,
                                                          page_limit: 5,
                                                        };
                                                  }',
                                   'results'=>'js:function (data, page) {
           			 					var more = (page * 5) < data.total;          
            							return {results: data.result, more: more};							
						}',
                      ),
                               'formatResult'=>'js:function(movie) {
			                   var folder = "'.Yii::app()->request->baseUrl.'/images/";   
            var markup = "<table class=movie-result><tr>";
			if (movie.pix !== undefined) {
			markup += "<td class=movie-image><img class=image-size src="+folder+movie.pix +" /></td>";
		}else
			{
				markup += "<td class=movie-image><img class=image-size src="+folder+"avater.png" + " /></td>";
			}
			
			markup += "<td class=movie-info><div class=movie-title>" + movie.text + "</div><div class=author-name>by "+movie.name+"</div>";
            markup += "</td></tr></table>"
            return markup; 
        						}',
                               'formatSelection'=>'js:function(data) {                                   
								top.location = "'.$this->createUrl("poem/view").'"+"/"+data.id
									
									return data.text;
                                }',
								'dropdownCssClass'=>"bigdrop", // apply css that makes the dropdown taller
								'escapeMarkup'=>'js:function (m) { return m; }',
								'formatNoMatches'=>'js:function () { return "No category match"; }',		 
                           ),
                      ));			
?>		
</form>

I customized the look and feel with a little css to suite my taste. It's not bad to show you

<style type="text/css">
	.image-size{
		height:50px;
		width:50px;
		float:left;
	}
	.movie-title{
		text-align:left;
		float:left;
		font-size:14px;
	}
	.author-name{
		font-style:italic;
		clear:both;
		font-size:12px;
	}
	.movie-info{
		padding-left:0;
	}
	.movie-result{margin-bottom:0;}
	.movie-image{
		width:55px;
	}
</style>

The page_limit is 5. There should have been a way of dynamically including it in the search query in the action but I'm trying to avoid sql injection. It needs some param binding. I welcome any suggestion on that. Clicking any highlighted selection would take you to a view of it ( e.g /poem/view/89)