Inconsistent Aliases in SQL Queries with findMany and `sql`` Template Literals
Hi,
I am encountering an issue with table aliases when using SQL queries. I have a helper function that constructs WHERE clauses, and everything works fine unless the table name is in snake_case.
I’m using sql
template literals to build the WHEREclause andsql.join` to join the conditions together. Here’s an example:
sql`AND ${table[k as keyof typeof table]} LIKE '%${sql.raw(v.like)}%'`
This is how I join them and return the result in a variable:
where: q?.where ? sql.join(getWhere(table)(q.where)) : undefined
At the end, I use the findMany function to take advantage of the include option. For example:
await tx.query.inventoryTransactions.findMany({ ...q, limit, offset });
However, when executing the query, I run into an issue related to the table names. The query generated looks like this:
SELECT `id`, `note`, `type`, `status`, `supplier_invoice_number`, `order_number`, `plan_modality_activity_school_id`, `rejection_note`, `approve_note`, `created_at`, `updated_at`
FROM `inventory_transactions` `inventoryTransactions`
WHERE (`inventory_transactions`.`supplier_invoice_number` LIKE '%12%' OR `inventory_transactions`.`order_number` LIKE '%12%')
LIMIT ? -- params: [10]
When using findMany, the query automatically adds an alias (inventoryTransactions), but the `sql template does not.
On the other hand, if I use a normal SELECT, it works fine because no alias is added. For example:
await tx.select({ count: sql<number>count(*) })
.from(inventoryTransactions)
.where(q.where);
This generates:
SELECT count(*)
FROM inventory_transactions
WHERE (inventory_transactions.supplier_invoice_number LIKE '%12%' OR inventory_transactions.order_number LIKE '%12%')
Is there a way to ensure consistency with aliases, or am I doing something wrong?
Thanks in advance for any help.0 Replies