sorting related data from the server

sorting related data from the server

ShovoShovo Posts: 5Questions: 1Answers: 0

Hi guys. I use Server-side in the dataTable and everything works fine, except for sorting those columns that were connected to the main table, in the setting the query goes wrong, but the result is not valid. I will be grateful for help.
$ajax = Tracker::with('user')->with('settings')->where('trackers.status', '1'); try { return datatables($ajax )->make(true); } catch (\Exception $e) { $e->getMessage(); }

    <script>


        var table =   $("#tracker").DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": "/trackers",
            "columnDefs": [{ "orderable": false, "targets": [ 9 ] }],
            "columns": [
                {data: 'id'},
                {data: 'imei'},
                {data: 'name',
                    defaultContent: ''
                },
                {
                    data: 'user.email',
                    defaultContent: 'Не превязон к ползовотеля'
                },
                {
                    data: 'settings.tied',
                    render: function( data, type, full, meta ) {

                        return data === '1' ? 'Да' : (data === '0' ? 'Нет' : 'не известно');
                    },
                    defaultContent: ''
                },
                {data: 'settings.type',
                    defaultContent: 'Нет'
                },
                {
                    data: 'settings.sum_html',
                    defaultContent: '',
                    render: function( data ) {
                        if(data !== null && data !== undefined){
                            res = data.split('~');
                            let html = '';
                            res.forEach((e,i) => {
                                html += `<p>${e}</p>`;
                            });
                            return html;
                        }else{
                            return null
                        }
                    },
                },
                {data: 'settings.auto_renewal',
                    defaultContent: 'Нет'
                },
                {data: 'comment',
                    defaultContent: ''
                },
                {
                    data: null,
                    render: function( data ) {
                        return "<a href=\"/trackers/"+data.id+"\" class=\"btn btn-info\"> Подробно</a>\n" +
                            " <form action=\"/trackers/"+data.id+"\" method=\"post\" class=\"d-inline\">\n" +
                            ' @csrf\n' +
                            ' @method('DELETE')\n' +
                            " <button type=\"submit\" class=\"btn btn-danger delete-btn\">Удалить</button>\n" +
                            " </form>"
                    },
                },
            ],

        });


    </script>

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Do you mean that the columns where you use columns.render are not sorting correctly? Or is it something else and I've not understood?

    The columns.render is a client-side action, so yes, the server (which does the sorting when serverSide is enabled) wouldn't know anything about the rendering on the client-side. You would need to update your server-side script to do that rendering an sort based on the rendered data.

    Allan

  • ShovoShovo Posts: 5Questions: 1Answers: 0
    edited September 2021

    look in the image, 1,2,3,4,9 work correctly and the rest do not, although I see a valid sql query in the debugger. for example!

    query: "select * from `trackers` left join `tracker_settings` on `tracker_settings`.`tracker_id` = `trackers`.`id` where `trackers`.`status` = ? order by `tracker_settings`.`tied` asc limit 10 offset 0"
    

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Do you mean if you click to sort on the Email column it doesn't order umiller, wanda, xsipes? If so, that's an error in the server-side script.

    Allan

  • ShovoShovo Posts: 5Questions: 1Answers: 0

    columns 0,1,2,8 belong to the Tracker model and they are sorted pretty well, but other connections via dependency (with ('user') -> with ('settings')) mail works fine here, but nothing else

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    We'll need to see the full query to understand why the ordering isn't happening.

    Colin

  • ShovoShovo Posts: 5Questions: 1Answers: 0
    edited September 2021

    TrackerController

        public function index(TrackerFilter $request)
        {
    
            $filter = Tracker::filter($request);
            $trackers = $filter->with('user')->with('settings')->where('status', '1')->paginate(50);
            $ajax = $filter->with('user')->with('settings')->where('trackers.status','=', '1');
    
            if ( \Request::wantsJson() ){
                try {
                    return datatables($ajax)->make(true);
                } catch (\Exception $e) {
                    $e->getMessage();
                }
            }
            else{
                return view('admin.actions.trackers.index', compact('trackers'));
            }
        }
    

    TrackerFilter

    namespace App\Filters;
    
    
    class TrackerFilter extends QueryFilter
    {
    
    
        public function brand($brand)
        {
            if (!is_null($brand)) {
                return $this->builder->where('brand', $brand);
            }
        }
    
    
        public function status($status = '0')
        {
            if (!is_null($status)) {
                return $this->builder->WhereHas('settings', function ($q) use ($status) {
                    $q->where('status', $status);
                });
            }
        }
    
    
        /**
         * @param mixed $builder
         * search by 'id','imei','name','email'
         */
        public function search($search = null)
        {
    
    
            if (is_string($search)) {
                $res = $this->builder
                    ->where('id', 'Like', '%' . $search . '%')
                    ->orWhere('imei', 'Like', '%' . $search . '%')
                    ->orWhere('name', 'Like', '%' . $search . '%')
                    ->orWhereHas('user', function ($q) use ($search) {
                        $q->where('email', 'Like', '%' . $search . '%');
                    });
                return $res;
            }
        }
    
    
    }
    

    QueryFilter

    namespace App\Filters;
    
    use Illuminate\Database\Eloquent\Builder;
    use Illuminate\Http\Request;
    
    class QueryFilter
    {
        public $request;
        protected $builder;
        protected $delimiter = ',';
    
    
    
        public function __construct(Request $request)
        {
            $this->request = $request;
        }
    
    
    
        public function filters()
        {
            return $this->request->query();
        }
    
    
    
    
        public function apply(Builder $builder)
        {
            $this->builder = $builder;
            foreach ($this->filters() as $name => $value) {
                if (method_exists($this, $name)) {
                    call_user_func_array([$this, $name], array_filter([$value]));
                }
            }
    
            return $this->builder;
        }
    
    
    
    
        protected function paramToArray($param)
        {
            return explode($this->delimiter, $param);
        }
    }
    

    select * from `trackers` left join `tracker_settings` on `tracker_settings`.`tracker_id` = `trackers`.`id` where `trackers`.`status` = ? order by `tracker_settings`.`tied` asc limit 10 offset 0
    

    in phpmyadmin works correctly

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    In your query it is ordering by the tied column. However, I don't see anything in your code which is specifying that. So I don't know if that is a static part, or if you are changing the column based on the sorting information submitted by DataTables.

    Allan

  • ShovoShovo Posts: 5Questions: 1Answers: 0

    what can I do to you

Sign In or Register to comment.