Is it possible to use the return of a query with GROUP_CONCAT in the WHERE clause?

Asked

Viewed 43 times

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 IDs in the clause where IN or if there is another way to achieve the result I seek.

mysql sql

  • But the GROUP_CONCAT function does not return a string?

  • Yes. But I don’t know what other approach I could take in this case

  • 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.

1 answer

1


Lucas, I think it would be more common to return users' Ids in separate lines in CTE to then merge CTE with the user table, but considering your example I believe that CTE is not necessary because the user table is already used in the initial queries. Here is a suggestion for testing:

SELECT u.id, u.nome
FROM visibilidades v
INNER JOIN usuarios u2 ON u2.id = v.usuario_id 
INNER JOIN usuarios u ON u.empresa_id = u2.empresa_id 
WHERE v.usuario_id = 440 AND v.is_completo > 0 

UNION ALL

SELECT u.id, u.nome
FROM visibilidades v
INNER JOIN usuarios u ON u.id = v.usuario_alvo_id OR u.id = v.usuario_id
WHERE v.usuario_id = 440 AND v.usuario_alvo_id > 0

UNION ALL

SELECT eq.usuario_id, u.nome
FROM visibilidades v
INNER JOIN usuarios_equipes eq ON eq.equipe_id = v.equipe_id
INNER JOIN usuarios u ON u.id = eq.usuario_id 
WHERE v.usuario_id = 440 AND v.equipe_id > 0

I hope it helps

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.