I have the following SQL query where I join two tables (entity_type
and shipment_entity_type
) and aggregate the data. The query is designed to compare the number of items in the entity_type
table with the total items shipped from the shipment_entity_type
table.
Here’s the query:
SELECT
t1."entityId",
t1."typeId",
t1."createdAt",
t1."itemCount" AS entity_type_items,
t2.total_items_shipped AS shipped_entity_type_items
FROM
public.entity_type t1
JOIN
(SELECT
"entityId",
"typeId",
SUM("itemsShipped") AS total_items_shipped
FROM
public.shipment_entity_type
WHERE
"deletedAt" IS NULL
GROUP BY
"entityId", "typeId") t2 ON t1."entityId" = t2."entityId"
AND t1."typeId" = t2."typeId"
WHERE
t1."itemCount" <> t2.total_items_shipped;
Now, I would like to add pagination to this query so that I can limit the results and fetch them in pages (e.g., 10 results per page). How can I achieve this?
I’m using PostgreSQL, and I want to handle pagination with LIMIT and OFFSET. What is the best way to modify this query to support pagination?
The problem is I am getting duplicate entityId
so If I want to show it on the frontend I will see duplicate data with different typeId
.