count(players.id) and add a where clause that matches the user id...select
games.name AS game,
profiles.name AS organizer
from games
left join profiles on games.organizer_id = profiles.id
where games.organizer_id = 'f316de4e-e478-4ce3-842c-00e98f475870'
or games.organizer_id in (select friends.friend_id from friends where friends.user_id = 'f316de4e-e478-4ce3-842c-00e98f475870' and friends.accepted = true)
or games.id in (select players.game_id from players where players.user_id = 'f316de4e-e478-4ce3-842c-00e98f475870')
order by games.datetime asc
;count(players.id)where
T
G
Splayer names that are part of each game. The thing is, the names are in the profiles table, so I have to join profiles through players... somehow, given simplified schemae:SCREATE OR REPLACE VIEW v_open_games AS
SELECT
games.name AS game,
profiles.name AS organizer,
games.kind
FROM games
LEFT JOIN profiles on games.organizer_id = profiles.id
LEFT JOIN players on games.id = players.game_id
WHERE games.organizer_id = auth.uid()
OR games.organizer_id IN (SELECT friends.friend_id FROM friends WHERE friends.user_id = auth.uid() AND friends.accepted = true)
OR games.id IN (SELECT players.game_id FROM players where players.user_id = auth.uid())
ORDER BY games.datetime ASC;playergameprofilesprofilesplayerscreate table profiles (
id uuid primary key references auth.users on delete cascade,
name text
);
create table games (
id uuid primary key DEFAULT gen_random_uuid(),
organizer_id uuid not null references profiles(id) on delete cascade,
name text,
kind game_kinds not null default 'friends-open'
);
create table players (
id uuid primary key DEFAULT gen_random_uuid(),
user_id uuid not null references profiles(id) on delete cascade,
game_id uuid not null references games(id) on delete cascade,
note text,
UNIQUE(user_id,game_id)
);-- open games
DROP VIEW IF EXISTS v_open_games;
CREATE OR REPLACE VIEW v_open_games AS
SELECT g.id,
g.name AS game,
g.datetime,
pr.name AS organizer,
g.kind,
g.address,
g.geog,
g.note,
g.players_min,
g.players_max,
g.players_conf,
g.game_on
FROM games AS g
JOIN profiles AS pr ON (g.organizer_id = pr.id)
WHERE g.organizer_id = auth.uid()
OR g.organizer_id IN (
SELECT friend_id
FROM friends
WHERE user_id = auth.uid()
AND accepted
)
OR g.id IN (
SELECT game_id
FROM players
WHERE user_id = auth.uid()
)
GROUP BY 1, 2, 3, 4
ORDER BY g.datetime ASC;