Laravel query exclude data

Question

I have query where i get all users with certain roles and it's working fine, what I need now is to exclude those users who has been saved in third table already and just return users without row in third table.

Logic

Users table (user has many roles, user hasOne manager)
Roles Table (role belongsTo user)
Managers Table (managers belongsTo user)

First time

User who has role of manager I return them all. (let say 2 users)

I save one of them in managers table.

Second time

User who has role of manager I return them all.

I save one of them in managers table.

Issue

In second time i should only get 1 user as I previously saved one of them into managers table. But with my current code I do still get 2 users.

Code

$users = User::whereHas("roles", function($q) {
  $q->where("name", "manager");
})->get();

SQL output of code above is:

"select * from `users` where 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` = ? and `name` = ?) and `users`.`deleted_at` is null"

Any idea?

This is not possible with the information you've provided. How do you tell if an entry in managers table is the same as the entry in users table? Why is the same person in two tables? If these are separate entities, just delete from the users table when you insert into the managers table. — miken32
– miken32, Commented Mar 11, 2020 at 3:21
@miken32 not sure if you get the logic, users id is stored in managers table. so when users with role of manager has his/her id in managers table then i filter it out. that's how. — mafortis
– mafortis, Commented Mar 11, 2020 at 5:15

Kristian · Accepted Answer · 2020-03-11 02:59:29Z

0

From your question, I predict that you have this database structure:

User
user_id(PK) | ...

Role
role_id(PK) | user_id | name | ...

Manager
manager_id | user_id | ...

So, you need to query all users, who have role='manager', but is not in table manager.

Just do a left join:

$users = User::query()
  ->select("user.*")
  ->join("Role", "user.user_id", "=", "role.user_id")
  ->leftJoin("Manager", "manager.user_id", "=", "user.user_id")
  ->whereNull("manager.manager_id")
  ->where("role.name", "=", "manager")
  ->get();

It will generate this SQL query:

SELECT user.*
FROM
  user
  INNER JOIN role ON role.user_id=user.user_id
  LEFT JOIN manager ON manager.user_id=user.user_id
WHERE manager.manager_id IS NULL AND role.name='manager'

answered Mar 11, 2020 at 2:59

Kristian

2,4518 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel query exclude data

Logic

Issue

Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Logic

Issue

Code

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related