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

mysql2

mysql2

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}` : ``};
`);

    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
  ?
  ?
  ?
  ?;

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

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

Params:

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

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

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

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

Andrii Sherman•5/19/23, 12:15 PM

First question, are you using raw query on purpose or you just didn't find how to make this query in drizzle?

Andrii Sherman•5/19/23, 12:16 PM

maybe I can help you to make it work with drizzle

jivanOP•5/19/23, 12:19 PM

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•5/19/23, 12:30 PM

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))

Andrii Sherman•5/19/23, 12:32 PM

I don't know why it;s not formatting

Andrii Sherman•5/19/23, 12:34 PM

worked

jivanOP•5/19/23, 12:37 PM

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 denodeno, so maybe something to do with mysql driver.
But, I haven't tested it with nodenode.

Andrii Sherman•5/19/23, 12:43 PM

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);

Andrii Sherman•5/19/23, 12:43 PM

in this case it will work well

jivanOP•5/19/23, 12:44 PM

Yeah, it works, but fails with any conditional logic in it.

jivanOP•5/19/23, 1:02 PM

I was able to convert the above query to drizzle with your example.
Thank you for your help.

adoublef•8/1/23, 5:19 PM

@a_sherman I wanted to ask if those functions from the drizzle-ormdrizzle-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

Aadoublef @a_sherman I wanted to ask if those functions from the `drizzle-orm` package can...

Andrii Sherman•8/2/23, 10:28 AM

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

adoublef•8/2/23, 3:01 PM

@Andrew Sherman ah I gave it ago and while I can import from Esm.shEsm.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

Conditional logic within template strings not supported on raw MySQL query

Similar Threads

Similar Threads

Similar Threads