Batch Paperdex lookups to avoid N+1 queries in player/card list endpoints #17

New Issue

cal · 2026-02-20T06:52:39Z

cal commented

2026-02-20 06:52:39 +00:00

`app/routers_v2/players.py:311-314`, `490-495`, and `cards.py:121-124` — For every player/card, a separate `Paperdex.select().where()` is issued. 500 players = 500 extra queries.

Priority: high

\`app/routers_v2/players.py:311-314\`, \`490-495\`, and \`cards.py:121-124\` — For every player/card, a separate \`Paperdex.select().where()\` is issued. 500 players = 500 extra queries. **Priority**: high

cal added the

performance

label 2026-02-20 06:52:39 +00:00

cal added the

ai-working

label 2026-03-04 03:31:00 +00:00

cal referenced this issue from a commit

2026-03-04 03:35:54 +00:00

fix: batch Paperdex lookups to avoid N+1 queries (#17)

cal referenced a pull request that will close this issue

2026-03-04 03:36:11 +00:00

fix: batch Paperdex lookups to avoid N+1 queries (#17) #53

cal added the

ai-pr-opened

label 2026-03-04 03:36:29 +00:00

cal commented

2026-03-04 03:36:31 +00:00

PR opened: #53

Fix approach: Before each result loop, collect all player IDs and issue a single Paperdex.select().where(Paperdex.player_id << player_ids) query. Results are grouped into a dict[player_id → list[row]] and looked up in O(1) per player instead of issuing a query per player.

Three locations fixed: players.py (get_players with inc_dex flag), players.py (players-by-team endpoint), and cards.py (card list endpoint). The cards endpoint also avoids a duplicate count() query by materializing the queryset once with list().

Note: the original cards.py code passed a Card instance to Paperdex.player == (a FK to Player), which would have matched by Card ID rather than player ID — likely a pre-existing bug. The batched version correctly uses x.player_id.

PR opened: https://git.manticorum.com/cal/paper-dynasty-database/pulls/53 **Fix approach:** Before each result loop, collect all player IDs and issue a single `Paperdex.select().where(Paperdex.player_id << player_ids)` query. Results are grouped into a `dict[player_id → list[row]]` and looked up in O(1) per player instead of issuing a query per player. Three locations fixed: `players.py` (get_players with `inc_dex` flag), `players.py` (players-by-team endpoint), and `cards.py` (card list endpoint). The cards endpoint also avoids a duplicate `count()` query by materializing the queryset once with `list()`. Note: the original `cards.py` code passed a `Card` instance to `Paperdex.player ==` (a FK to `Player`), which would have matched by Card ID rather than player ID — likely a pre-existing bug. The batched version correctly uses `x.player_id`.

cal removed the

ai-working

label 2026-03-04 03:36:55 +00:00

cal referenced this issue from a commit

2026-03-05 21:33:45 +00:00

fix: batch Paperdex lookups to avoid N+1 queries (#17)

cal referenced this issue from a commit

2026-03-07 07:37:11 +00:00

fix: batch Paperdex lookups to avoid N+1 queries (#17)

cal closed this issue

2026-03-16 16:09:57 +00:00

cal referenced this issue from a commit