Início / dba / Perguntas / 202254
Accepted
guettli
Asked: 2018-03-26 23:57:55 +0800 CST

Obter membros de um determinado grupo do relacionamento N:M

  • 772

Eu tenho essa relação N:M:

CREATE TABLE auth_user (
    id integer NOT NULL PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer NOT NULL PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer NOT NULL PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

Como selecionar todos os nomes de usuário que estão no grupo 'group1'?

Eu uso o PostgreSQL, mas o SQL que funciona em todos os lugares é o preferido.

postgresql join

5 respostas

  1. Best Answer

    Essa EXISTSconsulta retorna usuários únicos, independentemente de haver entradas duplicadas em auth_user_groups.

    SELECT *
FROM   auth_user u
WHERE  EXISTS (
   SELECT 1
   FROM   auth_user_groups
   WHERE  user_id = u.id
   AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
   );

    Normalmente rápido também.

    Notas

    Para Postgres - que você parece estar usando.

    A sequência de colunas de índice é importante. Você definiu UNIQUE(user_id, group_id), que é implementado com um índice exclusivo correspondente. Para sua consulta específica, um índice em (group_id, user_id)seria preferível.

    Você pode alternar as colunas da UNIQUErestrição ou criar um índice adicional (opcionalmente exclusivo) com as colunas invertidas se precisar de ambos (o que é o caso comum). Relacionado:

    Também esteja ciente de que as colunas incluídas em uma UNIQUErestrição ainda podem ser NULL. (Ao contrário de uma PRIMARY KEYrestrição que faz com que todas as colunas de membros sejam NOT NULLautomaticamente!) Você normalmente deseja user_ide também group_ido auth_user_groupsseja NOT NULL. Relacionado:

    • 8

  2. Algo assim:

    select *
from auth_user
where id in (select aug.user_id
             from auth_group ag
               join auth_user_groups aug on aug.group_id = ag.id
             where ag.name = 'group1');
    • 6

  3. A_horse_with_no_name postou uma boa resposta, na verdade essa era minha opção principal (merecia um upvote =))

    Você também pode fazer isso da maneira usual:

    select au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

    Mas observe que, se você tiver mais de um grupo com o mesmo nome e usuários em grupos homônimos, obterá linhas duplicadas:

    INSERT INTO auth_group VALUES (3, 'group1');
INSERT INTO auth_user_groups VALUES (1, 1, 3);

    Nesse caso, você deve usar um distinto:

    select distinct au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

    Observação A resposta A_Horse ainda funciona nesse caso.

    • 2

  4. A partir do SQL Server 2017 e do Azure SQL DB, você pode usar os novos recursos do banco de dados gráfico e a nova cláusula MATCH para responder a consultas como esta, por exemplo

    SELECT FORMATMESSAGE ( 'User %s (%i) is in group %s.', [user].userName, [user].userId, [group].groupName ) msg
FROM dbo.users [user], dbo.hasGroup hasGroup, dbo.groups [group]
WHERE [group].groupName = 'group1'
  AND MATCH ( [user]-(hasGroup)->[group] );

    Meus resultados:

    Meus resultados

    Roteiro completo disponível aqui .

    • 2 
  5. select username from auth_user, auth_user_groups, auth_group where 
  auth_group.name='group1' and 
  auth_user_groups.group_id=auth_group.id and
  auth_user.id=auth_user_groups.user_id;
    • 0

relate perguntas