Drizzle Team•4mo ago

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•4mo 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•4mo 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•4mo 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•4mo ago

@Aaroned Why not left join? I have 1 to many relation in this case

Aaroned•4mo 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•4mo 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•4mo 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•4mo ago

wtf you are a genius really appreciate that

Gaming

Programming

How to Join/subquery when using orderBy and groupBy