If you don't know yet, $facet in MongoDB allows you to run fork query into separate pipelines.
You can do this in PostgreSQL as well, using CTE and JSON functions.
SELECT
(
SELECT json_agg(row_to_json)
FROM (
SELECT row_to_json(t)
FROM (
SELECT * FROM match_cte
LIMIT ${limit} OFFSET ${(page - 1) * limit}
) t
) t1
) result,
(
SELECT COUNT(*) FROM match_cte
) "count"
I think this is especially more important for expensive queries that you cannot fully index somehow...
Top comments (0)