Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Limit Query in Post resolver : limit instead of take #29

Open
ginamdar opened this issue Mar 10, 2021 · 4 comments
Open

Limit Query in Post resolver : limit instead of take #29

ginamdar opened this issue Mar 10, 2021 · 4 comments

Comments

@ginamdar
Copy link

ginamdar commented Mar 10, 2021

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
Copy link

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
Copy link

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
Copy link

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
Copy link

Possible solution typeorm/typeorm#747 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants