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?
Best Answer
It seems to me you just need to replace your
WHERE
clause with aHAVING
clause like this: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 yourGROUP BY
. Therefore, useHAVING
.