Ordering a relation of a relation fails

[nWidart]

Hello,

Summary of problem

I have a table of users, which are linked to installers, and an installer has a company. On the table I display the company of the installer.

Meaning I have this in the getColumns method:

private function getColumns()
{
    return [
        'action' => ['orderable' => false, 'searchable' => false, 'class' => 'datatablesActionsColumn'],
        'id',
        'email',
        'login',
        'first_name',
        'last_name',
        'company_id' => 'installer.company.name',
    ];
}

On display this works fine, it read the relation of the relation.

However when ordering the Company_id column, it fails on the ajax request with this error:

Call to undefined method Illuminate\Database\Query\Builder::installer.company()

Expected behaviour

When ordering on the company_id column, it should use the relation of the relation.

builder::installer->company()

System details

• Operating System: Ubuntu 14.04
• PHP Version: 7.0.5
• Laravel Version 5.1.34
• Laravel-Datatables Version: 6.10.1

Am I missing something ?

Thank you.

[yajra]

I think this issue is connected with #521

[nWidart]

Hello,

521 was fixed thanks to you comment, by adding ->select('users.*'). However this issue still persists. Still getting Call to undefined method Illuminate\Database\Query\Builder::installer.company().

Thank you,

[intrepidws]

I have the same problem. This is one of those instances where a BelongsToThrough relation would be really helpful.

Issue #521 did not fix the issue for me either.

[yajra]

@intrepidws, thanks for the info. I think BelongsToThrough relation is not yet supported.

What the recent PR supported is belongsTo relationship which was tested and proven to work. Will try to dig more on eager loading support when I had the chance.

ATM, a more stable solution would be to use join statements for complex relationships.

[nWidart]

I only have the usual relations though:

// On user class

public function installer()
{
    return $this->belongsTo(User::class, 'installer_id');
}

public function company()
{
    return $this->belongsTo(Company::class);
}

However if I do use a join() statement, it won't create a correct collection object.

[yajra]

@nWidart, belongsTo should work. Please double check your column name declarations as stated in the demo. Thanks!

[nWidart]

One belongs to works, yes. The issue is with 2 belongs 2 chained after each other.

The demo doesn't show this particular use-case. Demo only has Post belongsTo User. I have a second chained relation. User belongsTo Installer belongsTo Company.

My column declaration is same as on first post of the issue:

'company_id' => 'installer.company.name',

And this makes the package try to load this relation:

Call to undefined method Illuminate\Database\Query\Builder::installer.company()

It makes a dot, between the relations, which the query builder doesn't understand. The relation definition is on this post.

As far as I can tell I see it being made here: https://github.com/yajra/laravel-datatables/blob/6.0/src/Engines/QueryBuilderEngine.php#L149 However when dumping at that location, it doesn't get touched upon filtering columns.

[intrepidws]

@yajra, you're right belongsToThrough isn't in the framework - and it doesn't look like it will be. I was just saying that it would be helpful here, since that's the crux of the issue. It's a belongsTo through another belongsTo.

@nWidart, does searching on that installer.company.name work for you?

[nWidart]

@intrepidws Good idea, I haven't tested that.

It doesn't work indeed. However it gives another error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'self_8453f0c508eb4e58a4ac1fa9057ac9a8.company_id' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `users` where (select count(*) from `roles` inner join `role_users` on `roles`.`id` = `role_users`.`role_id` where `role_users`.`user_id` = `users`.`id` and `slug` = customer) >= 1 and (LOWER(`first_name`) LIKE %tes% or LOWER(`last_name`) LIKE %tes% or LOWER(`email`) LIKE %tes% or LOWER(`login`) LIKE %tes% or (select count(1) from `companies` where `self_8453f0c508eb4e58a4ac1fa9057ac9a8`.`company_id` = `companies`.`id` and `name` like %tes%) >= 1 or LOWER(`id`) LIKE %tes%)) count_row_table)

[intrepidws]

@nWidart Well that's weird. On the surface it makes sense (it's looking for company_id on the wrong table) but I'm confused why it's using "self_8453f0c508eb4e58a4ac1fa9057ac9a8".

[intrepidws]

I can confirm that using a join appears to work for me here, both on filtering and searching.

That said however, it would be ideal to be able to use normal Eloquent language instead.

[yajra]

@nWidart, nested eager loading is not yet fully functional and needs an enhancement. Issue is a duplicate of #509. In this regard, I suggest you use join statements while we still work on the solution for nested relations.

@nWidart @intrepidws, if possible, please submit a PR to support nested relations. I don't quite use this approach on my current projects and it may take me some time to implement this. Thanks a lot!

[yajra]

Fixed via #2051.