Check if relation exists in database via pivot table

Question

situation

User can belong to multiple organizations, linked via a pivot table called employees

Models at play: User, Employee & Organizations

Relevant database columns:

users
- id

employees
- user_id
- organization_id

organizations
- id

goal

An efficient way to check if user 1 and user 2 share at least one organization_id in the employees table

usecase

Api endpoint /api/v1/user/# returns additional metadata regarding the user.

Using a policy, it checks if the current user and the user id from the url are the same, or that they are both employee in at least one organization, the organization_id is not known at this stage, all that matters is that it matches.

example A

user A (1) is employee of organization foo (1)

user B (2) is employee of organization bar (2)

employee table thus has the following records:

+-----------------+---------+
| organization_id | user_id |
+-----------------+---------+
|               1 |       1 |
|               2 |       2 |
+-----------------+---------+

in this example the query should return a false result, since there is no shared organization_id between user A and B

example B

user A (1) is employee of organization foo (1)

user A (1) is employee of organization foobar (3)

user B (2) is employee of organization bar (2)

user B (2) is employee of organization foobar (3)

employee table thus has the following records:

+-----------------+---------+
| organization_id | user_id |
+-----------------+---------+
|               1 |       1 |
|               2 |       2 |
|               3 |       1 |
|               3 |       2 |
+-----------------+---------+

in this example the query should return a true result, since there is a shared organization_id between user A and B

policy code

/**
 * Determine whether the user can view the model.
 *
 * @param  \App\User  $user
 * @param  \App\User  $model
 * @return mixed
 */
public function view(User $user, User $model)
{
    if ($user->is($model)) {
        return true;
    } else {
        // check if users share at least one organization 
    }
}

code that works but does not look efficient

foreach ($user->organizations()->with('users')->get() as $organization) {

    if ($organization->users->where('id', $model->id)->first()) {
        return true;
    }

}

return false;

experimental code with joins instead of something done with laravel models

        \Illuminate\Support\Facades\DB::table('employees as auth_employee')
        ->join('employees as other_employee', 'other_employee.organization_id', '=', 'auth_employee.organization_id')
//        ->join('organizations', 'organizations.id', '=', 'organizations.id')
        ->where('auth_employee.id', 1)
        ->where('other_employee.id', 2)
        ->get()

requested solution

An efficient query to get a (castable to) boolean result result wether or not 2 users share at least one organization_id on the employees table, 'bonus points' for using the laravel models / query builder.

footer

Thanks for reading, here is a potato: 🥔

Answer 1

Assuming you have a users relationship set up in your Organization model, you could use the whereHas method:

$user->organizations()->whereHas('users', function ($query) use($model) {
    $query->where('users.id', $model->id);
})->exists();

Answer 2

As a raw query, I would probably use EXISTS here, but since you would need to port any query to Laravel/PHP code, I might suggest using a self-join:

SELECT DISTINCT
    e1.user_id, e2.user_id
FROM employees e1
INNER JOIN employees e2
    ON e1.organization_id = e2.organization_id AND e1.user_id = 2
WHERE
    e1.user_id = 1;

This would just return the user_id pair of values (1, 2). If you wanted a query to return all pairs of distinct users sharing at least one organization, you could rewrite this query to this:

SELECT DISTINCT
    e1.user_id, e2.user_id
FROM employees e1
INNER JOIN employees e2
    ON e1.organization_id = e2.organization_id AND e1.user_id <> e2.user_id;