typeorm limit不起作用，查询返回数组中的一个元素，不管设置了什么限制？

6qfn3psc 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(272)

我不熟悉打字。我用过 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客户机上执行查询时，查询工作正常。它返回了期望的结果。但在应用程序代码中，它只返回结果集中的单个条目。

sql postgresql node.js orm typeorm

来源：https://stackoverflow.com/questions/63277353/typeorm-limit-not-working-query-returned-one-element-in-the-array-no-matter-wha

1条答案

按热度按时间

8zzbczxx1#

查看我找到的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获取查询的实际结果集。这似乎效率较低，因为执行了一个额外的查询，但解决了问题。

赞(0）回复(0）举报 2021-07-24

我来回答

typeorm limit不起作用，查询返回数组中的一个元素，不管设置了什么限制？

1条答案

相关问题

热门标签

最新问答