Laravel-datatables: Nested Relationships - sort & search error: Column not found: 1054 Unknown column

[FilipQL]

Currently, it seems that nested relationships are not fully supported (to be more precise - when having nested relationship, sort and search trigger Column not found: 1054 Unknown column ... error).

I know that this is old issue but was wondering will there be any updates? :) Can we expect this to be supported anytime soon? If not, are there any work-around examples (I couldn't find anything in the documentation)?

It would be really good to support at least 1 level deep nested relationships (e.g. thread.forum.name - see below) because I think that would cover most of the cases (ideally, it would be best to support as much as possible, but I guess that's very complicated/tricky to implement).

Summary of problem or feature request

There are 3 Eloquent models with the following relations:

• Post _belongsTo_ Thread
• Thread _belongsTo_ Forum

I am using Datatables to display posts with nested eager-loading:

Post::select('posts.*')->with('thread.forum');

and I have 'thread.forum.name' column:

{ data: 'thread.forum.name', name: 'thread.forum.name' }

The forum names are displayed in Datatables, but when I try to order by the forum name (or search all searchable columns) - I'm getting the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'thread.forum.name' in 'order clause' (SQL: select posts.* from posts order by thread.forum.name asc limit 10 offset 0)

So, I have to disable _search_ and _sort_ functionalities on this column (by adding orderable: false, searchable: false).

System details

• PHP Version: 7.2
• Laravel Version: 6 LTS
• Laravel-Datatables Version: 9.11.1

[dyaskur]

try to remove select('posts.*'), is it working?

[FilipQL]

try to remove select('posts.*'), is it working?

I tried, the same problem still exists.

[yajra]

Can you please try this:

Post::select('posts.*')->with('thread')->with('thread.forum');

[FilipQL]

@yajra I tried, no difference (the same problem is still there).
As I see, this package doesn't support nested relationships. Or to be more precise (as I wrote above) - when having nested relationship, _sort_ and _search_ trigger Column not found: 1054 Unknown column ... error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'thread.forum.name' in 'order clause' (SQL: select posts.* from posts order by thread.forum.name asc limit 10 offset 0)

So I was wondering will there be any updates regarding this issue? As I mentioned above, it would be really good to support at least 1 more level deep nested relationships because I think that would cover most of the cases (ideally, it would be best to support as much as possible, but I guess that's very complicated/tricky to implement?).

[yajra]

I think I have tested up to 3 or more levels before and it works but not all relations are supported especially on the ordering part. I would say it's tricky since we basically need to join all the related tables behind the scene for it to work properly.

Cannot update the package yet due to several projects at hand. Maybe you can try using orderColumn and filterColumn for a work around. I will replicate to replicate this when I got the chance. Thanks!