PWSey Comments - Answer Overflow

PWSey

Posts Comments

DTDrizzle Team

•Created by PWSey on 10/30/2023 in #help

Using db. causes the whole app to hang forever.

It was because of getFeedbacks, moving that function to another file fixed it for some reason

2 replies

DTDrizzle Team

•Created by codefork on 10/14/2023 in #help

SSL/TLS error when running push:mysql with planetscale

I believe you need to add to the end of the Database url

{"rejectUnauthorized":true}

{"rejectUnauthorized":true}

Example

DATABASE_URL='mysql://username:[email protected]/database?ssl={"rejectUnauthorized":true}'

DATABASE_URL='mysql://username:[email protected]/database?ssl={"rejectUnauthorized":true}'

2 replies

DTDrizzle Team

•Created by PWSey on 10/23/2023 in #help

How to get a similar result of db.query but with extra aggregation and using select

I think I got it, thanks again!

  const mostUpvoted = await db
    .select({
      id: feedback.id,
      feedbackNo: feedback.feedbackNo,
      userId: feedback.userId,
      title: feedback.title,
      body: feedback.body,
      type: feedback.type,
      status: feedback.status,
      isPinned: feedback.isPinned,
      createdAt: feedback.createdAt,
      updatedAt: feedback.updatedAt,
      totalVotes:
        sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`.as(
          "totalVotes"
        ),
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(desc(sql.identifier("totalVotes")));

  const mostUpvoted = await db
    .select({
      id: feedback.id,
      feedbackNo: feedback.feedbackNo,
      userId: feedback.userId,
      title: feedback.title,
      body: feedback.body,
      type: feedback.type,
      status: feedback.status,
      isPinned: feedback.isPinned,
      createdAt: feedback.createdAt,
      updatedAt: feedback.updatedAt,
      totalVotes:
        sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`.as(
          "totalVotes"
        ),
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(desc(sql.identifier("totalVotes")));

12 replies

DTDrizzle Team

•Created by PWSey on 10/23/2023 in #help

How to get a similar result of db.query but with extra aggregation and using select

also how can I orderBy totalVotes when I order by feedbackId it seems to be working for some reason not sure, I was hoping I could orderBy totalVotes

12 replies

DTDrizzle Team

•Created by PWSey on 10/23/2023 in #help

How to get a similar result of db.query but with extra aggregation and using select

  const mostUpvoted = await db
    .select({
      id: feedback.id,
      feedbackNo: feedback.feedbackNo,
      userId: feedback.userId,
      title: feedback.title,
      body: feedback.body,
      type: feedback.type,
      status: feedback.status,
      isPinned: feedback.isPinned,
      createdAt: feedback.createdAt,
      updatedAt: feedback.updatedAt,
      totalVotes: sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`,
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(asc(feedback.id));

  const mostUpvoted = await db
    .select({
      id: feedback.id,
      feedbackNo: feedback.feedbackNo,
      userId: feedback.userId,
      title: feedback.title,
      body: feedback.body,
      type: feedback.type,
      status: feedback.status,
      isPinned: feedback.isPinned,
      createdAt: feedback.createdAt,
      updatedAt: feedback.updatedAt,
      totalVotes: sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`,
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(asc(feedback.id));

I think I got it working, thanks the posts you have mentioned they were very helpful, I am not sure as you mentioned doing a lot in a single query I just did not want to do another trip to the database I believe it would be not ideal? I will be doing filtering but I want to do it in the database level as then when I implement pagination it will work properly. Thanks again!

12 replies

DTDrizzle Team

•Created by PWSey on 10/23/2023 in #help

How to get a similar result of db.query but with extra aggregation and using select

The objects for reference with db.select:

    id: 'id',
    feedbackNo: 53,
    userId: 'user-id',
    title: 'Title',
    body: { type: 'doc', content: [Array] },
    type: 'type',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:46:36.149Z,
    updatedAt: 2023-10-22T14:46:36.149Z,
    totalVotes: '1',
    votes: {
      id: 'id',
      userId: 'id',
      feedbackId: 'id',
      voteType: 'UP',
      createdAt: 2023-10-23T20:26:32.006Z,
      updatedAt: 2023-10-23T20:26:32.006Z
    },
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
    }

    id: 'id',
    feedbackNo: 53,
    userId: 'user-id',
    title: 'Title',
    body: { type: 'doc', content: [Array] },
    type: 'type',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:46:36.149Z,
    updatedAt: 2023-10-22T14:46:36.149Z,
    totalVotes: '1',
    votes: {
      id: 'id',
      userId: 'id',
      feedbackId: 'id',
      voteType: 'UP',
      createdAt: 2023-10-23T20:26:32.006Z,
      updatedAt: 2023-10-23T20:26:32.006Z
    },
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
    }

And this is with db.query:

{
    id: 'id',
    feedbackNo: 51,
    userId: 'id',
    title: 'title',
    body: { type: 'doc', content: [Array] },
    type: 'general',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:41:12.460Z,
    updatedAt: 2023-10-22T14:41:12.460Z,
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
     
    },
    votes: [ [Object] ],
  },

{
    id: 'id',
    feedbackNo: 51,
    userId: 'id',
    title: 'title',
    body: { type: 'doc', content: [Array] },
    type: 'general',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:41:12.460Z,
    updatedAt: 2023-10-22T14:41:12.460Z,
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
     
    },
    votes: [ [Object] ],
  },

12 replies

Gaming

Programming