Digamos que tengo dos tablas:
--------------------------------
| user_id | permissions |
---------------------------------
| 1 | ['perm1', 'perm2'] |
---------------------------------
| 2 | ['perm1'] |
---------------------------------
----------------------------------------
| integration_id | permissions_required |
-----------------------------------------
| 1 | ['perm1'] |
-----------------------------------------
| 2 | ['perm2'] |
-----------------------------------------
| 3 | ['perm1', 'perm2'] |
------------------------------------------
Me gustaría poder consultar todas las integraciones a las que un usuario específico tiene acceso. He estado buscando por un tiempo, pero soy bastante nuevo en el uso de mysql hasta este punto.
¿Cómo haría para consultar estas tablas para encontrar todas las integraciones en las que el usuario tiene todos los permisos necesarios?
EDITAR: Me doy cuenta de que no mencioné que los permisos y las columnas permissions_required son columnas JSON. Según la respuesta aceptada, voy a mencionar el tema de la reestructuración de los permisos de nuestra base de datos para usar muchos-> muchos tradicionales en lugar de matrices.
Este es un claro ejemplo de por qué almacenar valores separados por comas en una sola columna es una mala idea. Con el diseño que tiene, deberá dividir cadenas (MySQL ni siquiera tiene una función incorporada para eso) y luego unir tablas según el resultado de las divisiones.
La tarea que desea hacer sería increíblemente más fácil en un esquema de tablas diseñado correctamente.
Comenzando desde cero, sus entidades son user
, integration
y permission
por lo tanto necesita una tabla para cada una de ellas.
User: id, name, whatever_else
Integration: id, description, whatever_else
Permission: id, description, whatever_else
La relación entre user
y permission
es many-to-many
, porque cada uno user
puede tener múltiples permissions
y cada uno permission
puede asignarse a múltiples users
: desea modelar esto con otra tabla y luego
UserPermission: userID, permissionID
Lo mismo vale para la relación entre permission
y integration
: cada uno integration
puede requerir varios permissions
, cada uno permission
puede ser necesario para varios integrations
.
IntegrationPermission: integrationID, permissionID
Sus datos de muestra se verían así
User
id | name
1 | User1
2 | User2
Permission
id | desc
1 | Perm1
2 | Perm2
UserPermission
userID | permissionID
1 | 1
1 | 2
2 | 1
Integration
id | desc
1 | Integration1
2 | Integration2
3 | Integration3
IntegrationPermission
integrationID | permissionID
1 | 1
2 | 2
3 | 1
3 | 2
Ahora la consulta: la parte complicada es tomar a los usuarios que tienen todos los permisos necesarios para una integración. Construyamos esto en unos pocos pasos.
En primer lugar, combinamos las dos tablas de relaciones para obtener el usuario, la integración y el número de permisos que tiene un usuario para esa integración.
select t1.userID,
t2.integrationID,
count(distinct t2.permissionID) as userIntegrationPermissions
from userPermission t1
join integrationPermission t2
on t1.permissionID = t2.permissionID
group by t1.userID, t2.integrationID
Luego, necesitamos el recuento de los permisos necesarios para cada integración.
select integrationID,
count(permissionID) permCount
from integrationPermission
group by integrationID
Finalmente unimos estos dos, agregando tablas de Usuario e Integración para obtener las descripciones (esto es opcional por supuesto)
select tt3.id, tt3.name, tt4.id, tt4.desc
from (
select t1.userID,
t2.integrationID,
count(distinct t2.permissionID) as userIntegrationPermissions
from UserPermission t1
join IntegrationPermission t2
on t1.permissionID = t2.permissionID
group by t1.userID, t2.integrationID
) tt1
join (
select integrationID,
count(permissionID) permCount
from IntegrationPermission
group by integrationID
) tt2
on tt1.integrationID = tt2.integrationID and
tt1.userIntegrationPermissions = tt2.permCount
join User tt3
on tt1.userID = tt3.id
join Integration tt4
on tt1.integrationID = tt4.id
order by 1, 3
Puedes jugar con él en este enlace (rextester)
Este artículo se recopila de Internet, indique la fuente cuando se vuelva a imprimir.
En caso de infracción, por favor [email protected] Eliminar
Déjame decir algunas palabras