mysql - 在按日期排序列方面需要帮助

Question

我正在尝试实现数据表，我希望记录按上次更新的记录排序。

我的 Blade 文件中的代码如下所示

{!! Datatable::table() ->addColumn( "", Lang::get('lang.subject'), Lang::get('lang.ticket_id'), Lang::get('lang.priority'), Lang::get('lang.from'), Lang::get('lang.last_replier'), Lang::get('lang.assigned_to'), Lang::get('lang.last_activity')) ->setUrl(route('get.inbox.ticket')) ->setOrder(array(7=>'desc')) ->setClass('table table-hover table-bordered table-striped') ->render();!!}

Controller 函数是

return \Datatable::collection(new Collection($tickets))
                ->addColumn('id', function ($ticket) {
                      return "<input type='checkbox' name='select_all[]' id='".$ticket->id."' onclick='someFunction(this.id)' class='selectval icheckbox_flat-blue' value='".$ticket->id."'></input>";
                })
                ->addColumn('subject', function ($ticket) {
                    $subject = DB::table('ticket_thread')->select('title')->where('ticket_id', '=', $ticket->id)->first();
                    if (isset($subject->title)) {
                        $string = $subject->title;
                        if (strlen($string) > 20) {
                            $stringCut = substr($string, 0, 30);
                            $string = substr($stringCut, 0, strrpos($stringCut, ' ')).' ...';
                        }
                    } else {
                        $string = '(no subject)';
                    }
                    //collabrations
                    $collaborators = DB::table('ticket_collaborator')->where('ticket_id', '=', $ticket->id)->get();
                    $collab = count($collaborators);
                    if ($collab > 0) {
                        $collabString = '&nbsp;<i class="fa fa-users"></i>';
                    } else {
                        $collabString = null;
                    }

                    $threads = Ticket_Thread::where('ticket_id', '=', $ticket->id)->first(); //
                    $count = Ticket_Thread::where('ticket_id', '=', $ticket->id)->count(); //Ticket_Thread::where('ticket_id', '=', $ticket->id)->get();

                    $attachment = Ticket_attachments::where('thread_id', '=', $threads->id)->get();
                    $attachCount = count($attachment);
                    if ($attachCount > 0) {
                        $attachString = '&nbsp;<i class="fa fa-paperclip"></i>';
                    } else {
                        $attachString = '';
                    }

                    return "<a href='".route('ticket.thread', [$ticket->id])."' title='".$subject->title."'>".$string."&nbsp;<span style='color:green'>(".$count.")<i class='fa fa-comment'></i></span></a>".$collabString.$attachString;
                })
                ->addColumn('ticket_number', function ($ticket) {
                    return "<a href='".route('ticket.thread', [$ticket->id])."' title='".$ticket->ticket_number."'>#".$ticket->ticket_number.'</a>';
                })
                ->addColumn('priority', function ($ticket) {
                    $priority = DB::table('ticket_priority')->select('priority_desc', 'priority_color')->where('priority_id', '=', $ticket->priority_id)->first();

                    return '<span class="btn btn-'.$priority->priority_color.' btn-xs">'.$priority->priority_desc.'</span>';
                })
                ->addColumn('from', function ($ticket) {
                    $from = DB::table('users')->select('user_name')->where('id', '=', $ticket->user_id)->first();

                    return "<span style='color:#508983'>".$from->user_name.'</span>';
                })
                ->addColumn('Last Replier', function ($ticket) {
                    $TicketData = Ticket_Thread::where('ticket_id', '=', $ticket->id)->max('id');
                    $TicketDatarow = Ticket_Thread::where('id', '=', $TicketData)->first();
                    $LastResponse = User::where('id', '=', $TicketDatarow->user_id)->first();
                    if ($LastResponse->role == 'user') {
                        $rep = '#F39C12';
                        $username = $LastResponse->user_name;
                    } else {
                        $rep = '#000';
                        $username = $LastResponse->first_name.' '.$LastResponse->last_name;
                        if ($LastResponse->first_name == null || $LastResponse->last_name == null) {
                            $username = $LastResponse->user_name;
                        }
                    }

                    return "<span style='color:".$rep."'>".$username.'</span>';
                })
                ->addColumn('assigned_to', function ($ticket) {
                    if ($ticket->assigned_to == null) {
                        return "<span style='color:red'>Unassigned</span>";
                    } else {
                        $assign = DB::table('users')->where('id', '=', $ticket->assigned_to)->first();

                        return "<span style='color:green'>".$assign->first_name.' '.$assign->last_name.'</span>';
                    }
                })
                ->addColumn('Last', function ($ticket) {
                    $TicketData = Ticket_Thread::where('ticket_id', '=', $ticket->id)->max('id');
                    $TicketDatarow = Ticket_Thread::select('updated_at')->where('id', '=', $TicketData)->first();

                    return UTC::usertimezone($TicketDatarow->updated_at);
                })
                ->searchColumns('subject', 'from', 'assigned_to', 'ticket_number', 'priority')
                ->orderColumns('subject', 'from', 'assigned_to', 'Last Replier', 'ticket_number', 'priority', 'Last')
                ->make();

我在呈现表之前按“Last”(最后更新日期列)对数据进行排序。

以“mm/dd/yyyy”和“yyyy/mm/dd”格式排序成功，但以“dd/mm/yyyy”格式排序失败。

当以 ''mm-dd-yyyy"格式排序时它工作正常 Sorting "mm-dd-yy"

但是在 dd-mm-yyyy 中排序不正确 sorting "dd-mm-yy"

谁能帮我正确实现所有日期格式的排序。时间差

Answer 1

我建议您使用隐藏跨度。数据表排序通过查看纯文本来工作。

改变

return UTC::usertimezone($TicketDatarow->updated_at);

到

return '<span style="display:none">'.$TicketDatarow->updated_at.'</span>'.UTC::usertimezone($TicketDatarow->updated_at);

看起来会一样，但排序会更好。