DT
Drizzle Team•10mo ago
Jaaneek

How to Join/subquery when using orderBy and groupBy

      const data = await ctx.db
        .select({
          propositionId: propositionsVotes.propositionId,
          twitchSubscriptionsCount: sql<number>`cast(count(nullif(${propositionsVotes.twitchSubscribed},false)) as int)`,
          youtubeSubscriptionsCount: sql<number>`cast(count(nullif(${propositionsVotes.youtubeSubscribed},false)) as int)`,
          normalVotesCount: sql<number>`cast(count(${propositionsVotes.profileId}) as int)`,
          proposition: WRITE SOMETHING THAT WILL GIVE ME PROPOSITION DATA IN HERE
        })
        .from(propositionsVotes)
        .groupBy(propositionsVotes.propositionId)
        .orderBy(
          sql<number>`cast(count(${propositionsVotes.twitchSubscribed}) as int)`,
        );
      const data = await ctx.db
        .select({
          propositionId: propositionsVotes.propositionId,
          twitchSubscriptionsCount: sql<number>`cast(count(nullif(${propositionsVotes.twitchSubscribed},false)) as int)`,
          youtubeSubscriptionsCount: sql<number>`cast(count(nullif(${propositionsVotes.youtubeSubscribed},false)) as int)`,
          normalVotesCount: sql<number>`cast(count(${propositionsVotes.profileId}) as int)`,
          proposition: WRITE SOMETHING THAT WILL GIVE ME PROPOSITION DATA IN HERE
        })
        .from(propositionsVotes)
        .groupBy(propositionsVotes.propositionId)
        .orderBy(
          sql<number>`cast(count(${propositionsVotes.twitchSubscribed}) as int)`,
        );
I need to get proposition, how should I approach that?
8 Replies
Aaroned
Aaroned•10mo ago
@Jaaneek since you are grouping by propositionId, have you tried simply doing an innerJoin on your proposition table. You would also have to add all the columns you want from proposition to the groupBy clause.
Jaaneek
JaaneekOP•10mo ago
@Aaroned Thx, works. If you are willing I would really love to read why it works like that. Even a quick explanation would be awesome ❤️
Aaroned
Aaroned•10mo ago
@Jaaneek INNER JOIN will create a result for each pair of records that match the inner join criteria. GROUP BY will aggregate those results to create a single result for each unique set of values in the columns specified in group by. Only aggregates like COUNT can be returned in addition to the group by columns.
Jaaneek
JaaneekOP•10mo ago
@Aaroned Why not left join? I have 1 to many relation in this case
Aaroned
Aaroned•10mo ago
@Jaaneek Use an inner join when you want to retrieve only the rows where there is a match in both tables based on the join condition. Use a left join when you want to retrieve all rows from the left table and the matched rows from the right table, with NULL values for non-matching rows. In your case the left table propositionVotes should always have a value in the right table proposition, therefore inner join
Jaaneek
JaaneekOP•10mo ago
Should I maybe query by the propositions table? and then do left join? I need to query all propositions anyway But then I have no idea how to orderBY :X Right now I'm only selecting propositions if propositionvotes exists. but I would like to always get them
Aaroned
Aaroned•10mo ago
yes querying by the propositions table would return all propositions, including those with zero votes, then do a left join. the orderby would be the same
Jaaneek
JaaneekOP•10mo ago
wtf you are a genius really appreciate that

Did you find this page helpful?