jivan Posts - Answer Overflow

jivan

•Created by jivan on 5/19/2023 in #help

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:

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

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

15 replies

Gaming

Programming