DT
Drizzle Team•2y ago
Ramazan

Prepend raw fields with table/subquery name in rendered SQL

Currently, it doesn't seem like raw fields in a select statement hold any information on the table/subquery they're used in. This results in subsequent usage of these fields elsewhere rendering to SQL as "field_name" instead of "alias"."field_name". This actually causes issues when you have overlapping field names in sql in your joins. Example: 
const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sq.date, transactions.date));
const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sq.date, transactions.date));
will result in an error 
error: column reference "date" is ambiguous
error: column reference "date" is ambiguous
with the following workaround 
const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`));
const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`));
This behavior persists through subsequent subqueries where 
const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const sq2 = db
  .select({
    date: transactions.date,
    balanceDate: balances.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`))
  .as("sq2");

const result = await db.select({
  balanceDate: sq2.balanceDate,
  anotherDate: someTable.date // also using sql`...`.as("date")
}).from(sq2).innerJoin(someTable, ...);
const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const sq2 = db
  .select({
    date: transactions.date,
    balanceDate: balances.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`))
  .as("sq2");

const result = await db.select({
  balanceDate: sq2.balanceDate,
  anotherDate: someTable.date // also using sql`...`.as("date")
}).from(sq2).innerJoin(someTable, ...);
will result in a similar error due to the ambiguity between someTable.date and sq2.balanceDate.
2 Replies
Angelelz
Angelelz•2y ago
I've seen at least one other person pointing to the behavior before. https://discordapp.com/channels/1043890932593987624/1148051375167643698 I just cheched github and the issue is there, and you actually commented on it. So it is and issue. Looks like the current workaround might look like this: https://discordapp.com/channels/1043890932593987624/1148051375167643698/1148079123017760828
Ramazan
RamazanOP•2y ago
That doesn't quite work in my case, where the raw sql field is in a subquery/CTE and collides with the name of of another table/subquery/cte. You have to prefix it with the subquery/CTE name manually every time it's used or keep track of and use different names for every such field like .as("balances_date") and .as("transaction_date").

Did you find this page helpful?