sorting related data from the server

Shovo

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>

allan

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

Shovo

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"

allan

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

Shovo

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

colin

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

Colin

Shovo

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

allan

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

Shovo

what can I do to you