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!

[shankhadevpadam]

I got the same issue while sorting nested relationships. Search work fine but sorting is not working.

[mansoorkochy]

set where clause attribute as a pivot table key. for example: The agency model has many to many relationships to the User model Agency one to many relationships to Property

then I need to retrieve all property with agency relationship and also nested user where user id is equal to 1

Property::with(['agency' => function($query){ $query->with(['user' => function($query){ $query->where('user_id',2); // previosuly I used to search for id but user_id worked }]);

best regards :)

[anchetaWern]

For those having the same problem. This is how I solved mine:

$user_products = Transaction::has('user')
            ->select('transactions.*')
            ->with(['user' => function ($query) use ($search_term) {
                $query->where('name', 'LIKE', "%{$search_term}%")
                    ->orWhere('email', 'LIKE', "%{$search_term}%");
            }])
            ->where('transaction_type', $type);

user is the joined table.

Then in your javascript, make sure you're selecting the relationship along with the field for both the data and name:

 { data: "user.email", name: "user.email" },
 { data: "user.name", name: "user.name" },

Hope it helps.

[RayhanYulanda]

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!

public function query(Employee $model)
    {
        return $model->newQuery()->with('subDepartment')->with('subDepartment.department');
    }
    
    Column::make('department'),
    Column::make('sub_department_id')->name('subDepartment.name')->data('sub_department.sub_department_name')->title('Sub Department'),

I've tried this but i get the same error

[yajra]

Self-join / nested relationship is not yet fully supported. A work-around is to use filterColumn. Something like:

    return DataTables::eloquent($model)
                ->filterColumn('subDepartment.name', function($query, $keyword) {
                    $query->whereHas('subDepartment', fn($q) => $q->where('name', $keyword));
                })
                ->toJson();

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.