1

I have a query generated by a ORM (Sequelize), i ran into an issue where sequelize fails, look at those issues. https://github.com/sequelize/sequelize/issues/7344

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

Postgres Query:


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;

Here the limit is not applying to the feeds, i want 20 feeds but it's giving me same feed 20 times.

2
  • Do you really need 10 columns labeled CreatedAt?
    – Rick James
    Dec 10, 2020 at 20:03
  • Your giant set of JOINs will get lots of dups. Unless there is a GROUP BY. But that may provide new questions.
    – Rick James
    Dec 10, 2020 at 20:04

1 Answer 1

0

That is not surprising.

If, for example, one feeds row has 20 feeds_reads, the result will contain at least 20 result rows with the same feeds.

If you want 20 feeds and all their related data, you could use

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

Note that LIMIT without ORDER BY is ralely useful.

4
  • Thanks this worked. but it only gives me half of result. Setting LIMIT 20 gives only 10 records on LIMIT 10 it gives 4 records Dec 10, 2020 at 7:48
  • You have to remove the final LIMIT 20 of course. And the WHERE condition could remove some result rows. Dec 10, 2020 at 7:54
  • FROM (SELECT * FROM "feeds" WHERE ("feeds"."companyId" = 4 AND "feeds"."isDraft" = false AND "feeds"."createdAt" < '2020-12-09 12:59:34.017 +00:00') OR "feeds"."createdById" = 11 LIMIT 20 OFFSET 0 ) AS "feeds" where to put this OR this causing issue. Dec 10, 2020 at 7:57
  • I cannot give you a training session in SQL here... Dec 10, 2020 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.