Pagination of to-many joins with joined strategy and PopulateHint.INFER

[B4nan]

this.repo.find({
  "media": {
    "createdBy": "5239ddb8-8daa-46ea-abee-e4b9ea8d5589",
    "bookings": {
      "createdBy": {
        "id": "5239ddb8-8daa-46ea-abee-e4b9ea8d5589"
      }
    }
  }
}, {
	fields: ["media.alt", "media.bookings.reminded", "media.bookings.createdBy"],
	limit: 20,
	populateWhere: PopulateHint.INFER,
})

SQL with limit:

 SELECT
	"l0"."id",
	"m1"."id" AS "m1__id",
	"m1"."alt" AS "m1__alt",
	"b3"."id" AS "b3__id",
	"b3"."reminded" AS "b3__reminded",
	"b3"."created_by_id" AS "b3__created_by_id",
	"c5"."id" AS "c5__id",
	"c5"."created_at" AS "c5__created_at",
	"c5"."updated_at" AS "c5__updated_at",
	"c5"."email" AS "c5__email",
	"c5"."first_name" AS "c5__first_name",
	"c5"."last_name" AS "c5__last_name",
	"c5"."password" AS "c5__password",
	"c5"."role" AS "c5__role",
	"c5"."is_disabled" AS "c5__is_disabled"
FROM
	"location" AS "l0"
	LEFT JOIN "location_media" AS "l2" ON "l0"."id" = "l2"."location_id"
	LEFT JOIN "medium" AS "m1" ON "l2"."medium_id" = "m1"."id"
	LEFT JOIN "booking_media" AS "b4" ON "m1"."id" = "b4"."medium_id"
	LEFT JOIN "booking" AS "b3" ON "b4"."booking_id" = "b3"."id"
	LEFT JOIN "user" AS "c5" ON "b3"."created_by_id" = "c5"."id"
WHERE
	"l0"."id" in(
		SELECT
			"l0"."id" FROM (
				SELECT
					"l0"."id" FROM "location" AS "l0"
				LEFT JOIN "location_media" AS "l2" ON "l0"."id" = "l2"."location_id"
				LEFT JOIN "medium" AS "m1" ON "l2"."medium_id" = "m1"."id"
				LEFT JOIN "booking_media" AS "b4" ON "m1"."id" = "b4"."medium_id"
				LEFT JOIN "booking" AS "b3" ON "b4"."booking_id" = "b3"."id"
				LEFT JOIN "user" AS "c5" ON "b3"."created_by_id" = "c5"."id"
			WHERE
				"m1"."created_by_id" = '5239ddb8-8daa-46ea-abee-e4b9ea8d5589'
				AND "b3"."created_by_id" = '5239ddb8-8daa-46ea-abee-e4b9ea8d5589'
			GROUP BY
				"l0"."id"
			LIMIT 20) AS "l0")

SQL without limit

SELECT
	"l0"."id",
	"m1"."id" AS "m1__id",
	"m1"."alt" AS "m1__alt",
	"b3"."id" AS "b3__id",
	"b3"."reminded" AS "b3__reminded",
	"b3"."created_by_id" AS "b3__created_by_id",
	"c5"."id" AS "c5__id",
	"c5"."created_at" AS "c5__created_at",
	"c5"."updated_at" AS "c5__updated_at",
	"c5"."email" AS "c5__email",
	"c5"."first_name" AS "c5__first_name",
	"c5"."last_name" AS "c5__last_name",
	"c5"."password" AS "c5__password",
	"c5"."role" AS "c5__role",
	"c5"."is_disabled" AS "c5__is_disabled"
FROM
	"location" AS "l0"
	LEFT JOIN "location_media" AS "l2" ON "l0"."id" = "l2"."location_id"
	LEFT JOIN "medium" AS "m1" ON "l2"."medium_id" = "m1"."id"
	LEFT JOIN "booking_media" AS "b4" ON "m1"."id" = "b4"."medium_id"
	LEFT JOIN "booking" AS "b3" ON "b4"."booking_id" = "b3"."id"
	LEFT JOIN "user" AS "c5" ON "b3"."created_by_id" = "c5"."id"
WHERE
	"m1"."created_by_id" = '5239ddb8-8daa-46ea-abee-e4b9ea8d5589'
	AND "b3"."created_by_id" = '5239ddb8-8daa-46ea-abee-e4b9ea8d5589'

Originally posted by @yackinn in #2980 (reply in thread)