order json columns as numbers

[geo903]

Summary of problem or feature request

Hello! There is a problem with sorting by the json column of data in which numbers are written; when sorting by this column, the numbers are sorted not as numbers but as strings 9,90,7,70.... I understand that string sorting is used, json is a string, but how can I specify for certain columns, for example, a numeric data type for sorting?

Code snippet of problem

$user = \Auth::user();
        $settings=\Config::get('settings');
        $fields=\DB::table('clients_fields')->where('project_id', '=', $settings['project_id'])->where('is_view', '=', 1)->orderBy('order_index', 'asc')->get();

        $request_fields="statuses.name, clients.id, companies.name as company_name,clients.create_date,clients.update_date, CONCAT(COALESCE(JSON_VALUE(clients.fields, '$.firstname'),''),' ',COALESCE(JSON_VALUE(clients.fields, '$.surname'),''),' ',COALESCE(JSON_VALUE(clients.fields, '$.patronymic'),'')) as name";

        foreach($fields as $field) {
            $request_fields=$request_fields.", JSON_VALUE(clients.fields, '$.".$field->name."') AS ".$field->name;
        }

        $clients = \DB::table('clients')
            ->select(\DB::raw($request_fields))
            ->leftjoin('companies', 'clients.company_id', 'companies.id')
            ->leftjoin('statuses', 'clients.status_id', 'statuses.id')
            ->where('clients.project_id', '=', $settings['project_id']);

        $dt = DataTables::of($clients);

        $dt->filterColumn('name', function ($query, $keyword){
            $sql = "LOWER(CONCAT(COALESCE(JSON_VALUE(clients.fields, '$.firstname'),''),' ',COALESCE(JSON_VALUE(clients.fields, '$.surname'),''),' ',COALESCE(JSON_VALUE(clients.fields, '$.patronymic'),''))) like ?";
            $query->whereRaw($sql, ["%{$keyword}%"]);
        });

        foreach($fields as $field) {
            $dt->filterColumn($field->name, function ($query, $keyword) use($field){
                $sql = "LOWER(JSON_VALUE(clients.fields, '$.".$field->name."')) like ?";
                $query->whereRaw($sql, ["%{$keyword}%"]);
            });
        }

        return $dt->toJson();

System details

• Operating System  Ubuntu
• PHP Version 8.0.2
• Laravel Version 10.0
• Laravel-Datatables Version 9.0

[yajra]

This is DB related and not on the package.

Anyway, I don't have much experience with json handling in DB. But a wild guess might be to use a virtual column.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.