Use an array coming from a "with" statement

user_1098303 0
Viewed 32

I have ids I can gather with this request:

SELECT array_agg(DISTINCT mc.id) as liste_ids
FROM mots_cles mc
WHERE mc.mot ILIKE '%Test%'

I'd like to request another table with a column having an intersection with this array:

SELECT
  a.titre,
  a.id
FROM articles a
WHERE a.motscles && [THE ARRAY]

I tried to write a request with this structure:

WITH xxx AS (
    SELECT array_agg(DISTINCT mc.id) as liste_ids
    FROM mots_cles mc
    WHERE mc.mot ILIKE '%Test%'
)
    SELECT
      a.titre,
      a.id
    FROM articles a
    WHERE a.motscles && xxx.liste_ids::integer[]

But as you may have guessed, it doesn't work. How could I write my request, I have the sensation I'm not very far from the result.

Thanks in advance.

postgresql arrays sql
1 Answers

You can use join:

WITH xxx AS (
    SELECT array_agg(DISTINCT mc.id) as liste_ids
    FROM mots_cles mc
    WHERE mc.mot ILIKE '%Test%'
)
SELECT a.titre, a.id
FROM articles a JOIN
     xxx
     ON a.motscles && xxx.liste_ids::integer[];

Or EXISTS:

SELECT a.titre, a.id
FROM articles a 
EXISTS (SELECT 1
        FROM xxx
        WHERE a.motscles && xxx.liste_ids::integer[]
       );
user_11440350
Related