限制和偏移复杂查询

Question

我有一个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次。

Answer 1

这并不令人惊讶。

例如，如果一个feeds行有20个feeds_reads，则结果将包含至少20个具有相同feeds的结果行。

如果您想要20个feeds及其所有相关数据，您可以使用

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

请注意，没有LIMIT的ORDER BY非常有用。