Ordering Issue

[raflyasyahdani11]

Summary of problem or feature request

Hello hope you in a great condition, i have a problem sorting on my model because of auto total record query. I have already try to use skipTotalRecords but its still generating the count query. The error description that i get from laravel is : Column not found: 1054 in order clause. And the weird thing is when i try sort directly from datatable html table i just working fine.

Code snippet of problem

public function indexx(Request $request)
    {
        $status = (int) $request->get('status', 0);
        $user = Auth::user();

        $data = Pengajuan::with([
            'realisasi',
            'cabang',
            'cabang.area',
            'approval',
            'user',
            'jenis_r',
            'paid',
        ])
            ->withCount('paid')
            ->whereHas('realisasi', function ($query) use ($status) {
                $query->where('status', $status);
            });

        if ($user->level != 0) {
            $data->whereHas('realisasi', function ($query) use ($user) {
                $query->where('user_pengajuan', $user->username);
            });
        } else if ($user->id_area === 11) {
            $data->whereHas('cabang.area', function ($query) {
                $query->where('id_comp', 7);
            });
        } else {
            $data->whereHas('cabang.area', function ($query) {
                $query->where('id_comp', '!=', 7);
            });
        }

        $data->orderBy('paid.tanggal');

        $now = Carbon::now();

        return DataTables::eloquent($data)
            ->setFilteredRecords(0)
            ->addRowData('is_past_deadline', function ($v) use ($now) {
                $tanggalBayar = $v->paid->tanggal;
                return $now->diffInDays($tanggalBayar) > 14;
            })
            ->addRowData('is_realised', function ($v) {
                return $v->tgl_real != Constant::DEFAULT_DATE;
            })
            ->addRowData('nominal_approval', function ($v) {
                $maxLevel = $v->approval->max('level');
                $approval = $v->approval->where('level', $maxLevel)->first();

                return $approval->nominalapr;
            })
            ->toJson();
    }

System details

• Operating System MacOs
• PHP Version 7.4.33
• Laravel Version 8.75
• Laravel-Datatables Version 8.0

[yajra]

I suspect this is due to ordering on the relation. Try removing $data->orderBy('paid.tanggal');

[raflyasyahdani11]

I suspect this is due to ordering on the relation. Try removing $data->orderBy('paid.tanggal');

its working fine, but how to order it by the relation, any suggest?

[yajra]

It depends on the relation, some may not work as expected. The package will try to join the related table. For a more stable solution, you can use SQL joins.

To sort by relation, you can declare it in the js column:

columns: [
 {data: 'paid.tanggal', name: 'paid.tanggal'}
]

[raflyasyahdani11]

It depends on the relation, some may not work as expected. The package will try to join the related table. For a more stable solution, you can use SQL joins.

To sort by relation, you can declare it in the js column:

columns: [
 {data: 'paid.tanggal', name: 'paid.tanggal'}
]

How can I set the default sorting order when the page first loads?

[yajra]

order: [
 [1, 'asc']
]

Just change the index as needed.

[raflyasyahdani11]

thanks!! it works as expected