DT
Drizzle Teamsevenwestonroads

How to reproduce a Prisma `include` statement for arrays of related entities without SQL?

Hello, I'm trying to translate this SQL query to DrizzleORM;
SELECT
channels.id,
channels.name,
channels.slug,
channels.type,
array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;
SELECT
channels.id,
channels.name,
channels.slug,
channels.type,
array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;
My issue is that I don't know how to use / when to use the array_agg - do I have to use raw SQL ? In Prisma, I could just do a findMany for channels w/ include the channel_performances. In my case the objective is to retrieve the channels with inside, the channel_performances as array for each channels. Do you know how to do that in Drizzle ORM ? Thank you !
AS
Andrii Sherman361d ago
You can use few approaches for that 1. Just use simple select with join and then aggregate results in your code. Great example with users+cities, that is actually the same you want https://github.com/drizzle-team/drizzle-orm/blob/main/docs/joins.md#aggregating-results
GitHub
drizzle-orm/joins.md at main · drizzle-team/drizzle-orm
TypeScript ORM for SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.
AS
Andrii Sherman361d ago
Second approach would be to use json_agg I'm afraid I can't find it so fast, but I believe @rphlmr has an example for you And we are going to prepare first class support for json_agg. So you won't need to even write it. Syntax will be close as Prisma, but will be definitely in another layer on top of drizzle core. As a helper @rphlmr Sorry to mention you twice sweating
R
rphlmr ⚡361d ago
Gist
Drizzle snippets
Drizzle snippets. GitHub Gist: instantly share code, notes, and snippets.
AS
Andrii Sherman361d ago
you're the best, thanks!
S
sevenwestonroads361d ago
Thanks, I'll go with the first implementation for the sake of simplicity. However, I got an issue in the code;
import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type City = InferModel<typeof cities>;

const rows = await db
.select({
city: cities,
user: users,
})
.from(cities)
.leftJoin(users, eq(users.cityId, cities.id));

const result = rows.reduce<Record<number, { city: City; users: User[] }>>(
(acc, row) => {
const city = row.city;
const user = row.user;

if (!acc[city.id]) {
acc[city.id] = { city, users: [] };
}

if (user) {
acc[cityId].users.push(user);
}

return acc;
},
{},
);
import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type City = InferModel<typeof cities>;

const rows = await db
.select({
city: cities,
user: users,
})
.from(cities)
.leftJoin(users, eq(users.cityId, cities.id));

const result = rows.reduce<Record<number, { city: City; users: User[] }>>(
(acc, row) => {
const city = row.city;
const user = row.user;

if (!acc[city.id]) {
acc[city.id] = { city, users: [] };
}

if (user) {
acc[cityId].users.push(user);
}

return acc;
},
{},
);
Where is defined cityId in the if scoped statement ? My TS gives me an error of an undefined const.
AS
Andrii Sherman361d ago
oh, maybe a typo will update docs, thanks! I guess you need to use city.id
S
sevenwestonroads361d ago
I figured it out ! :)) Thanks again @Andrii Sherman you're the G
J
jacksn349d ago
do you have any suggestions on making this work with a nested relation?
// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
const chunks: SQL[] = [];

Object.entries(shape).forEach(([key, value]) => {
if (chunks.length > 0) {
chunks.push(sql.raw(`,`));
}
chunks.push(sql.raw(`'${key}',`));
chunks.push(sql`${value}`);
});

return sql<InferColumnsDataTypes<T>[]>`json_build_object(${sql.fromList(
chunks,
)})`;
}

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonAggBuildObject<T extends Record<string, AnyColumn>>(
shape: T,
) {
return sql<InferColumnsDataTypes<T>[]>`coalesce(json_agg(${jsonBuildObject(
shape,
)}), '[]')`;
}
// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
const chunks: SQL[] = [];

Object.entries(shape).forEach(([key, value]) => {
if (chunks.length > 0) {
chunks.push(sql.raw(`,`));
}
chunks.push(sql.raw(`'${key}',`));
chunks.push(sql`${value}`);
});

return sql<InferColumnsDataTypes<T>[]>`json_build_object(${sql.fromList(
chunks,
)})`;
}

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonAggBuildObject<T extends Record<string, AnyColumn>>(
shape: T,
) {
return sql<InferColumnsDataTypes<T>[]>`coalesce(json_agg(${jsonBuildObject(
shape,
)}), '[]')`;
}
Allowing for usage like this:
users: jsonAggBuildObject({
id: user.id,
name: user.name,
image: user.image,
tasks: jsonBuildObject({ id: task.id, title: task.title }),
}),
users: jsonAggBuildObject({
id: user.id,
name: user.name,
image: user.image,
tasks: jsonBuildObject({ id: task.id, title: task.title }),
}),
However, I cant really figure out how to stop typescript from complaining here and infer the correct type for tasks Some builtin feature to support such aggregation would be a great addition to the library, I really like what you guys built! @Raphaël Moreau
R
rphlmr ⚡349d ago
🧐 even if TypeScript complains, does it works? I’ll try to see but right now I have no idea 😅
J
jacksn349d ago
Yeah, it works but i have no idea on how to make it work for typescript
R
rphlmr ⚡349d ago
I have to build a repro to test and debug any chance you have something public I can pull ? I know the issue but I don't know how to solve it. jsonAggBuildObject should take Record<string, AnyColumn> or the return type of jsonBuildObject as arg. But jsonBuildObject type relies on T and T can be of type ReturnType of jsonBuildObject. So their is a loop for TS :/
N
Noahh342d ago
I'm very interested in this topic, I understand why Drizzle doesn't have this functionality but I'm really looking to replicate it as soon as possible (especially nested includes). My use-case schema is this:
export const menuItems = pgTable('menu_items', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name').notNull()
});

export const modifierGroups = pgTable('modifier_groups', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name').notNull()
});

export const menuItemModifierGroups = pgTable(
'menu_item_modifier_groups',
{
menuItemId: uuid('menu_item_id')
.notNull()
.references(() => menuItems.id),
modifierGroupId: uuid('modifier_group_id')
.notNull()
.references(() => modifierGroups.id)
},
(table) => ({
menuItemIdModifierGroupIdOrderPk: primaryKey(
table.menuItemId,
table.modifierGroupId
),
}),
);

export const modifiers = pgTable('modifiers', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name')
});

export const modifierGroupModifiers = pgTable(
'modifier_group_modifiers',
{
modifierGroupId: uuid('modifier_group_id')
.notNull()
.references(() => modifierGroups.id),
modifierId: uuid('modifier_id')
.notNull()
.references(() => modifiers.id)
},
(table) => ({
modifierGroupIdModifierIdOrderPk: primaryKey(
table.modifierGroupId,
table.modifierId
),
}),
);
export const menuItems = pgTable('menu_items', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name').notNull()
});

export const modifierGroups = pgTable('modifier_groups', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name').notNull()
});

export const menuItemModifierGroups = pgTable(
'menu_item_modifier_groups',
{
menuItemId: uuid('menu_item_id')
.notNull()
.references(() => menuItems.id),
modifierGroupId: uuid('modifier_group_id')
.notNull()
.references(() => modifierGroups.id)
},
(table) => ({
menuItemIdModifierGroupIdOrderPk: primaryKey(
table.menuItemId,
table.modifierGroupId
),
}),
);

export const modifiers = pgTable('modifiers', {
id: uuid('id').defaultRandom().primaryKey(),
name: text('name')
});

export const modifierGroupModifiers = pgTable(
'modifier_group_modifiers',
{
modifierGroupId: uuid('modifier_group_id')
.notNull()
.references(() => modifierGroups.id),
modifierId: uuid('modifier_id')
.notNull()
.references(() => modifiers.id)
},
(table) => ({
modifierGroupIdModifierIdOrderPk: primaryKey(
table.modifierGroupId,
table.modifierId
),
}),
);
Basically, a whole bunch of nested stuff. Ideally, I could like this to come out to be
{
"id": "...",
"name": "...",
"modifierGroups": [{
"id": "...",
"name": "...",
"modifiers": [{
"id": "...",
"name": "..."
},
{
"id": "...",
"name": "..."
}]
},
{
"id": "...",
"name": "...",
"modifiers": [{
"id": "...",
"name": "..."
},
{
"id": "...",
"name": "..."
}]
}
]
}
{
"id": "...",
"name": "...",
"modifierGroups": [{
"id": "...",
"name": "...",
"modifiers": [{
"id": "...",
"name": "..."
},
{
"id": "...",
"name": "..."
}]
},
{
"id": "...",
"name": "...",
"modifiers": [{
"id": "...",
"name": "..."
},
{
"id": "...",
"name": "..."
}]
}
]
}
I was able to get it so that each menu item has a modifierGroups list, but I'm not sure where to go from there to get modifiers in each modifierGroups item. Currently, I have
const items = await this.drizzleService.db
.select({
...getTableColumns(menuItems),
modifierGroups: jsonAggBuildObject({
...getTableColumns(modifierGroups),
}),
})
.from(menuItems)
.leftJoin(
menuItemModifierGroups,
eq(menuItems.id, menuItemModifierGroups.menuItemId),
)
.leftJoin(
modifierGroups,
eq(menuItemModifierGroups.modifierGroupId, modifierGroups.id),
)
.groupBy(menuItems.id);
const items = await this.drizzleService.db
.select({
...getTableColumns(menuItems),
modifierGroups: jsonAggBuildObject({
...getTableColumns(modifierGroups),
}),
})
.from(menuItems)
.leftJoin(
menuItemModifierGroups,
eq(menuItems.id, menuItemModifierGroups.menuItemId),
)
.leftJoin(
modifierGroups,
eq(menuItemModifierGroups.modifierGroupId, modifierGroups.id),
)
.groupBy(menuItems.id);
Which gives me
[
{
"id": "317078bd-6265-4156-986c-085bdf297765",
"name": "SmashBurger",
"modifierGroups": [
{
"id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
"name": "Toppings"
},
{
"id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
"name": "Toppings"
},
{
"id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
"name": "Double Up"
},
{
"id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
"name": "Double Up"
}
]
}
]
[
{
"id": "317078bd-6265-4156-986c-085bdf297765",
"name": "SmashBurger",
"modifierGroups": [
{
"id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
"name": "Toppings"
},
{
"id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
"name": "Toppings"
},
{
"id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
"name": "Double Up"
},
{
"id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
"name": "Double Up"
}
]
}
]
V
volks342d ago
@Noahh My usual approach for these complicated things is to first write it out in raw SQL and then translate that to Drizzle. Its much easier to reason with
J
jacksn342d ago
I think this would be best implemented by getting the modifierGroups in a subquery
Want results from more Discord servers?
Add your server
More Posts
many-to-one selection as arrayHi, Let's say I have a user table, and the user can have multiple profiles. When I select using joiAlias in from() change? (SQLite)Did something change wrt using aliases in `from()`? Until recently I've been using aliases to affectTable definition has 'any' typeI'm defining all of my tables in an `index.ts` file; but on some tables like `visitor_events` I get CTE query of hierarchical dataI'd like to drizzle-ize this query: ```sql WITH RECURSIVE Child(n) AS ( VALUES('...id') createInsertSchema wrong type (drizzle-zod 0.3.1, sqlite))After upgrading to 0.3.1, all of the fields changed to ZodTypeAnyHow do I get the values of an insert inside a transaction?I have this transaction where I create a project and add variables and a history of those variables.Raw sql`` quoting issueHi, I doing a work around until Drizzle supports generated columns. I have a manual script that addsNuxt3 type errorHello, is there somebody who used Drizzle in Nuxt3 project? I have problem with inheriting types froSyntax error mysql migration using composite primary key```sql --> statement-breakpoint CREATE TABLE `member_to_address` ( `member_id` varchar(32) NOT Nwhen using planetscale, using the `.$with()` and `.with()` clauses causes errorIs it not possible to use `$with()` and `with()` with planetscale? it gives the following error: `DaZod prototype mismatchEven though the Zod object constructor name created by Drizzle is ZodObject, it is not an instanceofunrecognized_keysGetting the following error when running generate:pg ``` ZodError: [ { "code": "unrecognized_kRaw SQL / Postgres stored generated column in schemaIs it possibile to add raw sql, or more specifically stored generated columns to the schema? Trying I think i have found another bugtypescript complaining when there's no overridingCustom getter/setter for model's propertyHi, are there any plans for custom getters? (like in TypeORM `transformer`)drizzle-zod type inferLooks there is problem in createInsertSchema type inference. 🤔 (+ need drizzle-zod tag in this posWhy is drizzle-zod converting a string to enum?It seems like it is converting mysql text() or varchar to enums<unknown>?how to do ANYneed to do `where 'myvariable' = ANY(mycolumn)`[BUG?] Postgres transactions throwing connection timeouts after a lot of queriesI think there's a syntax error in Postgres transactions ----> see screenshot cc: @bloberenoberUnique Key SchemaI am trying to create an unique key on a table using a custom schema, the generated sql migration fi