Relations
Available relations are:
belongsTo: a one belongs to another, the belonging one includes a referencing column.
Example: a payment containinguser_id
andorder_id
belongs to a user and an order.hasOne: is similar to "belongs to," but the referencing column is on the other side.
Example: an order has one payment, theorder_id
column is on the payment side.- through: when many tables are connected using
belongsTo
orhasOne
, the first of them can connect to the last by usinghasOne: though
.
Example: a user has one profile, an order belongs to a user, an order can have one profile through a user.through
can stack many levels, so a payment that belongs to an order can also have a profile through the order.
- through: when many tables are connected using
hasMany: a one has many others, the others include a column referencing the one.
Example: a user has many orders and payments.- through: when many tables are connected, and at least one of the connection is
hasMany
orhasAndBelongsToMany
,
the first of them can connect to the last by usinghasMany: though
.
Example: an order has many products, a user has many orders, a user can have multiple ordered products though orders.
A payment belongs to an order, it also can have many ordered products though the related order.
- through: when many tables are connected, and at least one of the connection is
hasAndBelongsToMany: a many-to-many relation, it is maintained by having an additional table where a single row is pointing to both parties.
Examples: movies and actors, products and categories.- vs.
hasMany: though
: we can sayhasAndBelongsToMany
is a simple case ofhasMany: though
, where the join table is managed automatically, you don't have to store any additional info in it. You can usehasAndBelongsToMany
between products and categories, but later if there is a need to store, let's say, info about who assigned the category to the product in the joining row, then it should be handled withhasMany: through
.
- vs.
belongsTo
belongsTo
is for a table which has a column pointing to another table.
For example, Book
belongs to Author
:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Author = Selectable<AuthorTable>;
export class AuthorTable extends BaseTable {
readonly table = 'author';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
}));
}
export type Book = Selectable<BookTable>;
export class BookTable extends BaseTable {
readonly table = 'book';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
title: t.string(),
// book has a column pointing to the author table
authorId: t.integer(),
}));
relations = {
author: this.belongsTo(() => AuthorTable, {
// required is affecting on TS type of returned record
required: true,
// columns of this table for the connection
columns: ['authorId'],
// columns of the related table to connect with
references: ['id'],
}),
};
}
hasOne
hasOne
association indicates that one other table has a reference to this table. That table can be fetched through this association.
This association adds all the same queries and abilities as belongsTo
, only difference is the reference column is located in another table.
For example, if each supplier in your application has only one account, you'd declare the supplier table like this:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Supplier = Selectable<SupplierTable>;
export class SupplierTable extends BaseTable {
readonly table = 'supplier';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
brand: t.string(),
// here are no reference columns for an Account
}));
relations = {
account: this.hasOne(() => AccountTable, {
// required is affecting on TS type of returned record
required: true,
// the same as in belongsTo:
// columns of this table for the connection
columns: ['id'],
// columns of the related table to connect with
references: ['supplierId'],
}),
};
}
export type Account = Selectable<AccountTable>;
export class AccountTable extends BaseTable {
readonly table = 'account';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
// Account has a column pointing to Supplier:
supplierId: t.integer(),
}));
}
hasOne through
A hasOne through
association sets up a one-to-one connection with another table. This association indicates that the declaring table can be matched with one instance of another table by proceeding through a third table.
hasOne through
gives the same querying abilities as a regular hasOne
, but without nested create functionality.
For example, if each supplier has one account, and each account is associated with one account history, then the supplier table could look like this:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Supplier = Selectable<SupplierTable>;
export class SupplierTable extends BaseTable {
readonly table = 'supplier';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
brand: t.string(),
}));
relations = {
account: this.hasOne(() => AccountTable, {
required: true,
columns: ['id'],
references: ['supplierId'],
}),
accountHistory: this.hasOne(() => AccountTable, {
required: true,
// a previously defined relation name
through: 'account',
// name of a relation in the Account table
source: 'accountHistory',
}),
};
}
export type Account = Selectable<AccountTable>;
export class AccountTable extends BaseTable {
readonly table = 'account';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
// Account has a column pointing to Supplier:
supplierId: t.integer(),
}));
relations = {
accountHistory: this.hasOne(() => AccountHistoryTable, {
required: true,
columns: ['id'],
references: ['accountId'],
}),
};
}
export type AccountHistory = Selectable<AccountHistoryTable>;
export class AccountHistoryTable extends BaseTable {
readonly table = 'accountHistory';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
data: t.text(),
// column pointing to the Account
accountId: t.integer(),
}));
relations = {
account: this.belongsTo(() => AccountTable, {
required: true,
columns: ['accountId'],
references: ['id'],
}),
};
}
hasMany
A hasMany
association is similar to hasOne
but indicates a one-to-many connection with another table. You'll often find this association on the "other side" of a belongsTo
association. This association indicates that each instance of the table has zero or more instances of another table.
For example, in an application containing authors and books, the author table could be declared like this:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Author = Selectable<AuthorTable>;
export class AuthorTable extends BaseTable {
readonly table = 'author';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
}));
relations = {
books: this.hasMany(() => BookTable, {
// columns of this table to use for connection
columns: ['id'],
// columns of the related table to connect with
references: ['authorId'],
}),
};
}
export type Book = Selectable<BookTable>;
export class BookTable extends BaseTable {
readonly table = 'book';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
title: t.string(),
// book has a column pointing to the author table
authorId: t.integer(),
}));
}
hasMany through
A hasMany though
association is often used to set up a many-to-many connection with another table. This association indicates that the declaring table can be matched with zero or more instances of another table by proceeding through a third table.
hasMany through
gives the same querying abilities as a regular hasMany
but without nested create functionality.
For example, consider a medical practice where patients make appointments to see physicians. The relevant association declarations could look like this:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Physician = Selectable<PhysicianTable>;
export class PhysicianTable extends BaseTable {
readonly table = 'physician';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
}));
relations = {
appointments: this.hasMany(() => AppointmentTable, {
// columns of this table to use for connection
columns: ['id'],
// columns of the related table to connect with
references: ['authorId'],
}),
patients: this.hasMany(() => PatienTable, {
// previously defined relation name
through: 'appointments',
// name of relation in Appointment table
source: 'patient',
}),
};
}
export type Appointment = Selectable<AppointmentTable>;
export class AppointmentTable extends BaseTable {
readonly table = 'appointment';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
appointmentDate: t.datetime(),
// column references physician:
physicianId: t.integer(),
// column references patient:
patientId: t.integer(),
}));
relations = {
physician: this.belongsTo(() => PhysicianTable, {
columns: ['physycianId'],
references: ['id'],
}),
patient: this.belongsTo(() => PatientTable, {
columns: ['patientId'],
references: ['id'],
}),
};
}
export type Patient = Selectable<PatientTable>;
export class PatientTable extends BaseTable {
readonly table = 'patient';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
}));
relations = {
appointments: this.hasMany(() => AppointmentTable, {
columns: ['id'],
references: ['patientId'],
}),
physicians: this.hasMany(() => PhysicianTable, {
// previously defined relation name
through: 'appointments',
// name of relation in Appointment table
source: 'physician',
}),
};
}
hasAndBelongsToMany
A hasAndBelongsToMany
association creates a direct many-to-many connection with another table. The table in between must exist in the database, but you can skip defining a table class in the code.
This association indicates that each instance of the declaring table refers to zero or more instances of another table, and vice-versa.
If snakeCase: true
config is set, you can write join table column in camelCase
, they will be translated to snake_case
.
For example, if your application includes posts and tags, with each post having many tags and each tag appearing in many posts, you could declare the tables this way:
import { Selectable } from 'orchid-orm';
import { BaseTable } from './baseTable';
export type Post = Selectable<PostTable>;
export class PostTable extends BaseTable {
readonly table = 'post';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
title: t.string(),
}));
relations = {
tags: this.hasAndBelongsToMany(() => TagTable, {
// columns of this table to connect with the middle table
columns: ['id'],
// columns of the middle table to connect the columns to
references: ['postId'],
through: {
// name of the middle table
table: 'postTag',
// columns of the middle table to connect to the related table
columns: ['tagId'],
// columns of the related table to connect the middle table to
references: ['id'],
},
}),
};
}
export type Tag = Selectable<TagTable>;
export class TagTable extends BaseTable {
readonly table = 'tag';
columns = this.setColumns((t) => ({
id: t.identity().primaryKey(),
name: t.string(),
}));
relations = {
posts: this.hasAndBelongsToMany(() => PostTable, {
columns: ['id'],
references: ['tagId'],
through: {
table: 'postTag',
columns: ['postId'],
references: ['id'],
},
}),
};
}
on - relation with a condition
All relation kinds support the on
option to specify conditions.
Adding on
affects two things:
- all* the queries of the relation are using the condition to filter records.
- when creating a related record, it automatically includes the values of
on
.
- except
disconnect
in belongs, the record that belongs to another record will be disconnected even if the related record doesn't match theon
conditions.
- except
export class UserTable extends BaseTable {
readonly table = 'user';
// ...snip
relations = {
posts: this.hasMany(() => PostTable, {
columns: ['id'],
references: ['user_id'],
}),
draftPosts: this.hasMany(() => PostTable, {
columns: ['id'],
references: ['user_id'],
on: {
status: 'draft',
},
}),
};
}
// later in the code:
// select draft posts:
await db.user.select({
draftPosts: (q) => q.draftPosts,
equivalent: (q) => q.posts.where({ status: 'draft' }),
});
// the created post is populated with `on` values automatically:
await db.user.find(id).update({
draftPosts: {
create: [{ title: '...', body: '...' }],
},
});
// equivalent without `on`:
await db.user.find(id).update({
posts: {
create: [{ title: '...', body: '...', status: 'draft' }],
},
});