DT
Drizzle Team•13mo ago
olup

mapWith, any special things to make it work ?

I can't seem to have mapWith() work - I am logging inside the function and it nvever gets called. I am doing some sql-fu and want to return a collection that is declared in drizzle, and would like to have it mapped to my js object. I tryed on sql``.mapWith(), sql.join().mapWith() without any success. Also I was wondering if there was a way to pass the drizzle table object somehow so that the return of the sql gets mapped with the default mapping of this table, not having to declare a mapper again ? the doc is not super clear on this front.
26 Replies
Angelelz
Angelelz•13mo ago
Let's do it over here, can you share the code that doesn't work for you?
olup
olupOP•13mo ago
let me first discribe the use case. I am doing a complicated sql query that looks like this:
const query = sql`
SELECT
${CustomerPlanModel.id} as customer_plan_id,
${CustomerPlanModel.networkProvider} as network_provider,
array_agg(DISTINCT ${
orderModel.planExternalReference
}) as external_references
FROM ${CustomerPlanModel}
INNER JOIN ${orderModel} ON ${CustomerPlanModel.id} = ${
orderModel.customerPlanId
}
WHERE ${CustomerPlanModel.id} IN (${sql.join(
planIds.map((id) => sql`${id}`),
sql`,`,
)})
GROUP BY ${CustomerPlanModel.id};
`;
const query = sql`
SELECT
${CustomerPlanModel.id} as customer_plan_id,
${CustomerPlanModel.networkProvider} as network_provider,
array_agg(DISTINCT ${
orderModel.planExternalReference
}) as external_references
FROM ${CustomerPlanModel}
INNER JOIN ${orderModel} ON ${CustomerPlanModel.id} = ${
orderModel.customerPlanId
}
WHERE ${CustomerPlanModel.id} IN (${sql.join(
planIds.map((id) => sql`${id}`),
sql`,`,
)})
GROUP BY ${CustomerPlanModel.id};
`;
this query returns a list of customerPlan objects however those are object with keys set to the database column names, which are un snake_case when declaring the drizzle model, we used snake_case for the db, and camelCase in js side
export const CustomerPlanModel = pgTable(
'customer_plan',
{
id: uuid('id').primaryKey().defaultRandom(),
initialDuration: integer('initial_duration'),
usageStartedAt: timestamp('usage_started_at'),
...
export const CustomerPlanModel = pgTable(
'customer_plan',
{
id: uuid('id').primaryKey().defaultRandom(),
initialDuration: integer('initial_duration'),
usageStartedAt: timestamp('usage_started_at'),
...
What i am looking to get is a regular customerPlan with camelCase names
Angelelz
Angelelz•13mo ago
You don't need to do the whole thing in sql Just the complicated parts Like this:
const query = db.select({
customerPlanId: CustomerPlanModel.id,
networkProvider: CustomerPlanModel.networkProvider,
externalReferences: sql<WhateverTypeThisColumnIs[]>`array_agg(distinct ${orderModer.planExternalReference})`
}).from(CustomerPlanModel)
.innerJoin(orderModel, eq(CustomerPlanModel.id, orderModel.customerPlanId))
.where(in(CustomerPlanModel, planIds.map((id) => id)))
.groupBy(CustomerPlanModel.id)
const query = db.select({
customerPlanId: CustomerPlanModel.id,
networkProvider: CustomerPlanModel.networkProvider,
externalReferences: sql<WhateverTypeThisColumnIs[]>`array_agg(distinct ${orderModer.planExternalReference})`
}).from(CustomerPlanModel)
.innerJoin(orderModel, eq(CustomerPlanModel.id, orderModel.customerPlanId))
.where(in(CustomerPlanModel, planIds.map((id) => id)))
.groupBy(CustomerPlanModel.id)
I might have errors, cause I haven't tested it, but that'll get you in the right direction
olup
olupOP•13mo ago
ok. What about another use cas - we do a complex update with a temp table and want to return the updated lines this kinda have to be in sql, and we use the returning statement I gues smapWith is not used for that
Angelelz
Angelelz•13mo ago
BTW, the mapWith method only works at the column level, for externalReferences column for example Not for the row or query level
olup
olupOP•13mo ago
ok
Angelelz
Angelelz•13mo ago
No, you can make it happen the same way
olup
olupOP•13mo ago
there is no way to leveragle drizzle internal mapping when returning a sql query that we know are of a certain type ?
Angelelz
Angelelz•13mo ago
I believe you should be able to do subqueries and/or ctes in updates Yes there is
olup
olupOP•13mo ago
// generate the derived table
const derivedTableLines: SQL[] = [];
for (const usage of usages) {
derivedTableLines.push(
sql`( ${usage.customerId}::uuid, ${usage.dataConsumed}::int, ${usage.zoneExternalReference} )`,
sql`,`,
);
}

derivedTableLines.pop(); // remove the last comma

// build the query
const dataConsumedColumn = sql.raw(CustomerPlanModel.dataConsumed.name);
const usageStartedAt = sql.raw(CustomerPlanModel.usageStartedAt.name);
const expirationDateColumn = sql.raw(CustomerPlanModel.expirationDate.name);
const usageLastUpdatedAtColumn = sql.raw(
CustomerPlanModel.usageLastUpdatedAt.name,
);
const query = sql.join([
sql`
UPDATE ${CustomerPlanModel}
SET
${dataConsumedColumn} = ${CustomerPlanModel.dataConsumed} + u.dataConsumed,
${usageStartedAt} = COALESCE(${CustomerPlanModel.usageStartedAt}, NOW()),
${expirationDateColumn} = COALESCE(${CustomerPlanModel.expirationDate}, NOW() + INTERVAL '1 DAY' * ${CustomerPlanModel.initialDuration}),
${usageLastUpdatedAtColumn} = NOW()

--- create a derived table with the values to update
FROM
(VALUES `,
...derivedTableLines,
sql`
) u(customerId, dataConsumed, zoneExternalReference)

WHERE ${CustomerPlanModel.customerId} = u.customerId
AND ${CustomerPlanModel.zoneExternalReference} = u.zoneExternalReference

RETURNING *;
`,
]);

// execute the query
const updatedPlans = await this.db
.execute<CustomerPlanModel>(query)
.catch((e) => {
const pgDialect = new PgDialect();
Logger.error('handleUsageEvent: error running the query', {
query: pgDialect.sqlToQuery(query),
});
throw e;
});
// generate the derived table
const derivedTableLines: SQL[] = [];
for (const usage of usages) {
derivedTableLines.push(
sql`( ${usage.customerId}::uuid, ${usage.dataConsumed}::int, ${usage.zoneExternalReference} )`,
sql`,`,
);
}

derivedTableLines.pop(); // remove the last comma

// build the query
const dataConsumedColumn = sql.raw(CustomerPlanModel.dataConsumed.name);
const usageStartedAt = sql.raw(CustomerPlanModel.usageStartedAt.name);
const expirationDateColumn = sql.raw(CustomerPlanModel.expirationDate.name);
const usageLastUpdatedAtColumn = sql.raw(
CustomerPlanModel.usageLastUpdatedAt.name,
);
const query = sql.join([
sql`
UPDATE ${CustomerPlanModel}
SET
${dataConsumedColumn} = ${CustomerPlanModel.dataConsumed} + u.dataConsumed,
${usageStartedAt} = COALESCE(${CustomerPlanModel.usageStartedAt}, NOW()),
${expirationDateColumn} = COALESCE(${CustomerPlanModel.expirationDate}, NOW() + INTERVAL '1 DAY' * ${CustomerPlanModel.initialDuration}),
${usageLastUpdatedAtColumn} = NOW()

--- create a derived table with the values to update
FROM
(VALUES `,
...derivedTableLines,
sql`
) u(customerId, dataConsumed, zoneExternalReference)

WHERE ${CustomerPlanModel.customerId} = u.customerId
AND ${CustomerPlanModel.zoneExternalReference} = u.zoneExternalReference

RETURNING *;
`,
]);

// execute the query
const updatedPlans = await this.db
.execute<CustomerPlanModel>(query)
.catch((e) => {
const pgDialect = new PgDialect();
Logger.error('handleUsageEvent: error running the query', {
query: pgDialect.sqlToQuery(query),
});
throw e;
});
Angelelz
Angelelz•13mo ago
But you have to do it by column
olup
olupOP•13mo ago
you mean call mapWith repeatedly ?
Angelelz
Angelelz•13mo ago
I mean, use mapWith on each column you need it
olup
olupOP•13mo ago
so if I retrun 5 cols, I can chain the mapWith calls, one for each col ?
Angelelz
Angelelz•13mo ago
You are thinking too bare metal mapWith won't work in almost plain sql
olup
olupOP•13mo ago
I take this as a compliment 😄 any other trick than mapWith then ?
Angelelz
Angelelz•13mo ago
You are supposed to use db.select() to use mapWith
olup
olupOP•13mo ago
the doc does not mention that, hence my confusion
Angelelz
Angelelz•13mo ago
Oh, wait... You have an update from query Let me check that
olup
olupOP•13mo ago
yes
Angelelz
Angelelz•13mo ago
olup
olupOP•13mo ago
a ok - so for this query I won't be able to use the query builder right ?
Angelelz
Angelelz•13mo ago
Unfortunately no
olup
olupOP•13mo ago
so no way to map the returning statement to js object ?
Angelelz
Angelelz•13mo ago
The problem is the from clause No, you have to do it manually
olup
olupOP•13mo ago
I was hoping I could access the mapping function from the model object but ok i'll wait for the update from to be implemented 😄 thnaks for the great work, and the support
Want results from more Discord servers?
Add your server