Outro dia me pediram para construir uma consulta SQL para carregar todos os usuários que possuem todas as tags fornecidas.
Digamos que temos as seguintes tabelas:
users: email
tags: id, name
user_tags: email, tag_id
Depois de pensar sobre isso no SQL, comecei a pesquisar funções de agregação, agrupar por, ter e todas essas coisas, até que finalmente encontrei a vela na caverna no PostgreSQL. Aqui está a resposta:
select users.email, array_agg(tags.name)
from users
join user_tags on users.email = user_tags.email
join tags on tags.id = user_tags.tag_id
group by users.email
having '{1, 2}'::int[] <@ array_agg(tags.id);
Aqui está o resultado:
email | array_agg
--------------+-----------------
a@sample.com | {rails,ruby}
b@sample.com | {rails,js,ruby}
(2 rows)
Veja o <@
método. Esse é o truque aqui. Primeiro, ele agrupa todas as tags e, em seguida, filtra as linhas onde está o array [1, 2] is contained
pelo agrupamento. Existe outra grande função que é o caminho de volta @>
: o primeiro parâmetro contém o segundo parâmetro.
Felicidades!!!