CODEWITHSUNDEEP
sqlpostgresql
RocketGuy3
RocketGuy3

Reputation: 625

How can I check whether a specific integer exists in an array column I'm selecting?

I'm trying to run a query that looks something like this:

SELECT matches.*, array_agg(match_users.user_id)::int[] AS user_ids FROM "matches"
  INNER JOIN "match_users" ON "match_users"."match_id" = "matches"."id"
  WHERE (1 = ANY(user_ids))
GROUP BY matches.id

The matches have a many-to-many relationship with users (through the match_users join table), and I'm basically trying to find all the matches that a given user belongs to (in this case, the user with the ID of 1).

But I keep getting this error:

ERROR:  column "user_ids" does not exist
LINE 15:     AND (1 = ANY(user_ids))
                      ^

********** Error **********

ERROR: column "user_ids" does not exist

I'm sure there's something simple I'm missing here... Do I need to do something differently with my alias?

EDIT: Could I fix it with a sub select? The following seems to work, although I'm not sure it's doing exactly what I want without a lot more dummy data and testing:

SELECT matches.*, count(match_users.id) AS num_users, array_agg(match_users.user_id)::int[] AS user_ids FROM "matches"
  INNER JOIN "match_users" ON "match_users"."match_id" = "matches"."id"
  WHERE (1 = ANY(SELECT id FROM users WHERE users.id = match_users.user_id))
GROUP BY matches.id

I'm also not sure it will be very performant... it seems like this would be an awful lot of sub-selects if there were enough rows.

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use a HAVING clause:

SELECT m.*, array_agg(mu.user_id)::int[] AS user_ids
FROM "matches" m INNER JOIN
     "match_users" mu
     ON mu."match_id" = m."id"
GROUP BY m.id
HAVING MAX(CASE WHEN mu.user_id = 1 THEN 1 ELSE 0 END) > 0;

In other words, you don't have to look in the array for the check. You can just look at the returned value.

Upvotes: 1

Related Questions