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? github.com/matfish2/vue-tables-2#options scroll down to pagination options. — Bergur
– Bergur, Commented Jan 4, 2019 at 17:52
@mare96 I updated my answer. Try with controller code which I added in my answer and let me know — sanu
– sanu, Commented Jan 5, 2019 at 15:23

sanu · Accepted Answer · 2019-01-05 15:28:51Z

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

Jacob Rivera · Accepted Answer · 2019-01-04 22:09:32Z

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 4, 2019 at 22:09

Jacob Rivera

542 bronze badges

1 Comment

artoju Over a year ago

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: npmjs.com/package/vue-tables-2#implementations

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related