SQL Joins using ORM and Query Builders
SQL JOIN simple definition from (w3schoo.com)
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Then, look at a selection from the "Customers" table:
CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
and it will produce something like this:
OrderID CustomerName OrderDate
10308 Ana Trujillo Emparedados y helados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996
Lets do these Joins using ORM and query Builders
Using Knex
Knex.js (pronounced /kəˈnɛks/) is a "batteries included" SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full-featured query and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects
Several methods are provided which assist in building joins.
join — .join(table, first, [operator], second)
The join builder can be used to specify joins between tables, with the first argument being the joining table, the next three arguments being the first join column, the join operator and the second join column, respectively.
knex('users')
.join('contacts', 'users.id', '=', 'contacts.user_id')
.select('users.id', 'contacts.phone')
Outputs:
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`
knex('users')
.join('contacts', 'users.id', 'contacts.user_id')
.select('users.id', 'contacts.phone')
Outputs:
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`
For grouped joins, specify a function as the second argument for the join query, and use on with orOn or andOn to create joins that are grouped with parentheses.
knex.select('*').from('users').join('accounts', function() {
this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`
async getData(tagId: string) {
return db<Offer>('offers')
.where({
tag_id: tagId,
})
.join('projects', 'projects.id', 'offers.project_id')
.where('projects.deleted_at', null)
.orderBy('projects.created_at', 'desc');
},
knex('users')
.join('accounts', 'users.id', 'accounts.user_id')
.select('users.user_name as user', 'accounts.account_name as account');
leftJoin — .leftJoin(table, ~mixed~)
knex.select('*').from('users').leftJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` left join `accounts` on `users`.`id` = `accounts`.`user_id`
knex.select('*').from('users').leftJoin('accounts', function() {
this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Conditional Modification in Query
const query = knex('users')
.join('accounts', 'users.id', 'accounts.user_id')
.select('users.user_name as user', 'accounts.account_name as account');
if (projectId) {
query.modify(function (queryBuilder) {
queryBuilder.where('projects.uuid', projectId);
});
}
Mixing and and or operator
const query = db<Offer>('offers').where({
tag_id: TagId,
});
query.modify(function (queryBuilder) {
queryBuilder.where('offers.state', 'accepted')
.orWhere('offers.state', 'qa_accepted')
});
return query;
Using TypeORM
TypeORM is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used with TypeScript and JavaScript (ES5, ES6, ES7, ES8). Its goal is to always support the latest JavaScript features and provide additional features that help you to develop any kind of application that uses databases - from small applications with a few tables to large scale enterprise applications with multiple databases.
import {
Entity,
PrimaryGeneratedColumn,
Column,
ManyToMany,
JoinTable,
} from "typeorm"
@Entity()
export class Album {
@PrimaryGeneratedColumn()
id: number
@Column()
name: string
@ManyToMany(() => Photo, (photo) => photo.albums)
@JoinTable()
photos: Photo[]
}
export class Photo {
/// ... other columns
@ManyToMany(() => Album, (album) => album.photos)
albums: Album[]
}
const photos = await AppDataSource.getRepository(Photo)
.createQueryBuilder("photo")
// first argument is an alias. Alias is what you are selecting - photos. You must specify it.
.innerJoinAndSelect("photo.metadata", "metadata")
.leftJoinAndSelect("photo.albums", "album")
.where("photo.isPublished = true")
.andWhere("(photo.name = :photoName OR photo.name = :bearName)")
.orderBy("photo.id", "DESC")
.skip(5)
.take(10)
.setParameters({ photoName: "My", bearName: "Mishka" })
.getMany()
Other example to showcase Joins and query builde rin typeORM
const result = await getConnection()
.createQueryBuilder('user')
.leftJoinAndSelect('user.linkedSheep', 'linkedSheep')
.leftJoinAndSelect('user.linkedCow', 'linkedCow')
.where('user.linkedSheep = :sheepId', { sheepId })
.andWhere('user.linkedCow = :cowId', { cowId });
Joins with database relationships
Many-to-one / one-to-many is a relation where A contains multiple instances of B, but B contains only one instance of A. Let's take for example User and Photo entities. User can have multiple photos, but each photo is owned by only one single user.
import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm";
import {User} from "./User";
@Entity()
export class Photo {
@PrimaryGeneratedColumn()
id: number;
@Column()
url: string;
@ManyToOne(() => User, user => user.photos)
user: User;
}
import {Entity, PrimaryGeneratedColumn, Column, OneToMany} from "typeorm";
import {Photo} from "./Photo";
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@OneToMany(() => Photo, photo => photo.user)
photos: Photo[];
}
Here we added @OneToMany to the photos property and specified the target relation type to be Photo. You can omit @JoinColumn in a @ManyToOne / @OneToMany relation. @OneToMany cannot exist without @ManyToOne. If you want to use @OneToMany, @ManyToOne is required. However, the inverse is not required: If you only care about the @ManyToOne relationship, you can define it without having @OneToMany on the related entity. Where you set @ManyToOne - its related entity will have "relation id" and foreign key.
Example how to save such relation:
const photo1 = new Photo();
photo1.url = "me.jpg";
await connection.manager.save(photo1);
const photo2 = new Photo();
photo2.url = "me-and-bears.jpg";
await connection.manager.save(photo2);
const user = new User();
user.name = "John";
user.photos = [photo1, photo2];
await connection.manager.save(user);
or alternatively you can do:
const user = new User();
user.name = "Leo";
await connection.manager.save(user);
const photo1 = new Photo();
photo1.url = "me.jpg";
photo1.user = user;
await connection.manager.save(photo1);
const photo2 = new Photo();
photo2.url = "me-and-bears.jpg";
photo2.user = user;
await connection.manager.save(photo2);
With cascades enabled you can save this relation with only one save call.
To load a user with photos inside you must specify the relation in FindOptions:
const userRepository = connection.getRepository(User);
const users = await userRepository.find({ relations: ["photos"] });
// or from inverse side
const photoRepository = connection.getRepository(Photo);
const photos = await photoRepository.find({ relations: ["user"] });
Or using QueryBuilder you can join them:
const users = await connection
.getRepository(User)
.createQueryBuilder("user")
.leftJoinAndSelect("user.photos", "photo")
.getMany();
// or from inverse side
const photos = await connection
.getRepository(Photo)
.createQueryBuilder("photo")
.leftJoinAndSelect("photo.user", "user")
.getMany();
With eager loading enabled on a relation, you don't have to specify relations in the find command as it will ALWAYS be loaded automatically. If you use QueryBuilder eager relations are disabled, you have to use leftJoinAndSelect to load the relation.
conclusion
In APIs most of the time we struggle to find the right way of doing Join, i hope these examples can help
references
- https://typeorm.io/
- http://knexjs.org/
- https://www.tutorialspoint.com/index.htm Basic Join Example
Top comments (0)