laravel 8 returns wrong id in collection - bug or I am wrong?

user_1312948 0
Viewed 228

The table peoples has a field company_id linked to the table companies. When I do the following query in pma, I get a list of peoples with their correct id (peoples.id).

SELECT peoples.*, companies.* FROM peoples LEFT JOIN companies ON peoples.company_id = companies.id WHERE (companies.is_active > 0) ORDER BY companies.company;

If I do the same query in Laravel, the id field in the collection $clients are in fact the comnpanies.id.

$clients = DB::select("SELECT peoples.*, companies.* FROM peoples LEFT JOIN companies ON peoples.company_id = companies.id WHERE (companies.is_active > 0) ORDER BY companies.company");

The same happens with eloquent a similar query:

$clients = People::join("companies","peoples.company_id","=","companies.id")->where('companies.is_active','>',0)->orderBy('company')->get();

The problem can be solved by selecting only the necessary fields (excluding companies.id):

$clients = DB::select("SELECT peoples.id, peoples.firstname, peoples.lastname, companies.company FROM peoples LEFT JOIN companies ON peoples.company_id = companies.id WHERE (companies.is_active > 0) ORDER BY companies.company");

Or in eloquent:

$clients = People::select('peoples.id', 'peoples.firstname', 'peoples.lastname', 'companies.company')
    ->join('companies', 'peoples.company_id', '=', 'companies.id')
    ->where('companies.is_active', '>', 0)
    ->orderBy('company')
    ->get();

Is that a bug or am I doing something wrong?

eloquent laravel
1 Answers

Use leftjoin instead of join with your eloquent query

People::select('peoples.id','peoples.firstname','peoples.lastname','companies.company')->leftjoin("companies","peoples.company_id","=","companies.id")->where('companies.is_active','>',0)->orderBy('company')->get();
user_85781930
Related