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?

Answer 1

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>