laravel Vue js：Vuetify 服务器端数据表搜索过滤器不起作用

Vue js: Vuetify server side Datatable search filter not working

提问by BGTabulation BGTabulate

I'm using vuetify for my datatable. Pagination and sort are working except the search filter. The response data from search filter is correct but the problem is its not rendering the response to my template. In vuetifydocs theres only have for pagination and sort. I'm trying to implement the search function via server-side.

我正在为我的数据表使用 vuetify。除了搜索过滤器外，分页和排序都在工作。来自搜索过滤器的响应数据是正确的，但问题是它没有将响应呈现给我的模板。在vuetify文档中，只有分页和排序。我正在尝试通过服务器端实现搜索功能。

My User.vue

我的用户.vue

export default{
    data () {
    return {
      max25chars: (v) => v.length <= 25 || 'Input too long!',
      tmp: '',
      search: '',
      totalItems: 0,
      pagination: {
        rowsPerPage: 1,
        search: ''
      },
      headers: [
        {
          text: 'Name',
          sortable: true,
          value: 'name',
          align: 'left'
        },
        {
          text: 'Email Add',
          sortable: true,
          value:'email',
          align: 'left'
        },
        {
          text: 'Roles',
          sortable: true,
          value:'roles_permissions',
          align: 'left'
        },
        {
          text: 'Date joined',
          sortable: true,
          value:'created_at',
          align: 'left'
        }
      ],
      items: [],
      loading: false,
      timer: null
    }
  },
  watch:{
    pagination:{
            handler(){
                this.getDataFromApi()
          .then(data => {
            const self = this;
            self.items = data.items;
            self.totalItems = data.total;
          })
            },
      deep: true
    }
  },
  mounted(){
    this.getDataFromApi()
        .then(data => {
            this.items = data.items;
        this.totalItems = data.total;
        });
  },
  methods:{
    getDataFromApi(search_val){
        this.loading = true;
      return new Promise((resolve, reject) => {
        const { sortBy, descending, page, rowsPerPage } = this.pagination
                const search = this.search;
        //console.log(search);
        clearTimeout(this.timer);
        this.timer = setTimeout(function(){

          axios({
            url: '/prod/api/user_table',
            method:'post',
            data:{
              sortBy : sortBy,
              descending: descending,
              page : page,
              rowsPerPage : rowsPerPage,
              search_val : search
            }
          })
          .then(response=>{
            if(response.status == 200){

              let items = response.data.data;
              const total = response.data.totalRecords;
              this.loading = false;
              resolve({
                items,
                total
              });
            }
          })
          .catch(error=>{
            if(error.response){
              console.log(error.response);
            }
          })
        },1000);
      })
    },
    fetchDataFromApi(value){
        //console.log(value);
    }
  },
  created(){

  }
}

Here is my back end side using laravel

这是我使用 Laravel 的后端

public function dataTable(Request $request){
    //return Datatable::eloquent(User::query())->make(true);
    $sortBy = $request->sortBy;
    $descending = $request->descending;
    $page = $request->page;
    $rowsPerPage = $request->rowsPerPage;
    $search_val = $request->search_val;

    //echo $rowsPerPage;
    if($descending){
        $orderedBy = 'desc';
    }else{
        $orderedBy = 'asc';
    }
    $start = ($page - 1) * $rowsPerPage;


    /*$totalRec = User::all();
    if(empty(trim($search_val))){
        $user = User::orderBy($sortBy,$orderedBy)->skip($start)->take($rowsPerPage)->get();
    }else{
        $user = User::where([

        ]);
    }*/

    $query = User::query();
    $column = ['name', 'email'];
    foreach ($column as $col) {
       $query->orWhere($col, 'LIKE','%'.$search_val.'%');
    }
    $query->orderBy($sortBy,$orderedBy)->skip($start)->take($rowsPerPage);
    $arr_items = [];
    foreach ($query->get()->toArray() as $shit => $v) {
        $arr_items['data'][] = array(
            'value' => $v['id'],
            'name' => $v['name'],
            'email' => $v['email'],
            'roles_permissions' => '',
            'created_at' => $v['created_at']
        );
    }
    $arr_items['totalRecords'] = User::count();
    return response()->json($arr_items);
}

回答by Eliyas Hossain

server side search & sort of datatable in vuetify.js

vuetify.js 中的服务器端搜索和数据表排序

If we need server side searchand sortin vuetify.js datatable, we have to make some changes in vuejs part.

如果我们需要在vuetify.js 数据表中进行服务端搜索和排序，我们必须在 vuejs 部分进行一些更改。

import {environment} from '../../environment';
export default {
    name: "Category",
    data() {
        return {
            categories: [],
            search: '',
            total: 0,
            loading: false,
            pagination: {},
            headers: [
                {text: 'ID', value: 'id'},
                {text: 'Name', value: 'name'},
                {text: 'Actions', value: 'name', sortable: false, align: 'center'}
            ],
            rowsPerPageItems: [5, 10, 20, 50, 100],
        }
    },
    watch: {
        pagination {
            this.getCategoriesByPagination();
        },
        search() {
            this.getCategoriesByPagination();
        }
    },
    methods: {
        getCategoriesByPagination() {
            this.loading = true;
            // get by search keyword
            if (this.search) {
                axios.get(`${environment.apiUrl}/category-filter?query=${this.search}&page=${this.pagination.page}&per_page=${this.pagination.rowsPerPage}`)
                    .then(res => {
                        this.categories = res.data.data;
                        this.total = res.data.meta.total;
                    })
                    .catch(err => console.log(err.response.data))
                    .finally(() => this.loading = false);
            }
            // get by sort option
            if (this.pagination.sortBy && !this.search) {
                const direction = this.pagination.descending ? 'desc' : 'asc';
                axios.get(`${environment.apiUrl}/category-order?direction=${direction}&sortBy=${this.pagination.sortBy}&page=${this.pagination.page}&per_page=${this.pagination.rowsPerPage}`)
                    .then(res => {
                        this.loading = false;
                        this.categories = res.data.data;
                        this.total = res.data.meta.total;
                    });
            } if(!this.search && !this.pagination.sortBy) {
                axios.get(`${environment.apiUrl}/category?page=${this.pagination.page}&per_page=${this.pagination.rowsPerPage}`)
                    .then(res => {
                        this.categories = res.data.data;
                        this.total = res.data.meta.total;
                    })
                    .catch(err => console.log(err.response.data))
                    .finally(() => this.loading = false);
            }
        }
    }
}

in htmlpart

在html部分

<v-text-field v-model="search"
              append-icon="search"
              label="Search"
              single-line
              hide-details
            ></v-text-field>

<v-data-table :headers="headers"
              :items="categories"
              :pagination.sync="pagination"
              :total-items="total"
              :rows-per-page-items="rowsPerPageItems"
              :loading="loading"
            ></v-data-table>

in Laravelpart, i used laravel scoutpackage.

在Laravel部分，我使用了laravel scout包。

Controller

控制器

/**
 * Get category
 * @return \Illuminate\Http\Resources\Json\AnonymousResourceCollection
 */
public function getAll()
{
    $per_page = empty(request('per_page')) ? 10 : (int)request('per_page');
    $categories = Category::latest()->paginate($per_page);
    return CategoryResource::collection($categories);
}

/**
 * Get category by search results
 * @return \Illuminate\Http\Resources\Json\AnonymousResourceCollection
 */
public function getBySearch()
{
    $per_page = empty(request('per_page')) ? 10 : (int)request('per_page');
    $categories = Category::search(request()->query('query'))->paginate($per_page);
    return CategoryResource::collection($categories);
}

/**
 * Get category by sorting
 * @return \Illuminate\Http\Resources\Json\AnonymousResourceCollection
 */
public function getByOrder()
{
    $per_page = empty(request('per_page')) ? 10 : (int)request('per_page');
    $direction = request()->query('direction');
    $sortBy = request()->query('sortBy');
    $categories = Category::orderBy($sortBy, $direction)->paginate($per_page);
    return CategoryResource::collection($categories);
}

Route

路线

    Route::get('category', 'Api\CategoryController@getAll');
    Route::get('category-filter', 'Api\CategoryController@getBySearch');
    Route::get('category-order', 'Api\CategoryController@getByOrder');

Model

模型

<?php

    namespace App;

    use Illuminate\Database\Eloquent\Model;
    use Laravel\Scout\Searchable;

    class Category extends Model
    {
       use Searchable;

       /**
         * Get the indexable data array for the model.
         *
         * @return array
         */
         public function toSearchableArray()
         {
            return [
               'name' => $this->name
            ];
         }
    }

回答by user219839

    <template>
      <div class="data-table">
        <v-data-table  :headers="headers" :items="desserts" :items-per-page="5"  :options.sync="options" :server-items-length="totalDesserts" :loading="loading" class="elevation-1" ></v-data-table>
      </div>
    </template>

    <script>
    import { mapGetters } from 'vuex'
    import axios from 'axios'
    import { api } from '~/config'
    import Form from '~/mixins/form'

    export default {
      data: () => ({

        desserts_s: [],
        totalDesserts: 0,
        loading: true,
        options: {},

        headers: [
          { text: 'id', value: 'id' },
          { text: 'lastname', value: 'lastname' },
          { text: 'email', value: 'email' },
        ],
        desserts: [],
      }),


        watch: {
          options: {
            handler () {
              this.getDataFromApi()
                .then(data => {
                  this.desserts = data.items
                  this.totalDesserts = data.total
                })
            },
            deep: true,
          },
        },
        mounted () {
          this.getDataFromApi()
            .then(data => {
              this.desserts = data.items
              this.totalDesserts = data.total
            })
        },
            methods: {
          getDataFromApi () {
            this.loading = true
            return new Promise((resolve, reject) => {
              const { sortBy, sortDesc, page, itemsPerPage } = this.options

             axios.get(api.path('test')+"?"+Object.keys(this.options).map(key => key + '=' + this.options[key]).join('&'))
                .then((response) => {
                  let items = response.data.users.data 
                  const total = response.data.users.total

                    console.log(response.data.users.data)

                  if (sortBy.length === 1 && sortDesc.length === 1) {
                    items = items.sort((a, b) => {
                      const sortA = a[sortBy[0]]
                      const sortB = b[sortBy[0]]

                      if (sortDesc[0]) {
                        if (sortA < sortB) return 1
                        if (sortA > sortB) return -1
                        return 0
                      } else {
                        if (sortA < sortB) return -1
                        if (sortA > sortB) return 1
                        return 0
                      }
                    })
                  }
                    this.loading = false
                    resolve({
                      items,
                      total,
                    })

                })
                .catch((error) => console.log(error.message))
            })
          },
          getDesserts () {


          },
        },
      }

    </script>

回答by Nicolas Bonnici

To enable server-side search to work don't pass the search prop to v-data-table. Otherwise the datatable pagination and search are client side even if you pass the "totalItems" prop.

要启用服务器端搜索，请不要将搜索道具传递给 v-data-table。否则，即使您传递了“totalItems”道具，数据表分页和搜索也是客户端。

回答by JoHansen

You can pass the search prop but the initial value needs to be null. I tried it first with an empty string and it didn't work, at least in my case.

您可以传递搜索道具，但初始值需要为空。我首先用空字符串尝试了它，但它不起作用，至少在我的情况下。

回答by Froxz

To late for answer, but I was looking for something similar to work with yajra/laravel-datatablesthis days and didn't found any examples / libs, so created something that worked:

回答晚了，但我这几天一直在寻找类似于yajra/laravel-datatables 的东西，但没有找到任何示例/库，所以创建了一些有用的东西：

1. Install composer require yajra/laravel-datatables-oracle:"~9.0"(And follow the instructions on how to add Provider, Facade, config
2. We will need to change our controller to Support DataTables:

1. 安装composer require yajra/laravel-datatables-oracle:"~9.0"（并按照有关如何添加Provider, Facade, config
2. 我们需要将控制器更改为支持数据表：

use DataTables;
------
public function dataTable(Request $request){
    //one line of code for simple search /sort / pagination
    return DataTables::of(User::query())->make(true);
}

1. Next we will adjust our Vuetifycomponent

3. 接下来我们将调整我们的Vuetify组件

Template

模板

<template>
    <v-data-table
        :headers="headers"
        :items="users"
        :pagination.sync="pagination"
        :total-items="totalUsers" 
        :rows-per-page-items="rowsPerPageItems"
        :loading="loading"
    >
        <template v-slot:items="props">
            <tr>
                <td>
                    <div class="d-flex">
                        <v-btn  :to="{ name: 'users.edit', params: { id: props.item.id }}">Edit</v-btn>
                    </div>
                </td>
                <td>{{ props.item.id }}</td>
                <td>{{ props.item.name }}</td>
                <td>{{ props.item.email }}</td>
            </tr>
        </template>
        <template v-slot:no-results>
            <v-alert :value="true" color="error" icon="warning">
                Your search for "{{ searchQuery }}" found no results.
            </v-alert>
        </template>
    </v-data-table>
</template>

JS

JS

<script>
    import axios from 'axios';
    export default {

        data () {
            return {
                draw: 1,
                users: [],
                searchQuery: "",
                loading: true,
                pagination: {
                    descending: true,
                    page: 1,
                    rowsPerPage: 10,
                    sortBy: "id",
                    totalItems: 0
                },
                totalUsers: 0,
                rowsPerPageItems: [10, 15, 20, 30, 40, 50],
                columns:{},
                headers: [
                    { text: 'Actions', value: 'actions', sortable: false, searchable: false, width: '210px'},
                    { text: 'ID', value: 'id', name: 'id', sortable: true, searchable: true, width: '40px'},
                    { text: 'Name', value: 'name', name: 'name', sortable: true, searchable: true, width: '250px'},
                    { text: 'Email', value: 'email', sortable: true, searchable: true, width: '80px'},
                ],
                cancelSource: null
            }
        },

        watch: {

            //watcher to watch for order/pagination and search criteria.
            //
            params: {
                handler() {

                    //on params change refetch Data
                    //We don't do it in mounted method, becuase on first load params will change.
                    this.getDataFromApi().then(data => {

                        this.users = data.items;
                        this.totalUsers = data.total;

                    });
                },

                deep: true
            }
        },

        mounted() {

            //Based on our Headers we create query data for DataTables
            //I've added a new param "searchable" to let DataBales know that this column is not searchable
            //You can also set name as "table.column eg users.name" if you select from more then table to avoid "Ambitious column name error from SQL"
            for (var i = 0; i < this.headers.length; i++) {

                this.columns[i] = {
                    data: this.headers[i].value,
                    name: (typeof(this.headers[i].name) != 'undefined' ? this.headers[i].name : this.headers[i].value),
                    searchable: this.headers[i].searchable,
                    orderable: this.headers[i].sortable,
                    search: {
                        value: '',
                        regex: false
                    }
                };
            }
        },

        //computed params to return pagination and search criteria
        computed: {
            params(nv) {

                return {
                    ...this.pagination,
                    query: this.searchQuery
                };
            }
        },

        methods: {

            cancelRequest() {

                //Axios cancelSource to stop current search if new value is entered
                if (this.cancelSource) {
                    this.cancelSource.cancel('Start new search, stop active search');
                }
            },

            getDataFromApi() {

                //show loading of Vuetify Table
                this.loading = true;

                return new Promise((resolve, reject) => {

                    this.cancelRequest();

                    this.cancelSource = axios.CancelToken.source();

                    //copy current params to modify
                    let params = this.params;

                    params.length = params.rowsPerPage; //set how many records to fecth per page
                    params.start = params.page == 1 ? 0 : (params.rowsPerPage * (params.page - 1)); //set offset
                    params.search = {
                        value: params.query,
                        regex: false
                    }; //our search query

                    params.draw = this.draw;

                    //sorting and default to column 1 (ID)
                    if(params.sortBy){

                        params.order = {
                            0: {
                                column: _.findIndex(this.headers, {
                                    'value': params.sortBy
                                }),
                                dir: (params.descending ? 'desc' : 'asc')
                            }
                        };
                    }else{
                        params.order = {
                            0: {
                                column: 1,
                                dir: 'desc'
                            }
                        };
                    }

                    params.columns = this.columns; //set our previously created columns

                    //fecth data
                    //I used here jQuery $.param() helper, becuase axios submits data as JSON Payload, and we need for data or Query params
                    //This can be changed
                    axios.get('/users?'+$.param(params), {
                        cancelToken: this.cancelSource.token
                    }).then((res) => {

                        this.draw++;

                        this.cancelSource = null;

                        let items = res.data.data;
                        let total = res.data.recordsFiltered;

                        resolve({
                            items,
                            total
                        });

                    }).catch((err) => {
                        if (axios.isCancel(err)) {
                            console.log('Request canceled', err.message);
                        } else {
                            reject(err);
                        }
                    }).always(() => {
                        this.loading = false;
                    });
                });
            }
        }
    }
</script>

Conclusion

结论

A simple solution to make vuetify work with Laravel DataTables, for sure is not ideal, but works well. Hope this helped.

使 vuetify 与 Laravel DataTables 一起工作的简单解决方案肯定不是理想的，但效果很好。希望这有帮助。