Mostrar todos os resultados de uma tabela não listada na tabela Other (Junction) Por: id

Question

Eu tenho 2 tabelas com as quais estou interagindo.

A primeira tabela: acl_permissions (é apenas uma tabela normal).

+-----------------------------------+
|           acl_permissions         |
+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

A segunda tabela: acl_group_permissions é uma tabela de junção.

+--------------------------------+
|      acl_group_permissions     |
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |     1    |        1       |
|  2 |     1    |        2       |
|  3 |     1    |        3       |
|  4 |     1    |        4       |
|  5 |     2    |        4       |
|  6 |     2    |        5       |
+----+----------+----------------+

Eu tenho uma consulta para mostrar todas as permissões permitidas atualmente por group_id '1'.

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1

O resultado é:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
+----+-------------+----------------+

MEU PROBLEMA

Eu quero que uma consulta mostre todas as permissões que não são permitidas pelo group_id '1'.

O resultado desejado seria:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

O mais perto que eu tenho é:

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
LEFT OUTER JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id IS NULL

Com o resultado sendo:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

Como eu alimentei o group_id de '1' na consulta para eliminar o id 4.

Já pesquisei bastante sobre o Google, mas estou encontrando exemplos e soluções que estão próximos, mas precisos para minha situação.

IE: usando uma tabela de junção. Meus MySQL Join Types infográficos também não ajudam.

Ajuda ..! Meu cérebro está derretendo ..!

    

Answer 1

Não use uma lógica de associação. Em vez disso, use uma lógica mais simples:

Selecione todas as permissões que o group_1 tem (de acl_group_permissions) e, em seguida, selecione todas as id de acl_permissions que não estejam nas selecionadas. Traduzido para SQL, torna-se:

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
WHERE acl_permissions.id NOT 
IN (
    SELECT permissions_id
    FROM acl_group_permissions
    WHERE group_id = 1
)

    

Answer 2

Não é o melhor, mas isso funcionaria

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id != 1 and acl_permissions.id 
NOT IN (SELECT DISTINCT acl_permissions.id
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1)

    

Answer 3

CONSULTA PROPOSTA

SELECT A.*
FROM acl_permissions A LEFT JOIN
(SELECT * FROM acl_group_permissions WHERE group_id=1) B
ON A.id = B.permissions_id WHERE B.id IS NULL;

SEUS DADOS DE AMOSTRA

DROP DATABASE IF EXISTS matt;
CREATE DATABASE matt;
USE matt
CREATE TABLE acl_permissions
(
    id int not null auto_increment,
    name varchar(32),
    permission varchar(32),
    primary key (id)
);
CREATE TABLE acl_group_permissions
(
    id int not null auto_increment,
    group_id int not null,
    permissions_id int not null,
    primary key (id)
);
INSERT INTO acl_permissions
(name,permission) VALUES
('Add User'   ,'addUser'),
('Edit User'  ,'editUser'),
('Delete User','deleteUser'),
('View User'  ,'viewUser'),
('Test Name'  ,'testPermission');
INSERT INTO acl_group_permissions
(group_id,permissions_id) VALUES
(1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
SELECT * FROM acl_permissions;
SELECT * FROM acl_group_permissions;

SEUS DADOS DE AMOSTRAGEM CARREGADOS

mysql> DROP DATABASE IF EXISTS matt;
Query OK, 2 rows affected (0.39 sec)

mysql> CREATE DATABASE matt;
Query OK, 1 row affected (0.00 sec)

mysql> USE matt
Database changed
mysql> CREATE TABLE acl_permissions
    -> (
    ->     id int not null auto_increment,
    ->     name varchar(32),
    ->     permission varchar(32),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE acl_group_permissions
    -> (
    ->     id int not null auto_increment,
    ->     group_id int not null,
    ->     permissions_id int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO acl_permissions
    -> (name,permission) VALUES
    -> ('Add User'   ,'addUser'),
    -> ('Edit User'  ,'editUser'),
    -> ('Delete User','deleteUser'),
    -> ('View User'  ,'viewUser'),
    -> ('Test Name'  ,'testPermission');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO acl_group_permissions
    -> (group_id,permissions_id) VALUES
    -> (1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM acl_permissions;
+----+-------------+----------------+
| id | name        | permission     |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM acl_group_permissions;
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |        1 |              1 |
|  2 |        1 |              2 |
|  3 |        1 |              3 |
|  4 |        1 |              4 |
|  5 |        2 |              4 |
|  6 |        2 |              5 |
+----+----------+----------------+
6 rows in set (0.00 sec)

mysql>

CONSULTA PROPOSTA EXECUTADA

mysql> SELECT A.*
    -> FROM acl_permissions A LEFT JOIN
    -> (SELECT * FROM acl_group_permissions WHERE group_id=1) B
    -> ON A.id = B.permissions_id WHERE B.id IS NULL;
+----+-----------+----------------+
| id | name      | permission     |
+----+-----------+----------------+
|  5 | Test Name | testPermission |
+----+-----------+----------------+
1 row in set (0.00 sec)

mysql>

DÊ UMA EXPERIÊNCIA !!!