Help with SQL query

Question

Short database schema:

• users (id)
• games (current_player_id) // has many cards
• cards (author_id, game_id, content, created_at)
• game_views(game_id, user_id) //shows which games user have seen

I need to find game for user, which meets all whose rules:

1. game's current_player is NULL (the game do not played by anybody right now)
2. author of the last card in game(order by created_at, I think) is not user
3. game have not been seen by user

I use PostgreSQL.

Answer 1

I'm writing this code with SQL Server in mind, but it should work in Postgres. If it doesn't, the differences should be minimal afaik.

This solution should work (I don't have Postgres installed here), but you may want to optimize it for large datasets; use indexes, statistics, etc... (the usual).

SELECT DISTINCT games.game_id FROM games INNER JOIN cards ON games.game_id = cards.game_id  WHERE games.current_player_id is NULL and games.game_id in (SELECT DISTINCT game_id FROM GAME_VIEWS WHERE user_id != 666) and cards.author_id != 666 GROUP BY games.game_id ORDER BY cards.created_at DESC

Again, swap "666" with the actual id. Hope it helps!

Answer 2

I think this is the most clear solution. With proper indexes, performance should be reasonable but its not going to scale well if the number of games gets into the millions. If that happens, one way to increase performance would be to store the last card_id at the game level.

select game_id
FROM game g
WHERE g.current_player is null
AND not exists (select 0 from game_view gv 
             where gv.user_id = v_my_user_id and gv.game_id = g.game_id)
AND not exists (select 0 from 
    (select author_id from cards c where c.game_id = g.game_id order 
    by created_at LIMIT 1) 
    t1 where t1.author_id =  v_my_user_id)