Unable to sort sequence column for yajra datatable

I am using datatable to display a list of products on my page. Each product have their own sequence value which is an integer. Some products do not have sequence value so they are assigned null by default. What I am trying to do is to display the products based on seq. For example, I have a product with seq 1 , another product with seq 2 and the others are null so it should display from smallest number to largest then followed by the null sequences.

What I am currently getting on my display is seq 2 then seq 1 and then null sequences. I want to display it based on priority (1 has higher priority than 2). This is my code for the datatable:

  var productTable = $('#datatable-product').DataTable({
        processing: true,
        serverSide: true,
        searching: false,
        bInfo: false,
        bLengthChange: false,
        autoWidth: false,
        ajax: {
            url: '/products/datatable',
            data: function (d) {
                d.product_name = $('input[name="product_name"]').val();
                d.category_id = $('select#filter_category_id').find(":selected").val();
            }
        },
        order: [[4, 'asc_nulls_last']],
    
        columns: [
            { data: 'cover_image', name: 'cover_image', orderable: false },
            { data: 'product_name', name: 'product_name', defaultContent: '', className: 'product__name' },
            { data: 'category_name', name: 'category_name', defaultContent: '', className: 'product__category-name' },
            { data: 'status', name: 'status', className: 'text-center' },
            { data: 'seq', name: 'seq', className: 'text-center'},
            {
                data: 'updated_at',
                name: 'updated_at',
                className: 'text-center',
                render: function (data, type, row, meta) {
                    if (row) {
                        return dateTimeDatatable(row.updated_at);
                    }
                    return '';
                }
            },
            { data: 'action', orderable: false, className: 'text-center' },
        ],
        language: {
            paginate: {
                previous: '<',
                next: '>'
            }
        }
    });

My backend code for reference:

  public function indexDatatable(GetProductRequest $request)
    {
        $parameters = $request->validated();
        return $this->productService->productDatatable($parameters);
    }

  public function productDatatable(array $parameters)
    {
        $productList = $this->productRepository->productList($parameters);
        
        return DataTables::of($productList)
                         ->orderColumns(['product_name', 'category_name', 'status', 'seq', 'updated_at'],
                             ':column ')
                         ->addColumn('cover_image', function ($data) {
                             $coverImage   = null;
                             $productImage = $data->managementFiles()->where(
                                 'status',
                                 ManagementFile::ACTIVATED
                             )->first();
                             if ($productImage) {
                                 $coverImage = Common::getImageAzureBlobLink($productImage->url);
                             }
                             return view('admin.products.partials.common.image', compact('data', 'coverImage'))
                                 ->render();
                         })
                         ->addColumn('status', function ($data) {
                             return view('admin.products.partials.common.badge', compact('data'))->render();
                         })
                         ->addColumn('action', function ($data) use ($parameters) {
                             if (isset($parameters['is_top']) &&
                                 $parameters['is_top'] === config('vip.product.is_top_product.yes')) {
                                 return view('admin.top_product.partials.common.actions', compact('data'))
                                     ->render();
                             }

                             return view('admin.products.partials.common.actions', compact('data'))
                                 ->render();
                         })
                         ->rawColumns(['status', 'action', 'cover_image'])
                         ->make(true);
    }

  public function productList(array $parameters)
    {
        $model = $this->model
            ->select('products.*', 'product_lang.text as product_name', 'category_lang.text as category_name')
            ->leftJoin('languageables as product_lang', function ($join) {
                $join->on('products.id', '=', 'product_lang.languageable_id')
                     ->where('product_lang.languageable_type', Product::class)
                     ->where('product_lang.type', Product::PRODUCT_NAME)
                     ->where('product_lang.language_id', Language::EN);
            })
            ->leftJoin('languageables as category_lang', function ($join) {
                $join->on('products.category_id', '=', 'category_lang.languageable_id')
                     ->where('category_lang.languageable_type', Category::class)
                     ->where('category_lang.language_id', Language::EN);
            })
            ->where(function ($query) use ($parameters) {
                if (!empty($parameters['category_id'])) {
                    $query->where('products.category_id', $parameters['category_id']);
                }
                if (!empty($parameters['product_name'])) {
                    $query->where('product_lang.text', 'like', '%' . $parameters['product_name'] . '%');
                }
                if (isset($parameters['is_top'])) {
                    $query->where('products.is_top', $parameters['is_top']);
                }
            });

        $model = $model->with([
            'managementFiles',
        ]);

        $this->resetModel();

        return $model;
    }

I am not sure why my datatable does not sort to what i want. How do I fix this issue?

lbecket

3 years ago

Level 39

You're using the asc_nulls_last option to sort the seq column in ascending order with nulls appearing last, but this is not the behavior that you want. Instead, you need to sort the seq column in ascending order first and then sort by priority (which is the reverse order of seq) in descending order.

To do this, modify the order option in the DataTable constructor to the following:

order: [[4, 'asc'], [null, 'asc']],
columnDefs: [
  {
    targets: 4,
    render: function (data, type, row, meta) {
        if (data === null) {
            return Number.POSITIVE_INFINITY;
        }
        return -data;
    },
    orderData: [4]
  }
]

Here, the order option is an array of two arrays. The first array is used to sort the seq column in ascending order. The second array specifies the default sorting, which is no sorting (hence null).

In addition, the columnDefs option is used to specify a custom rendering function for the seq column. In the rendering function, if the value of seq is null, we return Number.POSITIVE_INFINITY, which is a large number that is guaranteed to appear last in the sort order. Otherwise, we return the negative value of seq, which effectively sorts the values in reverse order (highest priority first). The orderData option specifies that the sorting should be done based on the data in the seq column.

CookieMonster

3 years ago

Level 6

@lbecket I tried using your solution but it seems:

[null, 'asc']

throws an error: Cannot read properties of undefined (reading 'aDataSort')

Hassankhan

3 years ago

Level 1

I have solved this problem like that

$(function () { var table = $('.data-table').DataTable({ processing: true, serverSide: true, ajax: "{{ route('users.index') }}", columns: [ {data: 'id', name: 'id'}, {data: 'name', name: 'name'}, {data: 'email', name: 'email'}, { data: 'created_at', type: 'num', render: { _: 'display', sort: 'timestamp' } } ] }); });

Please or to participate in this conversation.