laravel-datatables: Column sorting - filterColumn is not working

Summary of problem or feature request

Column sorting is not working as expected when you have a formatted date. test

Code snippet of problem

->filterColumn('data_hora', function ($query, $keyword) {
   $query->whereRaw("to_char(data_hora,'DD/MM/YYYY HH:MI:SS') like ?", ["%$keyword%"]);
})

Complete Code:

public function ajaxListarRegistrosCadastrados(Request $request)
    {
        if($request->ajax()) {
            $registros = Noticia::select(['id', 'titulo', 'data_hora', 'data_hora_agendamento', 'updated_at', 'fixa', 'status', 'visualizacoes']);

            return Datatables::of($registros)
                ->addColumn('categoria', function($registro){
                    $categoria = (isset(Noticia::withTrashed()->find($registro->id)->categoria))? Noticia::withTrashed()->find($registro->id)->categoria->nome : null;
                    if($categoria)
                        return $categoria;
                    else
                        return '-';
                }, 2)
                ->addColumn('action', function ($registro) {
                    $edit   = null;
                    $delete = null;
                    if(Auth::user()->hasPermission($this->rota.'.edit')){
                        $edit   = '<a href="'. route($this->rota.'.edit', Hashids::encode($registro->id)) .'" class="btn btn-sm btn-primary" data-toggle="tooltip" data-placement="top" title="Editar"><i class="fa fa-pencil-square-o" aria-hidden="true"></i></a>';
                    }
                    if(Auth::user()->hasPermission($this->rota.'.destroy')){
                        $delete =  Form::open(['route' => [$this->rota.'.destroy', Hashids::encode($registro->id)], 'method' => 'DELETE', 'class' => 'form-inline']).
                            '<button type="submit" class="btn btn-sm btn-danger btn-lixeira" data-id="'.$registro->id.'" data-toggle="tooltip" data-placement="top" title="Deletar"><i class="fa fa-trash-o" aria-hidden="true"></i></button>'.
                            Form::close();
                    }

                    if(!isset($edit) && !isset($delete)){
                        $acao = '<span data-toggle="tooltip" title="Você não possui permissão para executar ações neste módulo!"><i class="fa fa-info-circle" aria-hidden="true"></i></span>';
                    }else{
                        $acao = $edit.$delete;
                    }

                    return '<div class="acao text-center">'.$acao.'</div>';
                })
                ->editColumn('data_hora_agendamento', function($registro){
                    if($registro->data_hora_agendamento)
                        return $registro->data_hora_agendamento;
                    else
                        return '-';
                })
                ->filterColumn('data_hora', function ($query, $keyword) {
                    $query->whereRaw("to_char(data_hora,'DD/MM/YYYY HH:MI:SS') like ?", ["%$keyword%"]);
                })
                ->escapeColumns(['action'])
                ->removeColumn('deleted_at')
                // regra que verifica se o status existe e se é inativo, adiciona class alert-danger
                ->setRowClass(function($registro){
                    return (isset($registro->status) && $registro->status == 'Inativo')? 'alert-danger' : null;
                })
                ->make();
        }else{
            return redirect()->route('login');
        }
    }

I also use the following mutator:

public static function FormataDataHoraPadrao($data){
 return Carbon::parse($data)->format('d/m/Y H:i:s');
}

System details

Operating System: homestead
PHP Version: 7.1
Laravel Version: 5.4.36
Laravel-Datatables Version: 7.10.1

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 2
Comments: 20 (3 by maintainers)

Most upvoted comments

@marcelogarbin good point. addColumn disables/adds the column on blacklist while editColumn does not. It is an expected behavior but might not be documented well. Sorry for that.

yajra on Dec 5, 2017

@yajra I believe I understood, in the “addColumn” case, it would only serve columns that do not require sorting, right? If yes, you can close this issue =)

Thank’s

garbinmarcelo on Dec 5, 2017

In place of “addColumn” I wrote “editColumn”, changing the function to:

->editColumn('id_categoria', function($registro){
                    $categoria = (isset(NoticiaCategoria::withTrashed()->find($registro->id_categoria)->nome))? NoticiaCategoria::withTrashed()->find($registro->id_categoria)->nome : null;
                    if($categoria)
                        return $categoria;
                    else
                        return '-';
                })

That way the ordering worked correctly. But if I use the same function for “addColumn” it is as in the 1st post I did, in the case not functional.

Would this be a bug?

garbinmarcelo on Dec 4, 2017

Ordination is already lost when I make this form:

return Datatables::of($registros)
                ->addColumn('categoria', function($registro){
                    $categoria = (isset(Noticia::withTrashed()->find($registro->id)->categoria))? Noticia::withTrashed()->find($registro->id)->categoria->nome : null;
                    if($categoria)
                        return $categoria;
                    else
                        return '-';
                }, 2)
                ->make();

@yajra Any suggestions for fixing this?

garbinmarcelo on Dec 1, 2017

@mleppala Why use?

   aoColumns: [
         {data:'name',name:'name'},
         {
             data: 'data_hora', name: 'data_hora',
         }
     ],

Where does this fit into my code since I use multiple tables dynamically?

I still do not understand and could not make the order work properly.

garbinmarcelo on Dec 1, 2017