CODEWITHSUNDEEP
mysqlsqllaraveleloquent
Andreas Hunter
Andreas Hunter

Reputation: 5004

Laravel Eloquent left join query

I have like this query which work in plain sql:

SELECT users.id, users.name, roles.name
FROM users
LEFT JOIN model_has_roles ON model_has_roles.model_id = users.id
LEFT JOIN roles ON roles.id = model_has_roles.role_id
ORDER BY roles.name ASC

Using laravel I tried like this but it's doesn't work:

$user = $request->user();

$role = (array) $request->get('role', []);
$order = (array) $request->get('order');

$perPage = $request->get('perPage') ?? 10;

$users = User::where('id', '!=', $user->id);

if(count($role)) {
    if (($key = array_search('admin', $role)) !== false) {
        unset($role[$key]);
    }
    $users = $users->whereHas('roles', function ($query) use($role) {
        return $query->whereIn('name', $role);
    });
} else {
    $users = $users->whereHas('roles', function ($query) {
        return $query->whereIn('name', ['farmer', 'specialist', 'company']);
    });
}

if(count($order) && isset($order['field'])) {
    $orderType = $order['type'] ?? 'ASC';
    if($order['field'] == 'role') {
        $users = $users->leftJoin('model_has_roles', 'model_has_roles.model_id', '=', 'users.id')
        ->leftJoin('roles', 'roles.id', '=', 'model_has_roles.role_id')
        ->orderBy('roles.name', $orderType);
    } else {
        $users = $users->orderBy($order['field'], $orderType);
    }
}

$users = $users->paginate($perPage);

Error message:

`"message": "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate from `users` left join `model_has_roles` on` `model_has_roles`.`model_id` = `users`.`id` left join `roles` on `roles`.`id` = `model_has_roles`.`role_id` where `id` != 3 and exists (select * from `roles` inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` where `users`.`id` = `model_has_roles`.`model_id` and `model_has_roles`.`model_type` = App\\Models\\User and `name` in (specialist, company)))",

Upvotes: 1

Views: 1528

Answers (1)

Abolfazl Mohajeri
Abolfazl Mohajeri

Reputation: 1988

Because your both models have id and after sql execute the query id column will be ambiguous so this will help you:

$users = User::where('id', '!=', $user->id);

change to

$users = User::where('users.id', '!=', $user->id);

Then try:

$users = $users->select(['users.id', 'users.name', 'roles.name'])
                ->leftJoin('model_has_roles', 'model_has_roles.model_id', '=', 'users.id')
                ->leftJoin('roles', 'roles.id', '=', 'model_has_roles.role_id')
                ->orderBy('roles.name', 'ASC');

Upvotes: 2

Related Questions