[BUG]: sql`` interpolates the wrong table name when used in extras

[fknop]

What version of drizzle-orm are you using?

0.29.3

What version of drizzle-kit are you using?

0.20.12

Describe the Bug

I'm using sql` to add a subquery for a SELECT COUNT(*) in theextras` field of the query API. Here is the snippet:

 const project = await db.query.projects.findFirst({
      columns: {
        id: true,
        name: true,
      },
      with: {
        locales: {
          orderBy: (table) => desc(table.isDefault),
        },
        branches: {
          orderBy: (table) => asc(table.name),
          extras: {
            translationsCount:
              sql<number>`(SELECT COUNT(*) FROM ${translationsTable} WHERE ${translationsTable.branchId} = ${projectBranches.id})`
                .mapWith(Number)
                .as('translationsCount'),
          },
        },
      },
      where: (table) => eq(table.id, projectId),
    })

Example of the generated query:

select "projects"."id", "projects"."name", "projects_locales"."data" as "locales", "projects_branches"."data" as "branches" from "projects" left join lateral (select coalesce(json_agg(json_build_array("projects_locales"."id", "projects_locales"."project_id", "projects_locales"."is_default", "projects_locales"."code", "projects_locales"."created_at") order by "projects_locales"."is_default" desc), '[]'::json) as "data" from (select * from "project_locales" "projects_locales" where "projects_locales"."project_id" = "projects"."id" order by "projects_locales"."is_default" desc) "projects_locales") "projects_locales" on true left join lateral (select coalesce(json_agg(json_build_array("projects_branches"."id", "projects_branches"."project_id", "projects_branches"."name", "projects_branches"."github_pull_request", "projects_branches"."created_at", (SELECT COUNT(*) FROM "translations" WHERE "projects_branches"."branch_id" = "projects_branches"."id")) order by "projects_branches"."name" asc), '[]'::json) as "data" from (select * from "project_branches" "projects_branches" where "projects_branches"."project_id" = "projects"."id" order by "projects_branches"."name" asc) "projects_branches") "projects_branches" on true where "projects"."id" = $1 limit $2 -- params: ["c1c35c25-be87-450a-a2c9-73e019a41547", 1]

The generated query generates project_branches.branch_id for the ${translationsTable.branchId} interpolation instead of translations.branch_id. I've tried using the same interpolation outside of extras using this snippet:

   const query =
      sql<number>`SELECT COUNT(*) FROM ${translationsTable} WHERE ${translationsTable.branchId} = ${projectBranches.id}`.getSQL()
    const pgDialect = new PgDialect()
    console.log(pgDialect.sqlToQuery(query))

And it properly generates the following SQL:

SELECT COUNT(*) FROM "translations" WHERE "translations"."branch_id" = "project_branches"."id"

I Managed to reproduce in this code sandbox here with a simple user - issue - comment schema https://codesandbox.io/p/devbox/kind-galois-tk5yg6?file=%2Fapp%2Fpage.tsx

It seems to only happen at least one level of with deep

Expected behavior

The sql`` properly interpolates the table name

Environment & setup

No response

[TakenPilot]

Here is a temporary workaround if someone needs it.

export const mysqlDialect = new MySqlDialect();
/**
 * @example
 * db.query.locations.findFirst({
 *  extras: (fields) => {
 *     return {
 *      ...countRelation("equipmentCount", fields.id, equipmentLocations.locationId),
 *     };
 *   },
 * };
 */
export const countRelation = <const T extends string>(name: T, fieldId: MySqlColumn, refId: MySqlColumn): { [Key in T]: SQL.Aliased<number> } => {
  const sqlChunks = sql`(SELECT COUNT(*) FROM ${refId.table} WHERE ${refId} = ${fieldId})`;
  const rawSQL = sql.raw(mysqlDialect.sqlToQuery(sqlChunks).sql);

  return {
    [name]: rawSQL.mapWith(Number).as(name),
  } as { [Key in T]: SQL.Aliased<number> };
};

Might be doing something extra, but it works and still gives type safety.