mstdmstd's avatar

Invalid sorting in rows of laravel-datatables-oracle

Hello, In my laravel 5.8 app using yajra/laravel-datatables-oracle 8 I make report sorted by 2 fields and I see that results are shown in different order. My control action is :

    public function get_check_out_history_dt_listing()
    {
        $request     = request();
        $requestData = $request->all();
        $filter_customer = !empty($requestData['filter_customer']) ? $requestData['filter_customer'] : '';
        $filter_check_out_datepicker_from = !empty($requestData['filter_check_out_datepicker_from']) ? $requestData['filter_check_out_datepicker_from'] : '';
        $filter_check_out_datepicker_till = !empty($requestData['filter_check_out_datepicker_till']) ? $requestData['filter_check_out_datepicker_till'] : '';

        $filter_check_out_datepicker_from = str_replace(',', '', $filter_check_out_datepicker_from);
        if ( !empty($filter_check_out_datepicker_from) ) {
            $filter_check_out_datepicker_from = Carbon::createFromFormat('d M Y', $filter_check_out_datepicker_from)->format('Y-m-d' );
        }

        $filter_check_out_datepicker_till = str_replace(',', '', $filter_check_out_datepicker_till);
        if ( !empty($filter_check_out_datepicker_till) ) {
            $filter_check_out_datepicker_till = Carbon::createFromFormat('d M Y', $filter_check_out_datepicker_till)->format('Y-m-d' );
        }

        $checkOutsCollection = CheckIn
            ::orderBy('check_ins.paid_through_date', 'asc') // The 2 fields I make sorting
            ->orderBy('storage_spaces.number', 'asc')
            ->getByStatus('O')  // O=>Check Out
            ->getByPaidThroughDate($filter_check_out_datepicker_from, '>=')
            ->getByPaidThroughDate($filter_check_out_datepicker_till, '<=')
            ->whereRaw( 'NOT ISNULL(check_ins.paid_through_date) ')
            ->leftJoin( 'clients', 'clients.id', '=', 'check_ins.client_id' )
            ->leftJoin( 'storage_spaces', 'storage_spaces.id', '=', 'check_ins.storage_space_id' )
            ->where( 'clients.full_name', 'like' , '%' . $filter_customer .'%' )
            ->select(
                "check_ins.id",
                "check_ins.check_in_date",
                "check_ins.paid_through_date",
                "check_ins.vat",
                "check_ins.job_ref_no as check_ins_job_ref_no",
                "clients.full_name as client_full_name",
                "clients.address as client_address",
                "storage_spaces.number as storage_spaces_number",
                "storage_spaces.status as storage_spaces_status",
                "check_ins.insurance_vat as storage_insurance_vat",
                "check_ins.vat as storage_actual_storage_rent"
            )
            ->get();

        return Datatables
            ::of($checkOutsCollection)     /*  Set format/labels for specific columns  */
            ->editColumn('storage_spaces_status', function ($storageSpace) {
                if (empty($storageSpace->storage_spaces_status)) {
                    return '';
                }
                return StorageSpace::getStorageSpaceStatusLabel($storageSpace->storage_spaces_status);
            })

            ->editColumn('storage_insurance_vat', function ($storageSpace) {
                if (empty($storageSpace->storage_insurance_vat)) {
                    return '';
                }
                return $this->formatCurrencySum($storageSpace->storage_insurance_vat);
            })

            ->editColumn('storage_actual_storage_rent', function ($storageSpace) {
                if (empty($storageSpace->storage_actual_storage_rent)) {
                    return '';
                }
                return $this->formatCurrencySum($storageSpace->storage_actual_storage_rent);
            })
            ->editColumn('check_in_date', function ($storageSpace) {
                if (empty($storageSpace->check_in_date)) {
                    return '';
                }
                return $this->getCFFormattedDate($storageSpace->check_in_date);
            })
            ->editColumn('paid_through_date', function ($storageSpace) {
                if (empty($storageSpace->paid_through_date)) {
                    return '';
                }
                return $this->getCFFormattedDate($storageSpace->paid_through_date);
            })
            ->make(true);
    } // get_check_out_history_dt_listing

and in related js file I run :

checkOutHistory.prototype.checkOutHistoryDataLoad = function () {
    Mustache.tags = ["<%", "%>"];

    var columnsData = []
    // columnsData[columnsData.length] = {data: 'id', name: 'id'}
    columnsData[columnsData.length] = {data: 'storage_spaces_number', name: 'storage_spaces_number'}
    columnsData[columnsData.length] = {data: 'storage_spaces_status', name: 'storage_spaces_status'}
    columnsData[columnsData.length] = {data: 'client_full_name', name: 'client_full_name'}
    columnsData[columnsData.length] = {data: 'client_address', name: 'client_address'}
    columnsData[columnsData.length] = {data: 'check_ins_job_ref_no', name: 'check_ins_job_ref_no'}
    columnsData[columnsData.length] = {data: 'check_in_date', name: 'check_in_date'}
    columnsData[columnsData.length] = {data: 'storage_actual_storage_rent', name: 'storage_actual_storage_rent'}
    columnsData[columnsData.length] = {data: 'storage_insurance_vat', name: 'storage_insurance_vat'}
    columnsData[columnsData.length] = {data: 'paid_through_date', name: 'paid_through_date'}


    oTable = $('#get-check-out-history-dt-listing-table').DataTable({
        processing: true,
        autoWidth: false,
        language: {
            "processing": "Loading Check Out History..."
        },
        serverSide: true,
        "lengthChange": true,
        "lengthMenu": this_backendLengthMenuArray,
        ajax: {                          // report/get-check-out-history-dt-listing
            url: this_backend_home_url + '/admin/report/get-check-out-history-dt-listing',
            data: function (d) {
                d.filter_customer = $("#filter_customer").val();
                d.filter_check_out_datepicker_from = $("#filter_check_out_datepicker_from").val();
                d.filter_check_out_datepicker_till = $("#filter_check_out_datepicker_till").val();
            },
        }, // ajax: {

        columns: columnsData,

        "drawCallback": function (settings, b) {
            var span_check_out_histories_records_count_content = $("#span_check_out_histories_records_count").html()

            if (typeof span_check_out_histories_records_count_content != "undefined") {
                $("#span_check_out_histories_records_count").html( ". Shows " + settings.json.data.length + " of " + settings.json.recordsFiltered + " box rooms" )
            } else {
                var $label = $("<label>").text(". Shows " + settings.json.data.length + " of " + settings.json.recordsFiltered + " box rooms").attr({
                    id: 'span_check_out_histories_records_count',
                    name: 'span_check_out_histories_records_count'
                });

                $(".dataTables_length > label ").append( $label );
            }

            $(".dataTables_info").css("display", "none")
            if (settings.json.recordsTotal <= settings.aiDisplay.length) { // we need to hide pagination block
                $(".dataTables_paginate").css("display", "none")
            } else {  // we need to show pagination block
                $(".dataTables_paginate").css("display", "block")
            }

        },

    }); // oTable = $('#get-check-out-history-dt-listing-table').DataTable({

}

When I check sql-trace I see that all rows are returned in valid order I set with orderBy in my control. That datatables changed ordering and I do not see where I do not set any ordering anymore in datatables... and how can I set it manually ?

Thanks!

0 likes
2 replies
mstdmstd's avatar

Thanks for your hint! To switch ordering is not a way for me, but I found link: https://datatables.net/reference/api/column().order() That is similar what I need, but in my case the problem is that I format datetime field in php control's action. So sorting by datetime field is not sorted properly. If datatables has possibility to sort by datetime field and show this field formatted? If yes, please link...

Please or to participate in this conversation.