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.
Best Answer
That is not surprising.
If, for example, one
feeds
row has 20feeds_reads
, the result will contain at least 20 result rows with the samefeeds
.If you want 20
feeds
and all their related data, you could useNote that
LIMIT
withoutORDER BY
is ralely useful.