Laravel-datatables: Sorting and filtering with relations

[perkola]

First of all, great package! It really saves me a lot of time.

My problem is that if i have entities with relations between them such as an article having one auhtor I can't sort the table of articles based on the authors name since I get an error like:

Column not found: 1054 Unknown column 'author' in 'order clause' (SQL: select * from `articles` order by `author` asc limit 10 offset 0)

// From controller
$articles = Article::with('author');

return Datatables::of($articles)->make(true);

I have the following in my Article model:

public function author()
{
    return $this->belongsTo('App\Author');
}

So my question is, how can I modify the default behaviour to take relations in perspective when sorting and filtering a data table.

[yajra]

Yes, since author is added via eager loading, you will not be able to sort by it. You need to specifically declare it on your js as orderable: false. But if you wish to sort and filter via author name for instance, then you need to use a join statement and select the appropriate fields like sample code below:

$articles = Article::select('articles.*', 'authors.name')
->leftJoin('authors', 'authors.id','=','articles.author_id');

[mtx-z]

Related models column sorting is only available with leftjoins ? Using a eloquent 'with' will not work ? Thx

[yharishy]

Sorting is possible using related models also in laravel using yajra datatable.

In Javascript File, datatable column array :
columns: [ {data: 'ids', name: 'id',orderable: false,searchable: false,"width": "3%"}, {data: 'first_name', name: 'first_name',"width": "15%"}, {data: 'last_name', name: 'last_name',"width": "15%"}, {data: 'cellnumber', name: 'cellnumber',"width": "15%"}, {data: 'detail.website', name: 'detail.website',"width": "10%"}, {data: 'detail.city', name: 'detail.city',"width": "10%"}, {data: 'detail.specialization', name: 'detail.specialization',"width": "10%"}, {data: 'iscertified', name: 'detail.iscertified',"width": "10%"}, ],

In Laravel Controller File :
$data = User::with('detail')->has('detail')->select(array('users.id','first_name','last_name','users.cellnumber','users.email'))->Usertype('Doctor');

[petar-dambovaliev]

@HarishSurypalYadav , it works for direct relationship but when you try to do

user.item.type.name

Falls apart.

[nikosv]

@yajra are you aware of the issue @PetarDambovaliev mentions. I am facing the exact same problem.

[madsem]

Did anybody came up with a solution to this? Stuck at the exact same problem atm :)

[flyrmyr]

@madsem This method may or may not be helpful, but.. The orderBy method seems to be using the name attribute on the column data. If you eager load the relation, for example:

$query = Model::with('relation');
````

you can setup the relation in the column array as such:

columns: [
...,
{
"searchable" : false,
"name" : "relation_id",
"title" : "Relation",
"data" : "relation.name"
},
...,
]
```

This will mean, however, that the global or column search options will be looking for a match on the model.relation_id and not on the model.relation.name which you'd likely prefer.

[fico7489]

you can sort related attribute very easily with this package: https://github.com/fico7489/laravel-eloquent-join

[akoSalman]

@perkola I had the same issue, when running
return Datatables::of($articles->select('articles.*')->make(true); i
nstead of
return Datatables::of($articles)->make(true);,
it solved.