How to sort multiple columns using laravel, mongo and datatables?

Question

I have a project in laravel. Database is mongodb. I am using a package to connect using laravel and mongo "https://github.com/jenssegers/laravel-mongodb". In my project I have a bookings listing page. jQuery Datatables server side feature is using for pagination, search and sort. At-present pagination and searching is working but sorting is not working. I am getting some error, Please check the attachment. I thing it's a problem in sort section in query. Please check below codes and help me. Thanks.

bookings.blade.php

$('#booking_data').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": {
                    "url": '{{ route('bookings.datatables') }}',
                    "dataType": "json",
                    "type": "POST",
                    "data":{ _token: "{{csrf_token()}}"}
                },
                "columns": [
                    { "data": "hash" },
                    { "data": "invoice_number" },
                    { "data": "usrEmail" },
                    { "data": "checkin_from" },
                    { "data": "reserve_to" },
                    { "data": "beds" },
                    { "data": "dormitory" },
                    { "data": "sleeps" },
                    { "data": "status" },
                    { "data": "payment_status" },
                    { "data": "payment_type" },
                    { "data": "total_prepayment_amount" },
                    { "data": "txid" }
                ]
            });

BookingController.php

public function dataTables(Request $request)
    {
        $columns       = array('invoice_number', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'txid');

        $totalData     = Booking::where('is_delete', 0)->count();

        $totalFiltered = $totalData;
        $limit         = (int)$request->input('length');
        $start         = (int)$request->input('start');
        $order         = $columns[$request->input('order.0.column')];
        $dir           = $request->input('order.0.dir');

        if(empty($request->input('search.value')))
        {
            $bookings = Booking::select('invoice_number', 'temp_user_id', 'user', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'txid')
                ->where('is_delete', 0)
                ->skip($start)
                ->take($limit)
                ->orderBy($order, $dir)
                ->get();
        }
        else {
            $search   = $request->input('search.value');
            $bookings = Booking::select('invoice_number', 'temp_user_id', 'user', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'txid')
                ->where('is_delete', 0)
                ->where(function($query) use ($search) { 
                    $query->where('invoice_number', 'like', "%{$search}%")
                        ->orWhere('payment_type', 'like', "%{$search}%");
                })
                ->skip($start)
                ->take($limit)
                ->orderBy($order, $dir)
                ->get();

            $totalFiltered = Booking::select('invoice_number', 'temp_user_id', 'user', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'txid')
                ->where('is_delete', 0)
                ->where(function($query) use ($search) {
                    $query->where('invoice_number', 'like', "%{$search}%")
                        ->orWhere('payment_type', 'like', "%{$search}%");
                })
                ->count();
        }

        $data = array();
        if(!empty($bookings))
        {
            foreach ($bookings as $key=> $booking)
            {
                $nestedData['hash']                    = '<input type="checkbox" name="id[]" value="'.$booking->_id.'" />';
                $nestedData['invoice_number']          = '<a class="nounderline modalBooking" data-toggle="modal" data-target="#bookingModal_'.$booking->_id.'" data-modalID="'.$booking->_id.'">'.$booking->invoice_number.'</a>';
                $nestedData['usrEmail']                = $booking->user;
                $nestedData['checkin_from']            = ($booking->checkin_from)->format('d.m.y');
                $nestedData['reserve_to']              = ($booking->reserve_to)->format('d.m.y');
                $nestedData['beds']                    = $booking->beds;
                $nestedData['dormitory']               = $booking->dormitory;
                $nestedData['sleeps']                  = $booking->sleeps;
                $nestedData['status']                  = $booking->status;
                $nestedData['payment_status']          = $booking->payment_status;
                $nestedData['payment_type']            = $booking->payment_type;
                $nestedData['total_prepayment_amount'] = $booking->total_prepayment_amount;
                $nestedData['txid']                    = $booking->txid;
                $data[]                                = $nestedData;
            }
        }

        $json_data = array(
            'draw'            => (int)$request->input('draw'),
            'recordsTotal'    => (int)$totalData,
            'recordsFiltered' => (int)$totalFiltered,
            'data'            => $data
        );

        echo json_encode($json_data);
}

.

Could you please tell which one is line 46 in your controller ? — shafiq.rst
– shafiq.rst, Commented Jun 29, 2017 at 13:19
We miss some info: what url do you call, what is on line 46 exactly, what does your route look like, what middleware is used, what does the table look like? — online Thomas
– online Thomas, Commented Jul 1, 2017 at 11:27

Pascal Meunier · Accepted Answer · 2017-07-02 02:14:06Z

1

+50

The problem is here in your BookingController.

$columns       = array('invoice_number', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'txid');
...
$order         = $columns[$request->input('order.0.column')];

DataTable will send the column name in the request, not the key so you can't use it that way. Maybe you can replace with this:

$order = $request->input('order.0.column');

If you want to validate against your valid columns you can use the in_array() function.

answered Jul 2, 2017 at 2:14

Pascal Meunier

7156 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gyrocode.com Over a year ago

order[0][column] request parameter is a zero-based column index, see Server-side processing, so $columns[$request->input('order.0.column')] seems like a valid way to do it.

Collectives™ on Stack Overflow

How to sort multiple columns using laravel, mongo and datatables?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related