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:

user_1033654 · Answer

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();

user_1863229 · Answer

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;

user_1034002 · Answer

The most readable example I can think of would be to put something like this in the user model:

public function isColleagueWith(User $user): bool
{
    return $this->organizations->intersectByKey($user->organizations)->count() > 0;
}

Usage is easy to read and understand:

$userA->isColleagueWith($userB);

If you wanted to use less DB queries, you could query the pivot table directly instead. Here you an get all organizations that employ the two users and check if the list contains any duplicate organization ids.

use Illuminate\Database\Eloquent\Relations\Pivot;

class Employees extends Pivot
{
    public function areColleagues(int $userIdA, int $userIdB): bool
    {
        $employments = $this->where('user_id', $userIdA)
            ->orWhere('user_id', $userIdB)
            ->get('organization_id');

        return $employments->count() > $employments->unique()->count();
    }
}

Usage:

Employees::areColleagues($userIdA, $userIdB);

user_1766831 · Answer

To check if users 1 and 2 share one or more organizations:

SELECT EXISTS (
          SELECT 1 FROM employees AS a
                   JOIN employees AS b  USING(organization_id)
                   WHERE  a.user_id = 1
                     AND  b.user_id = 2;

Have both of these indexes:

(user_id, organization_id)
(organization_id, user_id)

Check if relation exists in database via pivot table

4 Answers