sql - TypeORM 限制不起作用，无论设置什么限制，查询都返回数组中的一个元素？

Question

我是 TypeORM 的新手。我曾经createQueryBuilder从数据库中获取数据并使用.limit函数来设置限制。但无论在查询中设置什么限制，结果中只返回数组中的单个条目。

代码：

           await this.createQueryBuilder()
            .select(['videoComment.id', 'videoComment.comment', 'videoComment.languageCode', 'videoComment.createdAt'
                , 'video.id', 'user.id', 'user.name', 'user.profileImage'])
            .from(VideoComment, 'videoComment')
            .innerJoin('videoComment.video', 'video', ' video.id = :videoId  and video.isActive = :isActive ')
            .innerJoin('videoComment.user', 'user')
            .where(' videoComment.id > :commentId ' +
                ' and videoComment.isActive = :isActive ', {
                videoId: videoId,
                commentId: commentId,
                isActive: isActive
            })
            .orderBy('videoComment.id')
            .limit(size.valueOf())
            .getMany();

生成的查询：

SELECT "videoComment"."id"            AS "videoComment_id",
       "videoComment"."created_at"    AS "videoComment_created_at",
       "videoComment"."comment"       AS "videoComment_comment",
       "videoComment"."language_code" AS "videoComment_language_code",
       "video"."id"                   AS "video_id",
       "user"."id"                    AS "user_id",
       "user"."name"                  AS "user_name",
       "user"."profile_image"         AS "user_profile_image"
FROM "video_comment" "VideoComment",
     "video_comment" "videoComment"
         INNER JOIN "video" "video"
                    ON "video"."id" = "videoComment"."video_id" AND ("video"."id" = $1 and "video"."is_active" = $2)
         INNER JOIN "user_detail" "user" ON "user"."id" = "videoComment"."user_id"
WHERE "videoComment"."id" > $3
  and "videoComment"."is_active" = $4
ORDER BY "videoComment"."id" ASC
LIMIT 5

当我在 POSTGRES 客户端上执行它时，查询工作正常。它返回了想要的结果。但在应用程序代码中，它只返回结果集中的单个条目。

标签： sqlnode.jspostgresqlormtypeorm

Answer 1

通过 SelectQueryBuilder.ts 文件我发现：

/**
     * Set's LIMIT - maximum number of rows to be selected.
     * NOTE that it may not work as you expect if you are using joins.
     * If you want to implement pagination, and you are having join in your query,
     * then use instead take method instead.
     */
    limit(limit?: number): this;

所以我使用了.take函数。它给出了正确的结果，但生成了一个额外的查询。

SELECT DISTINCT "distinctAlias"."videoComment_id" as "ids_videoComment_id", "distinctAlias"."videoComment_id"
FROM (SELECT "videoComment"."id"            AS "videoComment_id",
             "videoComment"."created_at"    AS "videoComment_created_at",
             "videoComment"."comment"       AS "videoComment_comment",
             "videoComment"."language_code" AS "videoComment_language_code",
             "video"."id"                   AS "video_id",
             "user"."id"                    AS "user_id",
             "user"."name"                  AS "user_name",
             "user"."profile_image"         AS "user_profile_image"
      FROM "video_comment" "VideoComment",
           "video_comment" "videoComment"
               INNER JOIN "video" "video" ON "video"."id" = "videoComment"."video_id" AND
                                             ("video"."id" = $1 and "video"."is_active" = $2)
               INNER JOIN "user_detail" "user" ON "user"."id" = "videoComment"."user_id"
      WHERE "videoComment"."id" > $3
        and "videoComment"."is_active" = $4) "distinctAlias"
ORDER BY "distinctAlias"."videoComment_id" ASC, "videoComment_id" ASC
LIMIT 5
    
SELECT "videoComment"."id"            AS "videoComment_id",
       "videoComment"."created_at"    AS "videoComment_created_at",
       "videoComment"."comment"       AS "videoComment_comment",
       "videoComment"."language_code" AS "videoComment_language_code",
       "video"."id"                   AS "video_id",
       "user"."id"                    AS "user_id",
       "user"."name"                  AS "user_name",
       "user"."profile_image"         AS "user_profile_image"
FROM "video_comment" "VideoComment",
     "video_comment" "videoComment"
         INNER JOIN "video" "video"
                    ON "video"."id" = "videoComment"."video_id" AND ("video"."id" = $1 and "video"."is_active" = $2)
         INNER JOIN "user_detail" "user" ON "user"."id" = "videoComment"."user_id"
WHERE ("videoComment"."id" > $3 and "videoComment"."is_active" = $4)
  AND "videoComment"."id" IN ($5, $6, $7, $8, $9)
ORDER BY "videoComment"."id" ASC

它首先获取不同的 id，然后使用这些 id 来获取查询的实际结果集。这似乎效率较低，因为执行了一个额外的查询，但解决了问题。