Sparticuz Posts - Answer Overflow

Sparticuz

•Created by Sparticuz on 2/6/2025 in #help

How to specify types for raw sql statement?

How do I specify the types for a raw sql statement? I have the following code, but I have to override the types to unknown before I can get the types out.

interface SQLResults {
  numResponses: number;
  opsTeam: number;
}
const csFeedbackMTD = sql<SQLResults>`
  SELECT a.ops_team AS opsTeam, COUNT(DISTINCT(a.customer_fk)) AS numResponses
  FROM (
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} IN(8, 14) AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    UNION ALL
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} = 6 AND
      ${eventsTable.scoredByEntity} = 1 AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    ) a
  LEFT OUTER JOIN ${customersTable} ON a.customer_fk = ${customersTable.id}
  WHERE
    ${customersTable.startDate} < DATE_SUB(CURDATE(),INTERVAL 10 DAY)
  AND
    a.ops_team IS NOT NULL
  GROUP BY a.ops_team`;

  // This type override is wrong, this is because the database.execute type is wrong
  const [result] = (await database.execute(csFeedbackMTD)) as unknown as [
    SQLResults[],
  ];

interface SQLResults {
  numResponses: number;
  opsTeam: number;
}
const csFeedbackMTD = sql<SQLResults>`
  SELECT a.ops_team AS opsTeam, COUNT(DISTINCT(a.customer_fk)) AS numResponses
  FROM (
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} IN(8, 14) AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    UNION ALL
    SELECT ${eventsTable.customer}, ${eventsTable.opsTeam}
    FROM ${eventsTable}
    WHERE
      ${eventsTable.type} = 6 AND
      ${eventsTable.scoredByEntity} = 1 AND
      ${eventsTable.effectiveDate} BETWEEN ${format(startDate, "yyyy-MM-dd")} AND ${format(endDate, "yyyy-MM-dd")}
    ) a
  LEFT OUTER JOIN ${customersTable} ON a.customer_fk = ${customersTable.id}
  WHERE
    ${customersTable.startDate} < DATE_SUB(CURDATE(),INTERVAL 10 DAY)
  AND
    a.ops_team IS NOT NULL
  GROUP BY a.ops_team`;

  // This type override is wrong, this is because the database.execute type is wrong
  const [result] = (await database.execute(csFeedbackMTD)) as unknown as [
    SQLResults[],
  ];

I've tried putting the <SQLResults> as database.execute<SQLResults>(csFeedbackMTD) as well, but the types don't seem to be what I'm getting back.

2 replies

Gaming

Programming