aliases

11 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 from drizzle-orm/pg-core to create table aliases. This is useful for self-referencing relationships like joining a table to itself. For example, to alias an employees table as manager: const manager = alias(employees, "manager");

Column aliasing with .as() method

Use the .as() method on columns to alias them in SELECT statements. This maps to the SQL AS keyword and controls the name of the column in the query result. For example: users.name.as("lower_name") produces "name" as "lower_name" in SQL.

Column aliasing with sql expressions

You can also use .as() with sql expressions to alias computed columns. For example: sql<string>`lower(${users.name})`.as("lower_name") produces lower("name") as "lower_name" in SQL.

Subquery aliasing with .as() method

When using a subquery as a data source, you must provide an alias using .as(). This allows you to reference the subquery's columns in the outer query. For example: const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');

Subqueries in joins

Subqueries can be used in join operations. Reference the subquery alias and its columns in the join condition. For example: .leftJoin(sq, eq(users.id, sq.id)) where sq is a subquery alias created with .as('sq').

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. For example: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));

CTE with sql expressions aliasing

When using sql expressions inside a CTE, you need to alias them with .as() to reference them in the outer query. For example: db.$with('sq').as(db.select({ name: sql<string>`upper(${users.name})`.as('name') }).from(users))

Self-join with table alias example

Example of joining employees table to itself to find 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)); ```

Table aliases for self-joins

Use the alias function from drizzle-orm/pg-core to create table aliases for self-joins. Example: import { alias } from 'drizzle-orm/pg-core'; const parent = alias(user, 'parent'); const result = await db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId)) generates SQL select ... from "user" left join "user" "parent" on "parent"."id" = "user"."parent_id".

Column aliases via .as() method in v1.0

Column aliasing is now supported via the `.as()` method on columns. Example: `db.select({ age: users.age.as('ageOfUser'), id: users.id.as('userId') }).from(users).orderBy(asc(users.id.as('userId')))`.

Column alias example for v1.0

Example of column aliasing in v1.0: `const query = db.select({ age: users.age.as('ageOfUser'), id: users.id.as('userId') }).from(users).orderBy(asc(users.id.as('userId')));`

Give your agent this brain