LIMIT 和 OFFSET 一個複雜的查詢

我有一個由 ORM（Sequelize）生成的查詢，我遇到了 sequelize 失敗的問題，請查看這些問題。 https://github.com/sequelize/sequelize/issues/7344

https://github.com/sequelize/sequelize/issues/12200

Postgres 查詢：

SELECT "feeds"."id",
   "feeds"."title",
   "feeds"."likes",
   "feeds"."description",
   "feeds"."files",
   "feeds"."allowComments",
   "feeds"."readConfirmation",
   "feeds"."isDraft",
   "feeds"."createdAt",
   "feeds"."updatedAt",
   "feeds"."companyId",
   "feeds"."createdById",
   "reads"."id" AS "reads.id",
   "reads->feeds_reads"."createdAt" AS "reads.feeds_reads.createdAt",
   "reads->feeds_reads"."updatedAt" AS "reads.feeds_reads.updatedAt",
   "reads->feeds_reads"."feedId" AS "reads.feeds_reads.feedId",
   "reads->feeds_reads"."userId" AS "reads.feeds_reads.userId",
   "createdBy"."id" AS "createdBy.id",
   "createdBy"."firstName" AS "createdBy.firstName",
   "createdBy"."jobTitle" AS "createdBy.jobTitle",
   "createdBy"."lastName" AS "createdBy.lastName",
   "createdBy"."profilePicture" AS "createdBy.profilePicture",
   "bookmarks"."id" AS "bookmarks.id",
   "bookmarks->feeds_bookmarks"."createdAt" AS "bookmarks.feeds_bookmarks.createdAt",
   "bookmarks->feeds_bookmarks"."updatedAt" AS "bookmarks.feeds_bookmarks.updatedAt",
   "bookmarks->feeds_bookmarks"."feedId" AS "bookmarks.feeds_bookmarks.feedId",
   "bookmarks->feeds_bookmarks"."userId" AS "bookmarks.feeds_bookmarks.userId",
   "units"."id" AS "units.id",
   "units"."parentId" AS "units.parentId",
   "units->feeds_units"."createdAt" AS "units.feeds_units.createdAt",
   "units->feeds_units"."updatedAt" AS "units.feeds_units.updatedAt",
   "units->feeds_units"."feedId" AS "units.feeds_units.feedId",
   "units->feeds_units"."unitId" AS "units.feeds_units.unitId",
   "units->users"."id" AS "units.users.id",
   "units->users->users_units"."createdAt" AS "units.users.users_units.createdAt",
   "units->users->users_units"."updatedAt" AS "units.users.users_units.updatedAt",
   "units->users->users_units"."userId" AS "units.users.users_units.userId",
   "units->users->users_units"."unitId" AS "units.users.users_units.unitId",
   "units->descendents"."id" AS "units.descendents.id",
   "units->descendents"."parentId" AS "units.descendents.parentId",
   "units->descendents->unitsancestor"."unitsId" AS "units.descendents.unitsancestor.unitsId",
   "units->descendents->unitsancestor"."ancestorId" AS "units.descendents.unitsancestor.ancestorId",
   "units->descendents->users"."id" AS "units.descendents.users.id",
   "units->descendents->users->users_units"."createdAt" AS "units.descendents.users.users_units.createdAt",
   "units->descendents->users->users_units"."updatedAt" AS "units.descendents.users.users_units.updatedAt",
   "units->descendents->users->users_units"."userId" AS "units.descendents.users.users_units.userId",
   "units->descendents->users->users_units"."unitId" AS "units.descendents.users.users_units.unitId",
   "teams"."id" AS "teams.id",
   "teams->feeds_teams"."createdAt" AS "teams.feeds_teams.createdAt",
   "teams->feeds_teams"."updatedAt" AS "teams.feeds_teams.updatedAt",
   "teams->feeds_teams"."feedId" AS "teams.feeds_teams.feedId",
   "teams->feeds_teams"."teamId" AS "teams.feeds_teams.teamId",
   "teams->peoples->teams_users"."createdAt" AS "teams.peoples.teams_users.createdAt",
   "teams->peoples->teams_users"."updatedAt" AS "teams.peoples.teams_users.updatedAt",
   "teams->peoples->teams_users"."userId" AS "teams.peoples.teams_users.userId",
   "teams->peoples->teams_users"."teamId" AS "teams.peoples.teams_users.teamId",
   "comments"."text" AS "comments.text",
   "comments"."id" AS "comments.id",
   "comments"."likes" AS "comments.likes",
   "comments"."parentId" AS "comments.parentId",
   "comments"."createdById" AS "comments.createdById",
   "comments"."createdAt" AS "comments.createdAt",
   "comments"."updatedAt" AS "comments.updatedAt",
   "comments->createdBy"."id" AS "comments.createdBy.id",
   "comments->createdBy"."firstName" AS "comments.createdBy.firstName",
   "comments->createdBy"."lastName" AS "comments.createdBy.lastName",
   "comments->createdBy"."jobTitle" AS "comments.createdBy.jobTitle",
   "comments->createdBy"."profilePicture" AS "comments.createdBy.profilePicture",
   "peoples->feeds_peoples"."createdAt" AS "peoples.feeds_peoples.createdAt",
   "peoples->feeds_peoples"."updatedAt" AS "peoples.feeds_peoples.updatedAt",
   "peoples->feeds_peoples"."feedId" AS "peoples.feeds_peoples.feedId",
   "peoples->feeds_peoples"."userId" AS "peoples.feeds_peoples.userId"
FROM "feeds" AS "feeds"
   LEFT OUTER JOIN (
       "feeds_reads" AS "reads->feeds_reads"
       INNER JOIN "users" AS "reads" ON "reads"."id" = "reads->feeds_reads"."userId"
   ) ON "feeds"."id" = "reads->feeds_reads"."feedId"
   LEFT OUTER JOIN "users" AS "createdBy" ON "feeds"."createdById" = "createdBy"."id"
   LEFT OUTER JOIN (
       "feeds_bookmarks" AS "bookmarks->feeds_bookmarks"
       INNER JOIN "users" AS "bookmarks" ON "bookmarks"."id" = "bookmarks->feeds_bookmarks"."userId"
   ) ON "feeds"."id" = "bookmarks->feeds_bookmarks"."feedId"
   LEFT OUTER JOIN (
       "feeds_units" AS "units->feeds_units"
       INNER JOIN "units" AS "units" ON "units"."id" = "units->feeds_units"."unitId"
   ) ON "feeds"."id" = "units->feeds_units"."feedId"
   LEFT OUTER JOIN (
       "users_units" AS "units->users->users_units"
      LEFT OUTER JOIN "users" AS "units->users" ON "units->users"."id" = "units->users->users_units"."userId"
   ) ON "units"."id" = "units->users->users_units"."unitId"
   LEFT OUTER JOIN (
       "unitsancestor" AS "units->descendents->unitsancestor"
       LEFT OUTER JOIN "units" AS "units->descendents" ON "units->descendents"."id" = "units->descendents->unitsancestor"."unitsId"
   ) ON "units"."id" = "units->descendents->unitsancestor"."ancestorId"
   LEFT OUTER JOIN (
       "users_units" AS "units->descendents->users->users_units"
       LEFT OUTER JOIN "users" AS "units->descendents->users" ON "units->descendents->users"."id" = "units->descendents->users->users_units"."userId"
   ) ON "units->descendents"."id" = "units->descendents->users->users_units"."unitId"
   LEFT OUTER JOIN (
       "feeds_teams" AS "teams->feeds_teams"
       INNER JOIN "teams" AS "teams" ON "teams"."id" = "teams->feeds_teams"."teamId"
   ) ON "feeds"."id" = "teams->feeds_teams"."feedId"
   LEFT OUTER JOIN (
       "teams_users" AS "teams->peoples->teams_users"
       INNER JOIN "users" AS "teams->peoples" ON "teams->peoples"."id" = "teams->peoples->teams_users"."userId"
   ) ON "teams"."id" = "teams->peoples->teams_users"."teamId"
   LEFT OUTER JOIN "comments" AS "comments" ON "feeds"."id" = "comments"."feedId"
   LEFT OUTER JOIN "users" AS "comments->createdBy" ON "comments"."createdById" = "comments->createdBy"."id"
   LEFT OUTER JOIN (
       "feeds_peoples" AS "peoples->feeds_peoples"
       INNER JOIN "users" AS "peoples" ON "peoples"."id" = "peoples->feeds_peoples"."userId"
   ) ON "feeds"."id" = "peoples->feeds_peoples"."feedId"
WHERE (
       "peoples"."id" = 11
       OR "feeds"."createdById" = 11
       OR "teams->peoples"."id" = 11
       OR "units->users"."id" = 11
       OR "units->descendents->users"."id" = 11
   )
   AND "feeds"."companyId" = 4
   AND "feeds"."isDraft" = false
   AND "feeds"."createdAt" < '2020-12-09 12:59:34.017 +00:00'
LIMIT 20;

這裡的限制不適用於提要，我想要 20 個提要，但它給了我 20 次相同的提要。

這並不奇怪。

例如，如果feeds一行有 20 feeds_reads，則結果將包含至少 20 個具有相同 的結果行feeds。

如果您想要 20feeds及其所有相關數據，您可以使用

FROM (SELECT * FROM "feeds" LIMIT 20) AS "feeds"

請注意，LIMIT沒有ORDER BY非常有用。

引用自：https://dba.stackexchange.com/questions/281256