0
I need to make a query that looks for the type of visibility that the user has, and then search for all the other users that he "sees". The user can see all the users of the company, himself and one more specific, or all of the team to which he belongs.
WITH Q1 AS ( SELECT
(CASE
WHEN v.is_completo > 0 THEN ( SELECT GROUP_CONCAT(u.id SEPARATOR ',') FROM usuarios u INNER JOIN usuarios u2 ON u.empresa_id = u2.empresa_id WHERE u2.id = v.usuario_id)
WHEN v.usuario_alvo_id > 0 THEN ( SELECT GROUP_CONCAT(u.id SEPARATOR ',') FROM usuarios u WHERE id IN (v.usuario_alvo_id, v.usuario_id))
WHEN v.equipe_id > 0 THEN ( SELECT GROUP_CONCAT(eq.usuario_id SEPARATOR ',') FROM usuarios_equipes eq where eq.equipe_id = v.equipe_id )
END) AS id
FROM visibilidades v
WHERE v.usuario_id = 440 )
SELECT u.id,u.nome FROM usuarios u where u.id IN (SELECT id FROM q1);
For that I used the CASE WHEN
in conjunction with subquerys to already try to fetch all users that it has visibility. The problem that these subquerys mostly return more than one ID
and so I needed to concatenate.
Q1 = [440,431,441,442]
. However, when trying to use the resubmission of the next query returns only the user of the first position (440).
I wonder if it is possible to use this arrays of ID
s in the clause where IN
or if there is another way to achieve the result I seek.
But the GROUP_CONCAT function does not return a string?
– anonimo
Yes. But I don’t know what other approach I could take in this case
– Lucas Ferreira
Well, if it’s a string you can’t use
IN
, maybe some of the string functions available in the DBMS you are working with.– anonimo