I'm trying to paginate results (every 25 rows) using Select2 4.0, but I don't know how to achieve it. Does somebody know how to do it?
If the user reach the end of the 25 rows and if there is more rows I would like to load it and show it.
Here is my HTML template
<div class="form-group">
{!! Form::select('breed_id', $breeds, null, ['class' => 'form-control', 'id' =>'breed_id'] ) !!}
</div>
And here is the JavaScript for Select2.
$("#breed_id").select2({
placeholder: 'Breed...',
width: '350px',
allowClear: true,
ajax: {
url: '',
dataType: 'json',
data: function(params) {
return {
term: params.term
}
},
processResults: function (data, page) {
return {
results: data
};
},
cache: true
}
});
And this is the code I have for my controller
if ($request->ajax())
{
$breeds = Breed::where('name', 'LIKE', '%' . Input::get("term"). '%')->orderBy('name')->take(25)->get(['id',DB::raw('name as text')]);
return response()->json($breeds);
}
Also when I tried to put params.page
it says "undefined".
Select2 supports pagination when using remote data through the pagination
key that comes out of processResults
.
For infinite scrolling, the pagination
object is expected to have a more
property which is a boolean (true
or false
). This will tell Select2 whether it should load more results when reaching the bottom, or if it has reached the end of the results.
{
results: [array, of, results],
pagination: {
more: true
}
}
In your case, you have the ability to shape your results. So you can actually change your JSON response to match the expected format, which means you won't even need to use processResults
.
Select2 can pass in the page number as page
if you modify the ajax.data
function to return it.
data: function(params) {
return {
term: params.term || "",
page: params.page || 1
}
},
And then you will be able to get the page using Input::get('page')
. And you can calculate the total number of results to skip using (page - 1) * resultCount
, where resultCount
is 25
in your case. This will allow you to modify your query to combine LIMIT
and OFFSET
to get just the results you need.
$page = Input::get('page');
$resultCount = 25;
$offset = ($page - 1) * $resultCount;
And you can use the following query to generate a LIMIT
/ OFFSET
query (based on this Stack Overflow question.
$breeds = Breed::where('name', 'LIKE', '%' . Input::get("term"). '%')->orderBy('name')->skip($offset)->take($resultCount)->get(['id',DB::raw('name as text')]);
So now $breeds
will contain only the requested results. The only thing left to do is to shape the response to match how Select2 is expecting it. You can determine if there are more pages by checking the total number of results and seeing if you've run over the limit.
$count = Breed::count();
$endCount = $offset + $resultCount;
$morePages = $endCount > $count;
So now $morePages
should be a boolean, which is exactly what Select2 is looking for in pagination.more
. Now you just need to shape the response to match the format I mentioned earlier.
$results = array(
"results" => $breeds,
"pagination" => array(
"more" => $morePages
)
);
And then rendering that
return response()->json($results);
Putting everything together, you get this for JavaScript
$("#breed_id").select2({
placeholder: 'Breed...',
width: '350px',
allowClear: true,
ajax: {
url: '',
dataType: 'json',
data: function(params) {
return {
term: params.term || '',
page: params.page || 1
}
},
cache: true
}
});
And the following for your controller
if ($request->ajax())
{
$page = Input::get('page');
$resultCount = 25;
$offset = ($page - 1) * $resultCount;
$breeds = Breed::where('name', 'LIKE', '%' . Input::get("term"). '%')->orderBy('name')->skip($offset)->take($resultCount)->get(['id',DB::raw('name as text')]);
$count = Breed::count();
$endCount = $offset + $resultCount;
$morePages = $endCount > $count;
$results = array(
"results" => $breeds,
"pagination" => array(
"more" => $morePages
)
);
return response()->json($results);
}