0

My question is similar to possible reference jsonb_array_length in a where clause, however I cannot use ORDER BY.

I have following tables:

CREATE TABLE IF NOT EXISTS games
(
  id SERIAL PRIMARY KEY,
  ...
)
CREATE TABLE IF NOT EXISTS users
(
  id SERIAL PRIMARY KEY,
  username CHARACTER VARYING(100) NOT NULL UNIQUE,
  ...
)
CREATE TABLE IF NOT EXISTS game_users
(
    game_id INTEGER REFERENCES games (id) NOT NULL,
    user_id INTEGER REFERENCES users (id) NOT NULL,
    joined_at TIMESTAMPTZ NOT NULL,
    UNIQUE (game_id, user_id)
);

I want to query games that have less than 4 participants.

SELECT gm.*,
  array_agg(usr.id) AS users_id,
  array_agg(gmUsr.joined_at) AS users_joined,
  array_agg(usr.username) AS users_username
FROM games AS gm
  LEFT JOIN game_users AS gmUsr ON gmUsr.game_id = gm.id
  LEFT JOIN users AS usr ON gmUsr.user_id = usr.id
WHERE cardinality(array_length(users_id)) < 4
GROUP BY gm.id

I already saw in an answer that the WHERE clause is executed before the SELECT one. So my query will not work. However I want to keep GROUP BY user id because I want aggregated data as:

type GamesJoinedUsers = {
  id: number,
  users_id: number[],
  users_joined: Date[],
  users_username: string[],
}

How to query such data at least somehow?

Is it possible to do it in one query without subquery?

5
  • 1
    You need to use having to apply filters on the result of aggregate functions
    – user1822
    Feb 20, 2023 at 14:37
  • HAVING also evaluated before the SELECT clause postgresqltutorial.com/postgresql-tutorial/postgresql-having
    – Alexey Vol
    Feb 20, 2023 at 15:27
  • That is not true. Try it! And rely on the real documentation. Feb 20, 2023 at 17:02
  • 1
    @LaurenzAlbe It most definitely is true: HAVING is evaluated before SELECT, this is part of the SQL standard. Here's a simple proof dbfiddle.uk/ZsF7NFLp Feb 20, 2023 at 22:52
  • @Charlieface You are right, I should have been more careful. Still, HAVING is the solution to the original question. Feb 21, 2023 at 7:16

1 Answer 1

2

It seems to me you just need to replace your WHERE clause with a HAVING clause like this:

HAVING
  COUNT(isr.id) < 4

The HAVING clause is the appropriate place for a filter that needs to apply to an aggregated result. In this case, you want a filter on the number of users per game, which is an aggregate result given your GROUP BY. Therefore, use HAVING.

1
  • Thanks! This works. Using HAVING with cardinality(array_length(users_id)) still does not work. However the trick with COUNT(usr.id) is more elegant in this case!
    – Alexey Vol
    Feb 21, 2023 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.