php - Get all types Belong to employee and return this type

Solution:

In this case, you need to use joins.

First, you need to determine the relationship between your rules and employees table. Without that, how should you be able to link the rule_request with the employees table? Something like employee_rules?

You can run something like this.

User::select("users.*")
 ->join("employees AS em", "em.user_id", "users.id")
 ->join("employee_rules AS er", "er.employee_id", "em.id")
 ->join("rules", "rules.id", "er.rule_id")
 ->join("rule_requests AS rr", "rr.rule_id", "rules.id")
 ->where("rr.request_type = 'Normal leave'")
 ->groupBy("users.id")
 ->get();

Since you want to return the users, your main table should be the user. Then you need to join it at your employees table. Then once you determine the link between your rules and employees table you then use it to join the two tables. Make sure to use inner join it will ensure you that it will only retrieve users that only have request_type => Normal leave.

Side note: When working with large databases, the subquery is a really bad solution. Joins are the best in that kind of scenario.

Cheers mate!