How to add pagination on vue-tables-2 using Laravel rest api?

Question

What should I do to enable pagination on Vue server-table from Laravel endpoint?

My component:

<template>
    <div>
        <v-server-table :columns="columns" url="/object/find" :options="options">
        </v-server-table>
    </div>
</template>
<script>
export default {
data () {
      return {
        columns: ['name', 'type', 'created_by', 'created_at'],
        options: {
          perPage: 5,
          perPageValues: [5, 10, 15, 25, 50, 100],
          pagination: {chunk: 5},
          dateColumns: ['created_at'],
          dateFormat: 'DD-MM-YYYY HH:mm',
          datepickerOptions: {
            showDropdowns: true,
            autoUpdateInput: true,
          }
          filterable: ['name', 'type','created_by', 'created_at'],
          sortable: ['name', 'type', 'created_by', 'created_at'],
          requestAdapter (data) {
            return {
              sort: data.orderBy ? data.orderBy : 'name',
              direction: data.ascending ? 'asc' : 'desc',
              limit: data.limit ? data.limit : 5,
              page: data.page,
              name: data.query.name,
              created_by: data.query.created_by,
              type: data.query.type,
              created_at: data.query.created_at
            }
          },
          responseAdapter ({data}) {
            return {
              data,
              count: data.length
            }
          },
        }
      }
    },
 }
</script>

Controller:

public function findObjects(Request $request)
    {
        $objects = Objects::withTrashed();
        $sort = $request->get('sort');
        $direction = $request->get('direction');
        $name = $request->get('name');
        $created_by = $request->get('created_by');
        $type = $request->get('type');
        $limit = (int)$request->get('limit');
        $page = (int)$request->get('page');
        $created_at = $request->get('created_at');
        if ($sort !== null && $direction !== null) {
            $objects->orderBy($sort, $direction);
        }
        if ($name !== null) {
            $objects->where('name', 'like', '%' . $name . '%');
        }
        if ($created_by !== null) {
            $objects->where('created_by', 'like', '%' . $created_by . '%');
        }
        if ($type !== null) {
            $objects->where('type', 'like', '%' . $type . '%');
        }
        if ($created_at !== null) {
            $date_range = json_decode($created_at);
            $objects->whereBetween('created_at', [Carbon::parse($date_range->start), Carbon::parse($date_range->end)]);
        }

         return $objects->get();
    }

All filters work fine. When I use LIMIT or TAKE or PAGINATE it will return 5 items and paginate links don't work in the component. What should I do in my controller and in my component to display for example 5 items on page?

Have you checked out the docs? https://github.com/matfish2/vue-tables-2#options scroll down to pagination options. — Bergur, Jan 04 '19 at 17:52
@mare96 I updated my answer. Try with controller code which I added in my answer and let me know — sanu, Jan 05 '19 at 15:23

sanu · Accepted Answer · 2019-01-05T15:28:51.383

Please go through the documentaction carefully here

You need to return a JSON object with two properties:

data : array - An array of row objects with identical keys.

count: number - Total count before limit.

For example your JSON should look like this:

[ 
    "data": [
       { 
          "name": "Name1", 
          "created_at": "01-01-2019 00:00:01, 
          "updated_at": "02-01-2019 10:12:13",
          "pushed_at" : "01-01-2019 00:00:05"
       },
       { 
          "name": "Name2", 
          "created_at": "01-01-2019 00:00:01, 
          "updated_at": "02-01-2019 10:12:13",
          "pushed_at" : "01-01-2019 00:00:05"
       }, 
       { 
          "name": "Name3", 
          "created_at": "01-01-2019 00:00:01, 
          "updated_at": "02-01-2019 10:12:13",
          "pushed_at" : "01-01-2019 00:00:05"
       }
    ],
    "count":100
]

In your controller you are not returning total row count for vue-table-2 pagination. Add count in your response will solve your issue

Change you controller code with following code:

public function findObjects(Request $request)
{
    $objects    = Objects::withTrashed();
    $sort       = $request->get('sort');
    $direction  = $request->get('direction');
    $name       = $request->get('name');
    $created_by = $request->get('created_by');
    $type       = $request->get('type');
    $limit      = (int)$request->get('limit');
    $page       = (int)$request->get('page');
    $created_at = $request->get('created_at');
    if ($sort !== null && $direction !== null) {
        $objects->orderBy($sort, $direction);
    }
    if ($name !== null) {
        $objects->where('name', 'like', '%' . $name . '%');
    }
    if ($created_by !== null) {
        $objects->where('created_by', 'like', '%' . $created_by . '%');
    }
    if ($type !== null) {
        $objects->where('type', 'like', '%' . $type . '%');
    }
    if ($created_at !== null) {
        $date_range = json_decode($created_at);
        $objects->whereBetween('created_at', [Carbon::parse($date_range->start), Carbon::parse($date_range->end)]);
    }

    $count = $objects->count();

    $objects->offset($limit * ($page - 1))->limit($limit);

    $data = $objects->get()->toArray();

    return response()->json([
        'data'  => $data,
        'count' => $count
    ]);
}

And Change Your vuejs code like this

<template>
<div>
    <v-server-table :columns="columns" url="/object/find" :options="options">
    </v-server-table>
</div>
</template>
<script>
export default {
data () {
      return {
        columns: ['name', 'type', 'created_by', 'created_at'],
        options: {
          perPage: 5,
          perPageValues: [5, 10, 15, 25, 50, 100],
          pagination: {chunk: 5},
          dateColumns: ['created_at'],
          dateFormat: 'DD-MM-YYYY HH:mm',
          datepickerOptions: {
            showDropdowns: true,
            autoUpdateInput: true,
          }
          filterable: ['name', 'type','created_by', 'created_at'],
          sortable: ['name', 'type', 'created_by', 'created_at'],
          requestAdapter (data) {
            return {
              sort: data.orderBy ? data.orderBy : 'name',
              direction: data.ascending ? 'asc' : 'desc',
              limit: data.limit ? data.limit : 5,
              page: data.page,
              name: data.query.name,
              created_by: data.query.created_by,
              type: data.query.type,
              created_at: data.query.created_at
            }
          }
        }
      }
    },
 }
</script>

I was searching for server side explanation since few days and this explanation is the best I could find ! Thanks — Jicao, Feb 21 '19 at 14:20

score 1 · Answer 2 · answered Jan 04 '19 at 22:09

1

In order to enable pagination you need to get it done in the SQL statement. If you are using SQL server use OFFSET/FETCH. If you using MYSQL use LIMIT/OFFSET. Use this link as reference:

What is the best way to paginate results in SQL Server

answered Jan 04 '19 at 22:09

Jacob Rivera

54
2

Don't know why you were down voted, you are indeed correct. The back-end side of things also needs to be accommodated to receive requests from the server table (search text, page size, page number etc.) and respond accordingly. That usually means making SQL statements with limits/offsets to execute in the database. I don't know how laravel works with server table but there seems to be an server-side implementation for that here: https://www.npmjs.com/package/vue-tables-2#implementations – artoju Jan 05 '19 at 08:03

How to add pagination on vue-tables-2 using Laravel rest api?

2 Answers2