Проверьте, существует ли связь в базе данных через сводную таблицу
ситуация
Пользователь может принадлежать к нескольким организациям, связанным с помощью сводной таблицы employees.
Модели в действии: User, Employee и Organizations
Соответствующие столбцы базы данных:
users
- id
employees
- user_id
- organization_id
organizations
- id
Цель
Эффективный способ проверить, разделяют ли пользователь 1 и пользователь 2 хотя бы один organization_id в таблице employees.
вариант использования
Конечная точка Api /api/v1/user/# возвращает дополнительные метаданные о пользователе.
Используя policy, он проверяет, совпадают ли текущий пользователь и идентификатор пользователя из URL-адреса, или они оба являются сотрудниками по крайней мере в одной организации, organization_id на этом этапе неизвестен, все, что имеет значение, это то, что он совпадает.
пример А
пользователь A (1) является сотрудником организации foo (1)
пользователь B (2) является сотрудником организационной панели (2)
Таким образом, таблица сотрудников содержит следующие записи:
+-----------------+---------+
| organization_id | user_id |
+-----------------+---------+
| 1 | 1 |
| 2 | 2 |
+-----------------+---------+
в этом примере запрос должен возвращать ложный результат, поскольку между пользователем A и B нет общего organization_id.
пример B
пользователь A (1) является сотрудником организации foo (1)
пользователь A (1) является сотрудником организации foobar (3)
пользователь B (2) является сотрудником организационной панели (2)
пользователь B (2) является сотрудником организации foobar (3)
Таким образом, таблица сотрудников содержит следующие записи:
+-----------------+---------+
| organization_id | user_id |
+-----------------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
+-----------------+---------+
в этом примере запрос должен возвращать истинный результат, поскольку существует общий organization_id между пользователем A и B
код политики
/**
* 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
}
}
код, который работает, но не выглядит эффективным
foreach ($user->organizations()->with('users')->get() as $organization) {
if ($organization->users->where('id', $model->id)->first()) {
return true;
}
}
return false;
экспериментальный код с объединениями вместо того, что сделано с моделями laravel
\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()
запрошенное решение
Эффективный запрос для получения (с возможностью преобразования в) логического результата, если 2 пользователя используют хотя бы один organization_id в таблице employees, «бонусные баллы» за использование моделей / построителя запросов laravel.
нижний колонтитул
Спасибо, что прочитали, вот картошка: ?
Предпочтительно запрос mysql в формате построителя запросов laravel или как отношение модели @TimBiegeleisen
как насчет того, чтобы взять весь идентификатор организации у обоих пользователей и проверить метод php in_array? разве это не будет решением? поскольку вам не нужен идентификатор организации, этого будет достаточно, верно?
Не могли бы вы показать нам результат точный, который вы хотите получить из этого запроса, в терминах значений?
@KevinRED это вариант, но в настоящее время я ищу сделать это как запрос, чтобы не втягивать много данных из базы данных.
@Quezler, хорошо, понял
@TimBiegeleisen - вывод, который можно интерпретировать как логическое значение, например массив событий, которые могут быть count() > 0ed
Я попытался ответить ниже в необработанном MySQL; посмотри.
@TimBiegeleisen спасибо за этот ответ, прежде чем я помечу его как принятый, я планирую подождать еще немного, чтобы увидеть, будет ли добавлен более laravel-ish ответ, но я буду голосовать за него заранее ?
Просто чтобы дважды проверить, вы хотите увидеть, разделяют ли $user и $model одну организацию, или просто пользователь разделяет организацию с кем-то еще?
@RossWilson, чтобы узнать, связан ли $user с $model через общий organization_id в таблице employees, но идентификатор organization не известен во время запроса, все, что имеет значение, это то, что они оба являются частью по крайней мере одной организации, что они оба сотрудник
Ответы 4
В качестве необработанного запроса я, вероятно, использовал бы здесь EXISTS, но, поскольку вам нужно будет перенести любой запрос в код Laravel / PHP, я мог бы предложить использовать самосоединение:
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;
Это просто вернет пару значений user_id(1, 2). Если вы хотите, чтобы запрос возвращал все пары отдельных пользователей, совместно использующих хотя бы одну организацию, вы можете переписать этот запрос следующим образом:
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;
Предполагая, что в вашей модели Organization настроены отношения пользователей, вы можете использовать метод где:
$user->organizations()->whereHas('users', function ($query) use($model) {
$query->where('users.id', $model->id);
})->exists();
Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous@Quezler Я обновил свой ответ, включив имя таблицы в предложение where.
Работает как шарм, но выполняет ли он только один запрос или повторяет обратный вызов для каждого пользователя и т. д. В базе данных?
@Quezler Это будет всего лишь один запрос.
Самый читаемый пример, который я могу придумать, - это добавить что-то вроде этого в пользовательскую модель:
public function isColleagueWith(User $user): bool
{
return $this->organizations->intersectByKey($user->organizations)->count() > 0;
}
Использование легко читать и понимать:
$userA->isColleagueWith($userB);
Если вы хотите использовать меньше запросов к БД, вы можете вместо этого напрямую запросить сводную таблицу. Здесь вы можете получить все организации, в которых работают два пользователя, и проверить, содержит ли список повторяющиеся идентификаторы организаций.
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();
}
}
Использование:
Employees::areColleagues($userIdA, $userIdB);
Если у вас уже есть пользователи, это всего лишь один дополнительный запрос, чтобы получить и организации, нетерпеливо загрузив отношение организаций, поэтому вы можете легко использовать решение № 1. Если у вас только два идентификатора пользователя, это проще с решением № 2 .
Чтобы проверить, принадлежат ли пользователи 1 и 2 к одной или нескольким организациям:
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;
Имеют оба этих индекса:
(user_id, organization_id)
(organization_id, user_id)
Спасибо за картошку, но вы можете сообщить нам, что вы хотите здесь? Вы хотите, чтобы необработанный запрос MySQL решил вашу проблему, хотите ли вы код Laravel / PHP или что-то еще?