Limit Query in Post resolver : limit instead of take

[ginamdar]

Thank you so much for this course @benawad, I am almost into 8 hours in it and i started with MongoDB and it worked well until Query Builder started :) so switch to Postgres

Found that we can still query using queryBuilder

    const qb = getConnection()
    .getRepository(Post)
    .createQueryBuilder("p")
    .innerJoinAndSelect(
      "p.creator",
      "user",
      "user.id = p.\"creatorId\""
    )
    .orderBy("p.\"createdAt\"", "DESC");
    if (cursor) {
      qb.where('p.\"createdAt\" < :cursor', { cursor: new Date(+cursor) })
    }
    qb.limit(realLimit); // instead of take()

also the query looks much more cleaner than using "take"

SELECT "p"."id" AS "p_id", "p"."uuid" AS "p_uuid", "p"."createdAt" AS "p_createdAt", "p"."updatedAt" AS "p_updatedAt", "p"."title" AS "p_title", "p"."text" AS "p_text", "p"."points" AS "p_points", "p"."creatorId" AS "p_creatorId", "user"."id" AS "user_id", "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."username" AS "user_username", "user"."password" AS "user_password", "user"."email" AS "user_email" FROM "post" "p" INNER JOIN "user" "user" ON "user"."id"="p"."creatorId" AND ("user"."id" = p."creatorId") WHERE p."createdAt" < $1 ORDER BY p."createdAt" DESC LIMIT 50

Found this here

[Devorein]

You, my friend, are a lifesaver. I was hesitant on trying Ben's solution on using raw SQL for joining and then mapping the values using json function, but since this works it has saved me hours. Many thanks.

[Devorein]

I think there is still a bit more optimization that could be done here. For example, this will always join the post and user table, even if the query didn't ask for the creator fields. In that case, checking the info object to see if the query contains the creator field and only joining it would be better.

[AaronMcCloskey]

Thank you! This is great, I thought having to write SQL was a bit more effort and assumed it could have been achieved with the queryBuilder

Although, I think;

qb.limit(realLimit);

should be

qb.limit(realLimit + 1);

and in the return

return {
  posts: posts.slice(0, realLimit),
  hasMore: posts.length === realLimit + 1,
};

Or instead of realLimit + 1 use the variable reaLimitPlusOne

For the hasMore functionality to work correctly.

[SergeNarhi]

Possible solution typeorm/typeorm#747 (comment)