Eager loading and accents issue

[Pitttt]

Summary of problem or feature request

I have a relationship with a model called User that has a related model Employee, and Employee has a related model Company.

I'm trying to use Datatables while eager loading the three of them, but if I use ->get() when retrieving the Users, the accents search doesn't work well (if I have an employee called Menéndez, it doesn't appear when I search Menendez), but if I use it without the ->get(), it throws an error while ordering by company.

Code snippet of problem

This orders right but the accents doesn't work:

$employees = User::with(['employee', 'employee.company'])->where('type', 'employee')->get();
return DataTables::of($employees)

This works right with accents but ordering by company throws error:

$employees = User::with(['employee', 'employee.company'])->where('type', 'employee');
return DataTables::eloquent($employees)

The error is: "Exception Message: Attempt to read property "company_relation" on null"

Any idea of how can I fix this?

Thanks!

System details

• Windows 10
• PHP 8.2
• Laravel 11
• Laravel-Datatables 11

[yajra]

I suspect this is a data error. You might have a user record without an employee record, making the relation null.

[Pitttt]

Hey yajra, thanks for your response. All relations are good. The problem is that when I don't order by company name, this is the value for an user:

{
            "id": 25,
            "name": "Zoe Domingo",
            "email": "[email protected]",
            "type": "employee",
            "employee": {
                "user_id": 25,
                "company_relation": "worker_family",
                "company": {
                    "id": 8,
                    "name": "Global Palacios",
                }
            }
        },

But when I order by company name (I can see the registry if I comment the column "company_relation"):

{
            "id": 8,
            "name": "Global Palacios",
            "email": "[email protected]",
            "type": "employee",
            "user_id": 25,
            "company_relation": "worker_family",
            "company_id": 8,
            "employee": null
        },

As you can see, the main ID and the name is not from the user, is from the company, there is no "company" value and the "employee" is null. All of that just for ordering by the company.

The queries in both cases are like this, there is one more for the company if not ordering by it:

Queries without ordering by company:

select count(*) as aggregate from `users` where `type` = ?
select * from `users` left join `employees` on `employees`.`user_id` = `users`.`id` where `type` = ? order by `employees`.`id_number` desc limit 10 offset 0
select * from `employees` where `employees`.`user_id` in (26, 27, 28, 39, 41, 45, 49, 51, 55, 61)
select * from `companies` where `companies`.`id` in (2, 3, 4, 7, 8, 9)

Queries with ordering:

select count(*) as aggregate from `users` where `type` = ?
select * from `users` left join `employees` on `employees`.`user_id` = `users`.`id` left join `companies` on `employees`.`company_id` = `companies`.`id` where `type` = ? order by `companies`.`name` desc limit 10 offset 10
select * from `employees` where `employees`.`user_id` in (4, 8, 9)

[Pitttt]

If anyone is interested, I solved it like this:

$employees = User::with(['employee', 'employee.company'])->where('type', 'employee')
        ->join('employees', employees.user_id', '=', 'users.id')
        ->join('companies', 'companies.id', '=', 'employees.company_id')
        ->select('users.*');

return DataTables::eloquent($employees);

Like this, both search and ordering works well since all the fields are present in every query.

[github-actions]

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.