Drizzle Team•14mo ago

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

Let's do it over here, can you share the code that doesn't work for you?

olupOP•14mo 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•14mo 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

olupOP•14mo 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•14mo ago

BTW, the mapWith method only works at the column level, for externalReferences column for example Not for the row or query level

olupOP•14mo ago

Angelelz•14mo ago

No, you can make it happen the same way

olupOP•14mo ago

there is no way to leveragle drizzle internal mapping when returning a sql query that we know are of a certain type ?

Angelelz•14mo ago

I believe you should be able to do subqueries and/or ctes in updates Yes there is

olupOP•14mo 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•14mo ago

But you have to do it by column

olupOP•14mo ago

you mean call mapWith repeatedly ?

Angelelz•14mo ago

I mean, use mapWith on each column you need it

olupOP•14mo ago

so if I retrun 5 cols, I can chain the mapWith calls, one for each col ?

Angelelz•14mo ago

You are thinking too bare metal mapWith won't work in almost plain sql

olupOP•14mo ago

I take this as a compliment 😄 any other trick than mapWith then ?

Angelelz•14mo ago

You are supposed to use db.select() to use mapWith

olupOP•14mo ago

the doc does not mention that, hence my confusion

Angelelz•14mo ago

Oh, wait... You have an update from query Let me check that

olupOP•14mo ago

yes

Angelelz•14mo ago

Lol, here I am saying that you can do it and there's an open issue: https://github.com/drizzle-team/drizzle-orm/issues/208

GitHub

Support UPDATE FROM · Issue #208 · drizzle-team/drizzle-orm

olupOP•14mo ago

a ok - so for this query I won't be able to use the query builder right ?

Angelelz•14mo ago

Unfortunately no

olupOP•14mo ago

so no way to map the returning statement to js object ?

Angelelz•14mo ago

The problem is the from clause No, you have to do it manually

olupOP•14mo 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

Gaming

Programming

mapWith, any special things to make it work ?