Drizzle Team•2y ago

Conditional logic within template strings not supported on raw MySQL query

I am trying to execute raw SQL query with some conditional logic but it always fails. It's working fine with mysql2 driver.

    const [res] = await db.execute(sql
`
SELECT 
  COUNT(*) as total
FROM
  te_tenants
WHERE
  1 = 1
  ${query ? `AND (te_name LIKE '%${query}%' OR te_code LIKE '%${query}%') ` : ``}
  ${filterTenantIds ? `AND te_id IN (${allowedTenantIds.join(", ")}) ` : ``}
  ${isConduitType ? `AND (te_conduit_prefix != '' OR te_conduit_prefix != NULL) `: ``}
  ${isPartnerAdmin ? `AND te_re_id = ${partnerId}` : ``};
`);

    const [res] = await db.execute(sql
`
SELECT 
  COUNT(*) as total
FROM
  te_tenants
WHERE
  1 = 1
  ${query ? `AND (te_name LIKE '%${query}%' OR te_code LIKE '%${query}%') ` : ``}
  ${filterTenantIds ? `AND te_id IN (${allowedTenantIds.join(", ")}) ` : ``}
  ${isConduitType ? `AND (te_conduit_prefix != '' OR te_conduit_prefix != NULL) `: ``}
  ${isPartnerAdmin ? `AND te_re_id = ${partnerId}` : ``};
`);

Generated Query:

SELECT 
  COUNT(*) as total                                                                                                    FROM
  te_tenants                                                                                                           WHERE
  1 = 1
  ?
  ?
  ?
  ?;

SELECT 
  COUNT(*) as total                                                                                                    FROM
  te_tenants                                                                                                           WHERE
  1 = 1
  ?
  ?
  ?
  ?;

Params:

["", "", "", ""]

["", "", "", ""]

9 Replies

Andrii Sherman•2y ago

First question, are you using raw query on purpose or you just didn't find how to make this query in drizzle? maybe I can help you to make it work with drizzle

jivanOP•2y ago

That would be great. I didn't find a way to convert the above query into drizzle. Could you also please tell me why the above fails as I will be doing a lot of raw queries in the future. Really appreciate your quick reply. Thanks, man.

Andrii Sherman•2y ago

Don't look and logic, just a small example on how to combine filter and pass them to .where + how to setup count() We didn't have all of that in docs yet, but it will be very soon Ping me if everything worked or not

import { and, eq, lt, or, SQL, sql } from "drizzle-orm";

const where: SQL[] = []

const id = ...
if (id > 3) {
  where.push(eq(schema.users.id, id))
}

if (id > 3) {
  where.push(or(lt(schema.users.id, 3), eq(schema.users.id, 3))!)
}

  await db.select({ count: sql<number>`count(*)`.mapWith(Number)}).from(schema.users).where(and(...where))

import { and, eq, lt, or, SQL, sql } from "drizzle-orm";

const where: SQL[] = []

const id = ...
if (id > 3) {
  where.push(eq(schema.users.id, id))
}

if (id > 3) {
  where.push(or(lt(schema.users.id, 3), eq(schema.users.id, 3))!)
}

  await db.select({ count: sql<number>`count(*)`.mapWith(Number)}).from(schema.users).where(and(...where))

I don't know why it;s not formatting worked

jivanOP•2y ago

Will try it out and let you know.

const sqlQuery = `SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sql`${sqlQuery}`);

const sqlQuery = `SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sql`${sqlQuery}`);

Something this simple is also failing. I am using deno, so maybe something to do with mysql driver. But, I haven't tested it with node.

Andrii Sherman•2y ago

yeah, you need to write

const sqlQuery = sql`SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sqlQuery);

const sqlQuery = sql`SELECT COUNT(*) as total FROM te_tenants WHERE 1 = 1;`;
const [res] = await db.execute(sqlQuery);

in this case it will work well

jivanOP•2y ago

Yeah, it works, but fails with any conditional logic in it. I was able to convert the above query to drizzle with your example. Thank you for your help.

adoublef•2y ago

@a_sherman I wanted to ask if those functions from the drizzle-orm package can work outside of Node/Drizzle? I am a Deno user that is looking to try and experiement with Turso and was thinking if I may be able to add this

Andrii Sherman•2y ago

It should work there. We didn't have a chance to try out Deno, but you can give it a shot

adoublef•2y ago

@Andrew Sherman ah I gave it ago and while I can import from Esm.sh looks like there's some node specific code that when imported breaks Deno. I think it's still something I'd like to dive into more this week so if I gain any traction will notify through an issue

Gaming

Programming

Conditional logic within template strings not supported on raw MySQL query