Yii 1.1: Select2 Infinite Scroll With Remote Data in Yii

5 followers

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)

Total 5 comments

#17342 report it
jorgito_ml at 2014/05/27 11:22am
Help

I have a select2 with multiple selection enabled (multiple=>true). It has preloaded items selected (Using 'initSelection'=>'...'). All works fine until pagination is fired...

After pagination, there are choosed options that appear in the combo. Any clue to solve this?

Thx.

Solved updating assets folder with the last select2 release.

#16909 report it
amanation at 2014/04/12 10:44am
@farid

it's welcome

#16908 report it
farid.muh19 at 2014/04/12 07:10am
SQL_CALC_FOUND_ROWS

@Israel Ama

hmmm u'r right.

I created an application which is only used in the internal network only, so I am less concerned about that. I will try to apply and see which one is faster result because sometimes it is very dependent on the design of the database used.

I've found the following article but it is old: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

thank you very much for your suggestion ... :)

#16905 report it
amanation at 2014/04/11 10:13am
nice one too

@farid: I picked some stuff from yours. But have you tried SQL_CALC_FOUND_ROWS and FOUND_ROWS() if your dbms is MySQL? That could save response time

#16900 report it
farid.muh19 at 2014/04/10 11:38pm
good...

Very useful information. Thanks for sharing!

I also have an example of using select2, but for yiiwheel extension.

view :

$this->widget('yiiwheels.widgets.select2.WhSelect2', array(
                'asDropDownList' => false,
                'language' => 'id',
                'model' => $model,
                'attribute'=>'id_distributor',
                'pluginOptions' => array(             
                    'minimumInputLength' => 2,
                    'width' => '40%',
                    'placeholder' => 'Pilih Distributor',
                    'allowClear' => true,
                    'ajax' => array( 
                        'url' => CController::createUrl('distributor/listdata'),
                        'dataType'=> 'json',
                        'quietMillis' => 500,
                        'data' => 'js:function (term,page) {
                            return {
                                q: term,
                                page_limit:10,
                                page: page,
                            };
                        }',
                        'results' => 'js:function (data,page) { 
                            var more = (page * 10) < data.total;
                            return {results: data.results, more: more};
                        }'                            
                    ),
 
                    'initSelection' => 'js:function(element, callback) {
                        // the input tag has a value attribute preloaded that points to a preselected ditributor id
                        // this function resolves that id attribute to an object that select2 can render
                        // for updateAction or after a failed form validation
                        var id=$(element).val();
                        if (id!=="") {
                            $.ajax("'.CController::createUrl('distributor/initdata').'", {
                                data: {
                                    qid: id
                                },
                                dataType: "json"
                            }).done(function(data) {                                     
                                callback(data); 
                            });
                        }
                    }',                    
                )));

controller :

public function actionListdata()
    {
        if (!YII_DEBUG && !Yii::app()->request->isAjaxRequest) {
            throw new CHttpException('403', 'Forbidden access.');
        }
        if (empty($_GET['q'])) {
            throw new CHttpException('404', 'Missing "term" GET parameter.');
        }
 
        $querytxt = Yii::app()->db->createCommand()
            ->select('count(id) as total')
            ->from('tbl_distributor')
            ->where(array('like','nama', array('%'.$_GET['q'].'%')))
            ->orwhere(array('like','kontak', array('%'.$_GET['q'].'%')))
            ->andwhere('deleted = 0')
            ->queryRow();
        $data=array();
        $data['total']=$querytxt['total'];
        $data['results']=array();
 
        $offset = ((int)$_GET['page']-1)*(int)$_GET['page_limit'];
        $limit = (int)$_GET['page_limit'];
        $querydata = Yii::app()->db->createCommand()
            ->select('id, nama, kontak, daftar_hitam')
            ->from('tbl_distributor')
            ->where(array('like','nama', array('%'.$_GET['q'].'%')))
            ->orwhere(array('like','kontak', array('%'.$_GET['q'].'%')))
            ->andwhere('deleted = 0')
            ->order('nama, kontak')
            ->limit($limit)
            ->offset($offset)
            ->queryAll();
        foreach($querydata as $item){
            $data['results'][]=array('id'=>$item['id'],'text'=>$item['nama'].($item['kontak']?' ('.$item['kontak'].')':'').($item['daftar_hitam']==1?' - DAFTAR HITAM':''));
        }
 
        echo CJSON::encode($data);
        Yii::app()->end();
    }
 
    public function actionInitdata()
    {
        if (!YII_DEBUG && !Yii::app()->request->isAjaxRequest) {
            throw new CHttpException('403', 'Forbidden access.');
        }
        if (empty($_GET['qid'])) {
            throw new CHttpException('404', 'Missing "term" GET parameter.');
        }
        $item = Yii::app()->db->createCommand()
            ->select('id, nama, kontak, daftar_hitam')
            ->from('tbl_distributor')
            ->where('id=:id',array(':id'=>$_GET['qid']))
            ->queryRow();
        $data=array('id'=>$item['id'],'text'=>$item['nama'].($item['kontak']?' ('.$item['kontak'].')':'').($item['daftar_hitam']==1?' - DAFTAR HITAM':''));
 
        echo CJSON::encode($data);
        Yii::app()->end();
    }

Leave a comment

Please to leave your comment.

Write new article