perf: ⚡ improve performance of queries on HasMany relationships

[ghusse]

Remove useless fields of the parent model from the request

Linked to CU-4rg1z2

I cannot create tests on this modification, because the requested fields were never returned by the function, which is returning record[params.associationName], and never record[model.name].

Example of the generated query when requesting messages for one given user:

Before

SELECT
  "user"."id",
  "user"."email",
  "user"."firstName",
  "user"."lastName",
  "user"."username",
  "user"."password",
  "user"."phone",
  "user"."createdAt",
  "user"."updatedAt",
  "user"."resetPasswordToken",
  "user"."isSecure",
  "user"."browserSessionHash",
  "user"."deletedAt",
  "user"."guestId",
  "messages"."id" AS "messages.id",
  "messages"."content" AS "messages.content",
  "messages"."createdAt" AS "messages.createdAt",
  "messages"."updatedAt" AS "messages.updatedAt",
  "messages"."deletedAt" AS "messages.deletedAt",
  "messages"."authorId" AS "messages.authorId",
  "messages"."noteId" AS "messages.noteId",
  "messages->mention"."id" AS "messages.mention.id",
  "messages->mention"."createdAt" AS "messages.mention.createdAt",
  "messages->mention"."updatedAt" AS "messages.mention.updatedAt",
  "messages->mention"."messageId" AS "messages.mention.messageId",
  "messages->mention"."userId" AS "messages.mention.userId",
  "messages->author"."id" AS "messages.author.id",
  "messages->author"."email" AS "messages.author.email",
  "messages->author"."firstName" AS "messages.author.firstName",
  "messages->author"."lastName" AS "messages.author.lastName",
  "messages->author"."username" AS "messages.author.username",
  "messages->author"."password" AS "messages.author.password",
  "messages->author"."phone" AS "messages.author.phone",
  "messages->author"."createdAt" AS "messages.author.createdAt",
  "messages->author"."updatedAt" AS "messages.author.updatedAt",
  "messages->author"."resetPasswordToken" AS "messages.author.resetPasswordToken",
  "messages->author"."isSecure" AS "messages.author.isSecure",
  "messages->author"."browserSessionHash" AS "messages.author.browserSessionHash",
  "messages->author"."deletedAt" AS "messages.author.deletedAt",
  "messages->author"."guestId" AS "messages.author.guestId",
  "messages->note"."id" AS "messages.note.id",
  "messages->note"."title" AS "messages.note.title",
  "messages->note"."recordId" AS "messages.note.recordId",
  "messages->note"."description" AS "messages.note.description",
  "messages->note"."archivedAt" AS "messages.note.archivedAt",
  "messages->note"."lastMessageAt" AS "messages.note.lastMessageAt",
  "messages->note"."messagesCount" AS "messages.note.messagesCount",
  "messages->note"."collectionStringId" AS "messages.note.collectionId",
  "messages->note"."createdAt" AS "messages.note.createdAt",
  "messages->note"."updatedAt" AS "messages.note.updatedAt",
  "messages->note"."deletedAt" AS "messages.note.deletedAt",
  "messages->note"."teamId" AS "messages.note.teamId",
  "messages->note"."authorId" AS "messages.note.authorId"
FROM
  "users" AS "user"
  LEFT OUTER JOIN (
    "mentions" AS "messages->mention"
    INNER JOIN "messages" AS "messages" ON "messages"."id" = "messages->mention"."messageId"
  ) ON "user"."id" = "messages->mention"."userId"
  AND (
    "messages"."deletedAt" > '2020-07-31 06:34:58.024 +00:00'
    OR "messages"."deletedAt" IS NULL
  )
  LEFT OUTER JOIN "users" AS "messages->author" ON "messages"."authorId" = "messages->author"."id"
  AND (
    "messages->author"."deletedAt" > '2020-07-31 06:34:58.024 +00:00'
    OR "messages->author"."deletedAt" IS NULL
  )
  LEFT OUTER JOIN "notes" AS "messages->note" ON "messages"."noteId" = "messages->note"."id"
  AND (
    "messages->note"."deletedAt" > '2020-07-31 06:34:58.024 +00:00'
    OR "messages->note"."deletedAt" IS NULL
  )
WHERE
  (
    (
      "user"."deletedAt" > '2020-07-31 06:34:58.024 +00:00'
      OR "user"."deletedAt" IS NULL
    )
    AND "user"."id" = '5'
  )
ORDER BY
  "messages"."id" DESC
LIMIT
  15 OFFSET 0;

After

SELECT
  "user"."id",
  "messages"."id" AS "messages.id",
  "messages"."content" AS "messages.content",
  "messages"."createdAt" AS "messages.createdAt",
  "messages"."updatedAt" AS "messages.updatedAt",
  "messages"."deletedAt" AS "messages.deletedAt",
  "messages"."authorId" AS "messages.authorId",
  "messages"."noteId" AS "messages.noteId",
  "messages->mention"."id" AS "messages.mention.id",
  "messages->mention"."createdAt" AS "messages.mention.createdAt",
  "messages->mention"."updatedAt" AS "messages.mention.updatedAt",
  "messages->mention"."messageId" AS "messages.mention.messageId",
  "messages->mention"."userId" AS "messages.mention.userId",
  "messages->author"."id" AS "messages.author.id",
  "messages->author"."email" AS "messages.author.email",
  "messages->author"."firstName" AS "messages.author.firstName",
  "messages->author"."lastName" AS "messages.author.lastName",
  "messages->author"."username" AS "messages.author.username",
  "messages->author"."password" AS "messages.author.password",
  "messages->author"."phone" AS "messages.author.phone",
  "messages->author"."createdAt" AS "messages.author.createdAt",
  "messages->author"."updatedAt" AS "messages.author.updatedAt",
  "messages->author"."resetPasswordToken" AS "messages.author.resetPasswordToken",
  "messages->author"."isSecure" AS "messages.author.isSecure",
  "messages->author"."browserSessionHash" AS "messages.author.browserSessionHash",
  "messages->author"."deletedAt" AS "messages.author.deletedAt",
  "messages->author"."guestId" AS "messages.author.guestId",
  "messages->note"."id" AS "messages.note.id",
  "messages->note"."title" AS "messages.note.title",
  "messages->note"."recordId" AS "messages.note.recordId",
  "messages->note"."description" AS "messages.note.description",
  "messages->note"."archivedAt" AS "messages.note.archivedAt",
  "messages->note"."lastMessageAt" AS "messages.note.lastMessageAt",
  "messages->note"."messagesCount" AS "messages.note.messagesCount",
  "messages->note"."collectionStringId" AS "messages.note.collectionId",
  "messages->note"."createdAt" AS "messages.note.createdAt",
  "messages->note"."updatedAt" AS "messages.note.updatedAt",
  "messages->note"."deletedAt" AS "messages.note.deletedAt",
  "messages->note"."teamId" AS "messages.note.teamId",
  "messages->note"."authorId" AS "messages.note.authorId"
FROM
  "users" AS "user"
  LEFT OUTER JOIN (
    "mentions" AS "messages->mention"
    INNER JOIN "messages" AS "messages" ON "messages"."id" = "messages->mention"."messageId"
  ) ON "user"."id" = "messages->mention"."userId"
  AND (
    "messages"."deletedAt" > '2020-07-31 06:42:22.626 +00:00'
    OR "messages"."deletedAt" IS NULL
  )
  LEFT OUTER JOIN "users" AS "messages->author" ON "messages"."authorId" = "messages->author"."id"
  AND (
    "messages->author"."deletedAt" > '2020-07-31 06:42:22.626 +00:00'
    OR "messages->author"."deletedAt" IS NULL
  )
  LEFT OUTER JOIN "notes" AS "messages->note" ON "messages"."noteId" = "messages->note"."id"
  AND (
    "messages->note"."deletedAt" > '2020-07-31 06:42:22.626 +00:00'
    OR "messages->note"."deletedAt" IS NULL
  )
WHERE
  (
    (
      "user"."deletedAt" > '2020-07-31 06:42:22.626 +00:00'
      OR "user"."deletedAt" IS NULL
    )
    AND "user"."id" = '5'
  )
ORDER BY
  "messages"."id" DESC
LIMIT
  15 OFFSET 0;

There are still other useless data retrieved here, but this will be addressed in another PR (this is another bug).

Pull Request checklist:

• Write an explicit title for the Pull Request, following Conventional Commits specification
• Create automatic tests
• Test manually the implemented changes
• Review my own code (indentation, syntax, style, simplicity, readability)
• Wonder if you can improve the existing code

[arnaudbesnier]

Task linked: CU-4rg1z2 P3 - Related Data - The related data retrieval query selects useless fields and it can lead to low performance.

[forest-bot]

🎉 This PR is included in version 6.3.4 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀