Problem summary
GET /api/feed/get currently issues 6 sequential queries per request (3 data + 3 likes), fetches up to 3× the requested item count and then merges/sorts in memory, and uses per‑type skip/take pagination so ordering across pages is not globally correct. The result is that page 2+ can skip items, repeat ranges, and get slower as skip grows.
Approach
Replace the three independent Prisma queries with a single raw SQL UNION that returns a globally sorted, cursor‑paginated list of feed IDs and types. Then, for just those IDs, batch‑fetch the full records and likes by type in parallel, reconstruct the feed in the same order as the UNION result, and expose a cursor (nextCursor) instead of relying on skip. This turns pagination into a stable (sort_date, id) cursor, reduces query fan‑out, and ensures we only fetch exactly take items per page.
Implementation steps
Step 1 — Backend helper
Add getFeedPage() in src/services/feedService.ts that:
-
Runs one UNION ALL over Submission, PoW, and GrantApplication with a shared (sort_date, id) cursor and ORDER BY sort_date DESC, id DESC.
-
Accepts existing filters (startDate/endDate, profileUserId, isWinner, filter, userId, privacy, takeOnlyType) and an optional highlightId.
-
Uses prisma.$queryRaw tagged templates for parameterized SQL.
Step 2 — API handler
In src/pages/api/feed/get.ts:
-
Replace the three data queries with a call to getFeedPage().
-
Split IDs by type, fetch full records and likes in parallel with findMany / likes.findMany.
-
Reconstruct the feed in the UNION order and return { data, nextCursor }.
-
Keep skip for backward compatibility (mapped to an OFFSET‑based path).
Step 3 — Frontend hook
In src/features/feed/queries/useGetFeed.ts:
-
Move from skip to cursor pagination (pageParam: cursor, getNextPageParam: lastPage.nextCursor).
-
Read items from the new { data, nextCursor } shape.
Step 4 — Highlight behaviour
Let the UNION include and prioritize the highlighted item when highlightId is present, and remove the extra findFirst / findUnique highlight queries and in‑memory .find() scan.
Problem summary
GET /api/feed/getcurrently issues 6 sequential queries per request (3 data + 3 likes), fetches up to 3× the requested item count and then merges/sorts in memory, and uses per‑type skip/takepagination so ordering across pages is not globally correct. The result is that page 2+ can skip items, repeat ranges, and get slower as skipgrows.Approach
Replace the three independent Prisma queries with a single raw SQL
UNIONthat returns a globally sorted, cursor‑paginated list of feed IDs and types. Then, for just those IDs, batch‑fetch the full records and likes by type in parallel, reconstruct the feed in the same order as the UNIONresult, and expose a cursor (nextCursor) instead of relying on skip. This turns pagination into a stable (sort_date, id)cursor, reduces query fan‑out, and ensures we only fetch exactly takeitems per page.Implementation steps
Step 1 — Backend helper
Add
getFeedPage()in src/services/feedService.tsthat:Runs one
UNION ALLover Submission, PoW, and GrantApplicationwith a shared (sort_date, id)cursor and ORDER BY sort_date DESC, id DESC.Accepts existing filters (
startDate/endDate, profileUserId, isWinner, filter, userId, privacy, takeOnlyType) and an optional highlightId.Uses
prisma.$queryRawtagged templates for parameterized SQL.Step 2 — API handler
In
src/pages/api/feed/get.ts:Replace the three data queries with a call to
getFeedPage().Split IDs by
type, fetch full records and likes in parallel with findMany/ likes.findMany.Reconstruct the feed in the
UNIONorder and return { data, nextCursor }.Keep
skipfor backward compatibility (mapped to an OFFSET‑based path).Step 3 — Frontend hook
In
src/features/feed/queries/useGetFeed.ts:Move from
skipto cursor pagination (pageParam: cursor, getNextPageParam: lastPage.nextCursor).Read items from the new
{ data, nextCursor }shape.Step 4 — Highlight behaviour
Let the
UNIONinclude and prioritize the highlighted item when highlightIdis present, and remove the extra findFirst/ findUniquehighlight queries and in‑memory .find()scan.