Joins | Alepha

alepha@docs:~/docs/guides/persistence$
cat 6-joins.md | pretty
2 min read
Last commit: 
#JoinsRelations between tables are not declared on $entity. Instead, use the with option in repository query methods to perform joins at query time.
typescript
import { t } from "alepha";import { $entity, $repository, db } from "alepha/orm";
#Defining Related EntitiesUse db.ref() to create foreign key columns that reference another entity:
typescript
const teams = $entity({  name: "teams",  schema: t.object({    id: db.primaryKey(),    name: t.text(),    country: t.text(),  }),}); const players = $entity({  name: "players",  schema: t.object({    id: db.primaryKey(),    teamId: db.ref(t.optional(t.integer()), () => teams.cols.id),    name: t.text(),    position: t.text(),  }),});
See Special Columns for db.ref() options like onDelete and onUpdate.
#Basic JoinUse the with option on getOne, findOne, findMany, or paginate to join related tables. The result includes the joined data as a nested object.
typescript
class PlayerService {  players = $repository(players);   async getPlayerWithTeam(playerId: number) {    return await this.players.getOne({      where: { id: { eq: playerId } },      with: {        team: {          join: teams,          on: ["teamId", teams.cols.id],        },      },    });    // result.name     → "Messi"    // result.team.name → "FC Barcelona"  }}
The on tuple maps [localColumn, foreignEntity.cols.foreignColumn].
#Join TypesThree join types are supported. The default is "left".
typescript
with: {  team: {    type: "left",   // default — include rows even if no match (team will be undefined)    join: teams,    on: ["teamId", teams.cols.id],  },}
typescript
with: {  team: {    type: "inner",  // exclude rows that have no matching team    join: teams,    on: ["teamId", teams.cols.id],  },}
typescript
with: {  team: {    type: "right",  // include all teams, even if no player references them    join: teams,    on: ["teamId", teams.cols.id],  },}
With a left join, if the foreign key is NULL or there is no matching row, the joined field is undefined:
typescript
const freeAgent = await this.players.getOne({  where: { name: { eq: "Free Agent" } },  with: {    team: { join: teams, on: ["teamId", teams.cols.id] },  },});// freeAgent.team → undefined
With an inner join, that row would be excluded entirely.
#Filtering on Joined TablesUse the join alias in the where clause to filter by columns from the joined table:
typescript
const spanishPlayers = await this.players.findMany({  with: {    team: {      join: teams,      on: ["teamId", teams.cols.id],    },  },  where: {    team: {      country: { eq: "Spain" },    },  },});
Combine base and joined table filters with and / or:
typescript
const results = await this.players.findMany({  with: {    team: {      join: teams,      on: ["teamId", teams.cols.id],    },  },  where: {    and: [      { position: { eq: "Forward" } },      { team: { country: { eq: "Spain" } } },    ],  },});
typescript
const results = await this.players.findMany({  with: {    team: {      join: teams,      on: ["teamId", teams.cols.id],    },  },  where: {    or: [      { goals: { gte: 200 } },      { team: { country: { eq: "France" } } },    ],  },});
All standard where clause operators work on joined table columns.
#Multiple JoinsJoin several tables at the same level:
typescript
const result = await this.users.getOne({  where: { id: { eq: userId } },  with: {    profile: {      join: profiles,      on: ["id", profiles.cols.userId],    },    city: {      join: cities,      on: ["cityId", cities.cols.id],    },  },});// result.profile.bio → "Tech lead"// result.city.name   → "Toronto"
Each join can have a different type:
typescript
with: {  profile: {    type: "inner", // must have a profile    join: profiles,    on: ["id", profiles.cols.userId],  },  city: {    type: "left", // city is optional    join: cities,    on: ["cityId", cities.cols.id],  },},
#Nested JoinsNest with inside a join to follow relationships deeper:
typescript
// user → city → countryconst result = await this.users.getOne({  where: { id: { eq: userId } },  with: {    city: {      join: cities,      on: ["cityId", cities.cols.id],      with: {        country: {          join: countries,          on: ["countryId", countries.cols.id],        },      },    },  },});// result.city.name          → "Toronto"// result.city.country.name  → "Canada"
Nesting works to arbitrary depth (3+ levels tested):
typescript
// post → author → city → countryconst post = await this.posts.getOne({  where: { id: { eq: postId } },  with: {    author: {      join: users,      on: ["authorId", users.cols.id],      with: {        city: {          join: cities,          on: ["cityId", cities.cols.id],          with: {            country: {              join: countries,              on: ["countryId", countries.cols.id],            },          },        },      },    },  },});// post.author.city.country.code → "CA"
Filtering also works on nested joins:
typescript
where: {  city: {    country: {      code: { eq: "CA" },    },  },},
#Self-Referencing JoinsWhen an entity references itself (e.g. a managerId pointing to the same users table), use .alias() to disambiguate:
typescript
const users = $entity({  name: "users",  schema: t.object({    id: db.primaryKey(),    name: t.text(),    managerId: db.ref(t.optional(t.integer()), () => users.cols.id),  }),}); const manager = users.alias("manager"); const result = await this.users.getOne({  where: { id: { eq: bobId } },  with: {    manager: {      join: manager,      on: ["managerId", manager.cols.id],    },  },});// result.manager.name → "Alice"
Aliases also work for nested self-references:
typescript
const manager = users.alias("manager");const managerManager = users.alias("manager_manager"); const result = await this.users.getOne({  where: { id: { eq: dianaId } },  with: {    manager: {      join: manager,      on: ["managerId", manager.cols.id],      with: {        manager: {          join: managerManager,          on: ["managerId", managerManager.cols.id],        },      },    },  },});// result.manager.name          → "Bob"// result.manager.manager.name  → "Alice"
More generally, whenever the same table appears more than once in a query (even through different join paths), use .alias() for each additional occurrence.
#SQL Join ConditionsFor complex join conditions beyond simple column equality, pass a raw SQL expression instead of the tuple:
typescript
import { sql } from "alepha/orm"; with: {  profile: {    join: profiles,    on: sql`${users.cols.id} = ${profiles.cols.userId}`,  },},
Note: raw SQL join conditions are only supported on PostgreSQL. SQLite requires the tuple syntax.
#Joins with Paginationpaginate supports with just like findMany:
typescript
const page = await this.players.paginate(  { page: 0, size: 10 },  {    with: {      team: {        join: teams,        on: ["teamId", teams.cols.id],      },    },    orderBy: { column: "name", direction: "asc" },  },);// page.content[0].team.name → "FC Barcelona"
#LimitationsNo one-to-many joins. The with option produces one-to-one joins (each row gets one joined object). For one-to-many relationships (e.g. a user's posts), run a separate query on the child table.
No orderBy on joined columns. Sorting is limited to columns on the base table.
No columns selection on joined tables. The full joined entity is always returned.
No aggregate queries with joins. Use repository.aggregate() separately or raw SQL via repository.query().
SQL join conditions require PostgreSQL. SQLite only supports the tuple syntax ["localCol", entity.cols.foreignCol].
On This Page
No headings found...