aliases

22 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Table aliasing with alias function

Use the `alias()` function to alias tables. This is useful for self-referencing relationships, such as joining a table to itself. The syntax is: `const aliasedTable = alias(originalTable, "aliasName");`. Example: `const manager = alias(employees, "manager");` creates an alias of the employees table named "manager", which can then be used in joins like `.leftJoin(manager, eq(employees.managerId, manager.id))`.

Using subqueries in joins

Subqueries can be used in joins after being aliased with `.as()`. Example: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');` followed by `.leftJoin(sq, eq(users.id, sq.id))` to join the subquery with another table.

Table aliasing example with self-join

Example showing aliasing tables for a self-referencing relationship (employees and their managers): ```ts const manager = alias(employees, "manager"); await db.select({ employeeName: employees.name, managerName: manager.name, }) .from(employees) .leftJoin(manager, eq(employees.managerId, manager.id)); ``` This generates: `select "employees"."name", "manager"."name" from "employees" left join "employees" as "manager" on "employees"."manager_id" = "manager"."id";`

Column aliasing example

Example showing column aliasing in a select query: ```ts const result = await db.select({ id: users.id, lowerName: users.name.as("lower_name"), }).from(users); ``` This generates: `select "id", "name" as "lower_name" from "users";`

SQL expression aliasing example

Example showing aliasing a `sql` expression: ```ts const result = await db.select({ id: users.id, lowerName: sql<string>\`lower(${users.name})\`.as("lower_name"), }).from(users); ``` This generates: `select "id", lower("name") as "lower_name" from "users";`

Subquery join example

Example showing a subquery used in a join: ```ts const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id)); ``` This generates: `select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users" left join (select "id", "name", "age" from "users" where "users"."id" = 42) "sq" on "users"."id" = "sq"."id";`

CTE with SQL expression example

Example showing a CTE with an aliased `sql` expression: ```ts const sq = db.$with('sq').as(db.select({ name: sql<string>\`upper(${users.name})\`.as('name'), }).from(users)); const result = await db.with(sq).select({ name: sq.name }).from(sq); ``` This generates: `with "sq" as (select upper("name") as "name" from "users") select "name" from "sq";`

Table aliasing with alias function

Tables can be aliased using the `alias()` function from drizzle-orm. The syntax is `alias(table, 'aliasName')`. This is useful for self-referencing queries like when joining a table to itself for relationships such as employees and their managers. Example: `const manager = alias(employees, 'manager');` creates an alias of the employees table that can be referenced separately in joins.

CTE with sql expressions requires aliasing

When using sql expressions inside a CTE, those expressions must be aliased with `.as()` to be able to reference them in the outer query. Example: `sql<string>\`upper(${users.name})\`.as('name')` ensures the computed column can be selected as `sq.name` in the main query.

Table aliasing enables self-joins

Using the `alias()` function allows you to join the same table multiple times in a single query. This is particularly useful for self-referencing relationships such as employees querying their managers, where you need to reference the same table twice with different roles.

Column alias with sql expression example

Example: const result = await db.select({ id: users.id, lowerName: sql<string>`lower(${users.name})`.as('lower_name'), }).from(users); This produces SQL: select `id`, lower(`name`) as `lower_name` from `users`;

Subquery aliasing requirement

When using a subquery as a data source, you must provide an alias using the `.as()` method. This lets you reference the subquery's columns in the outer query.

Table aliasing with alias function

Table aliasing is done using the `alias` function imported from drizzle-orm/mysql-core. It is useful when you need to join the same table multiple times, such as in self-referencing relationships like employees and their managers. The alias function takes the table and a string alias name as parameters.

Table alias example with self-referencing join

Example: const manager = alias(employees, 'manager'); await db.select({ employeeName: employees.name, managerName: manager.name }).from(employees).leftJoin(manager, eq(employees.managerId, manager.id)); This produces SQL: select `employees`.`name`, `manager`.`name` from `employees` left join `employees` `manager` on `employees`.`manager_id` = `manager`.`id`;

Column aliasing with .as() method

Columns can be aliased using the `.as()` method on columns and sql expressions. This maps directly to the SQL AS keyword and lets you control the name of a column in the query result.

Subquery alias example

Example: const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq); This produces SQL: select `id`, `name`, `age` from (select `id`, `name`, `age` from `users` where `users`.`id` = 42) `sq`;

Subquery aliasing in joins example

Example: const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id)); This produces SQL: select `users`.`id`, `users`.`name`, `users`.`age`, `sq`.`id`, `sq`.`name`, `sq`.`age` from `users` left join (select `id`, `name`, `age` from `users` where `users`.`id` = 42) `sq` on `users`.`id` = `sq`.`id`;

CTE aliasing with db.$with()

Common table expressions (CTEs) are aliased using `db.$with('alias')`. The alias name is used to reference the CTE in the main query.

Purpose of CTEs in Drizzle and how they are created

CTEs (Common Table Expressions) in Drizzle are created using `db.$with('alias_name').as(query)`. CTEs allow you to define named subqueries that can be referenced in the main query. They are used with the `.with()` method to include them in a query.

CTE alias example

Example: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq); This produces SQL: with `sq` as (select `id`, `name`, `age` from `users` where `users`.`id` = 42) select `id`, `name`, `age` from `sq`;

CTE with sql expression aliasing

When using `sql` expressions inside a CTE, you need to alias them with `.as()` to be able to reference them in the outer query.

CTE with sql expression example

Example: const sq = db.$with('sq').as(db.select({ name: sql<string>`upper(${users.name})`.as('name'), }).from(users)); const result = await db.with(sq).select({ name: sq.name }).from(sq); This produces SQL: with `sq` as (select upper(`name`) as `name` from `users`) select `name` from `sq`;

Give your agent this brain