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?
A
Aaroned14d 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.
J
Jaaneek14d 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 ❤️
A
Aaroned14d 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.
J
Jaaneek14d ago
@Aaroned Why not left join? I have 1 to many relation in this case
A
Aaroned14d 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
J
Jaaneek14d 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
A
Aaroned14d 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
J
Jaaneek14d ago
wtf you are a genius really appreciate that
Want results from more Discord servers?
Add your server
More Posts
prepared statements placeholder typingSchema ```ts const users = sqliteTable("user", { id: text("id").primaryKey(), text: text("text")friendship query that returns the other user objectI have two tables: ```friendships:{id:uuid, sender: uuid, target: uuid, status: enum("accepted","penIssue with push commandHello. I pushed migrations to database using `drizzle-kit push:mysql` and It worked fine. Running itDrizzleKit Studio TypeError: customResultMapper is not a functionI am trying to get DrizzleKit studio to run with Cloudflare D1. I already saw the existing workarounpush:mysql autoincrement errorWhen running `push:mysql`, drizzle-kit throws this error: ``` Warning You have removed the primaryHow to search the docs for the correct datatype returned by each function?Since I'm using typescript, quite often I am welcomed by eslint errors. For eample: ```ts try { Self-Relation in Drizzle ORM?I'm trying to create a folder/file structure in postgresql with drizzle orm I have the following tabDrizzle spontaneously trying to truncate my dbToday I opened up Drizzle Studio, connected to my Vercel Postgres database, and a modal popped up sastring arrayHow are string arrays defined in drizzle? It always seems to revert back to json/jsonb when definingdates in sqlite not being evaluated, causing "Invalid Date"``` expiresAt: int("expires_at", { mode: "timestamp" }) .notNull() .$defaultFn(() =>drizzle-kit generate migration for pg is stuck at: Reading config file 'drizzle.config.ts'Hello, im using Deno 1.41.3. After executing `deno run -A npm:drizzle-kit generate:pg` cli just showHow do I reuse the results from the where clause within the findMany?I have a vendor ID and want to retrieve all restaurant IDs and menu IDs associated with that vendor.drizzle kit generate schemas for mysql had a little type mismatched problem.Has anyone encountered a similar problem of mismatched types with the error message "MySqlVarCharBuiHow to make a text based timestamp type for sqlite with zod schema inferenceWhen I use the built in integer with mode timestamp, the generated zod schemas correctly declare thaWhat's the best way to query my db?I'm running Supabase Postgres locally with Drizzle. In production, I'd use the connection pooler wHow to query many-many with mysqlI setup a simple example to test if many-many query and ran again into the same issue. ```ts Error:How do I access config in fromDriver and toDriver when defining a custom type using customType?None of the examples do this so it's not clear how/if this is possible?Cannot get user roleI'm using Next.js with next auth and the drizzle adapter. I must be doing something wrong because th`drizzle-kit generate:sqlite` doesn't work`lib/drizzle.ts` ```ts import { text, blob, sqliteTable } from "drizzle-orm/sqlite-core"; export coStudio not enough info to infer relationI'm getting this error: ``` throw new Error( ^ Error: There is not enough information to