Drizzle Team•2mo ago

How to convert filters (e.g. eq(), ne(), lte(), like()) into SQL string, for use in sql.raw()

I'm trying to use LEFT JOIN LATERAL, but I have a lot of filters already created with drizzle. I would like to use them in my SQL:

  const query = sql.raw(`SELECT user.id, message.received_at FROM user LEFT JOIN LATERAL (SELECT message.user_id, message.received_at FROM message WHERE message.user_id = user.id ORDER BY message.received_at LIMIT 1) message on user.id=message.user_id`);
  // TODO use other filters too: `AND ${filtersExceptTime?.getSQL()}` doesn't work

  const result = await db.execute(query);

  const query = sql.raw(`SELECT user.id, message.received_at FROM user LEFT JOIN LATERAL (SELECT message.user_id, message.received_at FROM message WHERE message.user_id = user.id ORDER BY message.received_at LIMIT 1) message on user.id=message.user_id`);
  // TODO use other filters too: `AND ${filtersExceptTime?.getSQL()}` doesn't work

  const result = await db.execute(query);

3 Replies

Ben-xD•2mo ago

This gets me halfway there: https://orm.drizzle.team/docs/sql#convert-sql-to-string-and-params But the problem is the params I passed into the filters (e.g. eq(user.id, userId)) is not added to the string.

Drizzle ORM - Magic sql`` operator

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

rphlmr ⚡•2mo ago

Did you try with just sql (no raw). In the template string you can use all drizzle expressions (and inject sql.raw too if needed but beware, this is an unsafe injection, prone to sql injection)

Ben-xD•2mo ago

I tried sql too, but it errors with error: syntax error at or near ")"

  const query = db.select().from(user).innerJoin(message, eq(user.id, message.userId))
  .where(sql`
    message.received_at = (
      SELECT MIN(received_at)
      FROM message
      WHERE message.user_id = user.id
      AND message.received_at >= ${queryParams.startTime}
      AND message.received_at <= ${queryParams.endTime}
    )
    `).orderBy(desc(message.receivedAtS));

  const query = db.select().from(user).innerJoin(message, eq(user.id, message.userId))
  .where(sql`
    message.received_at = (
      SELECT MIN(received_at)
      FROM message
      WHERE message.user_id = user.id
      AND message.received_at >= ${queryParams.startTime}
      AND message.received_at <= ${queryParams.endTime}
    )
    `).orderBy(desc(message.receivedAtS));

ahh i know why that happens. Basically, the filter queryParams.startTime is sometimes undefined: it's number | undefined. I just need to use sql a bit better

Gaming

Programming

How to convert filters (e.g. eq(), ne(), lte(), like()) into SQL string, for use in sql.raw()