load more and more data when scroll down in Kartik Select2 widget

Question

I am working on stock management system , with a large number of products and the customer wan to display the products in a Select2 widget exactly like kartik widget, and he want to represent the data more and more when scrolling down ,is it available or can be done using this widget or I have to create my own ? this is my code

<?php
$url = Url::to ( [ 'products-list' ] );
echo Select2::widget ( [
    'name' => 'state_10' ,
    'options' => [
        'id' => 'select_product' ,
        'placeholder' => 'Select Product...' ,
        'multiple' => false ,
        'class' => ''
    ] ,
    'pluginOptions' => [
        'allowClear' => true ,
        'minimumInputLength' => 1 ,
        'ajax' => [
            'url' => $url ,
            'dataType' => 'json' ,
            'data' => new JsExpression ( 'function(params) { return {q:params.term}; }' )
        ] ,
        'escapeMarkup' => new JsExpression ( 'function (markup) { return markup; }' ) ,
        'templateResult' => new JsExpression ( 'function(product) { console.log(product);return product.text; }' ) ,
        'templateSelection' => new JsExpression ( 'function (subject) { return subject.text; }' ) ,
    ] ,
] );
?>

and I have this action in my controller and every thing is working well but I need to start with displaying a number of products in select2 and then when scrolling start getting more and more

controller action :

    public function actionProductsList($q = null, $id = null) {
        Yii::$app->response->format = Response::FORMAT_JSON;
        $out = ['results' => ['id' => '', 'text' => '', 'qtyLeft' => '', 'serialNumbers' => '']];
        if (!is_null($q)) {
            $query = new Query;
            $query->select(['product_id as id', new Expression("CONCAT(product_name,' -- ',product_qty_left) AS text, product_qty_left as qtyLeft, s.serial_number as serialNumbers")])
                    ->from('product')
                    ->join('LEFT JOIN', 'serial_number s', 's.r_product_id = product.product_id')
                    ->where(['like', 'product_name', $q]);
//                    ->limit(20);
            $command = $query->createCommand();
            $data = $command->queryAll();
            $out['results'] = array_values($data);
        } elseif ($id > 0) {
            $out['results'] = ['id' => $id, 'text' => AppProduct::find()->where(['product_id' => $id])->one()->product_name];
        }
        return $out;
    }

Answer 1

According to the docs to use pagination, you must tell Select2 to add any necessary pagination parameters to the request by overriding the ajax.data setting. The current page to be retrieved is stored in the params.page property.

So you need to change the Select2 to the following

$url = Url::to ( [ 'products-list' ] );
echo Select2::widget ( [
    'name' => 'state_10' ,
    'options' => [
        'id' => 'select_product' ,
        'placeholder' => 'Select Product...' ,
        'multiple' => false ,
        'class' => ''
    ] ,
    'pluginOptions' => [
        'allowClear' => true ,
        'minimumInputLength' => 1 ,
        'ajax' => [
            'url' => $url ,
            'dataType' => 'json' ,
            'data' => new JsExpression ( 'function(params) { return {q:params.term, page:params.page || 1}; }' )
        ] ,
        'escapeMarkup' => new JsExpression ( 'function (markup) { return markup; }' ) ,
        'templateResult' => new JsExpression ( 'function(product) { console.log(product);return product.text; }' ) ,
        'templateSelection' => new JsExpression ( 'function (subject) { return subject.text; }' ) ,
    ] ,
] );

Select2 will expect a pagination.more value in the response. The value of more should be true or false, which tells Select2 whether or not there are more pages of results available for retrieval:

{
  "results": [
    {
      "id": 1,
      "text": "Option 1"
    },
    {
      "id": 2,
      "text": "Option 2"
    }
  ],
  "pagination": {
    "more": true
  }
}

so modify your server-side code to include the $page param and include the limit and offset in your query. i am using 5 records as limit currrently you can change it.

public function actionProductsList($page, $q = null , $id = null ) {
        $limit=5;
        $offset=($page-1)*$limit;

        Yii::$app->response->format = Response::FORMAT_JSON;
        $out = [ 'results' => [ 'id' => '' , 'text' => '' , 'qtyLeft' => '' , 'serialNumbers' => '' ] ];
        if ( !is_null ( $q ) ) {
            $query = new \yii\db\Query;
            $query->select ( [ 'product_id as id' , new Expression ( "CONCAT(product_name,' -- ',product_qty_left) AS text, product_qty_left as qtyLeft, s.serial_number as serialNumbers" ) ] )
                    ->from ( 'product' )
                    ->join ( 'LEFT JOIN' , 'serial_number s' , 's.r_product_id = product.product_id' )
                    ->where ( [ 'like' , 'product_name' , $q ] )
                    ->offset ( $offset )
                    ->limit ( $limit );
            $command = $query->createCommand ();
            $data = $command->queryAll ();
            $out['results'] = array_values ( $data );
            $out['pagination'] = [ 'more' => !empty($data)?true:false ];
        } elseif ( $id > 0 ) {
            $out['results'] = [ 'id' => $id , 'text' => AppProduct::find ()->where ( [ 'product_id' => $id ] )->one ()->product_name ];
        }
        return $out;
    }

Update

There were a few fixes that I found in the above code and changed

• Fix the offset $offset=($page-1)*$limit; rather than using the $page like ->offset($page) use the $offset like ->offset($offset).
• Configure more results correctly to return false if no more results otherwise it keeps sending ajax calls to the server even if there are no more results so change it to $out['pagination'] = [ 'more' => !empty($data)?true:false ];