Group by Multiple columns with drizzle

I have a schema that looks rougly like this:

date | location | name | quantity

date | location | name | quantity

I want to groupBy multiple columns(date,location), but I'm not sure if there is a drizzle-way to do it, or should I do a raw query?

        const grouped = await d
            .select({
                location: marketGoods.location,
                                date: marketGoods.date
                quantitySum: sql<number>`sum(${marketGoods.quantity})`,                
            })
            .from(marketGoods)
///idk what to do here
            .groupBy(({ date }) => date)
            .groupBy(({ good }) => good);

        const grouped = await d
            .select({
                location: marketGoods.location,
                                date: marketGoods.date
                quantitySum: sql<number>`sum(${marketGoods.quantity})`,                
            })
            .from(marketGoods)
///idk what to do here
            .groupBy(({ date }) => date)
            .groupBy(({ good }) => good);

3 Replies

EidurOP•16mo ago

I think I solved it lol

.groupBy((t) => [t.good, t.date]);

.groupBy((t) => [t.good, t.date]);

Asseater Peter•16mo ago

Yeah that's how I'd do it (tho I haven't tried it in a hot minute) Is it working?

EidurOP•16mo ago

yeah, it is. I just didnt realize that's how GroupBy works in drizzle. Docs dont have an example for it, but just looking at types made it pretty obvious

Gaming

Programming

Group by Multiple columns with drizzle