This addon adds the functionality to preload relations via joins instead of a separate query. This package is heavily inspired from python's Django Framework
Works with
@adonisjs/lucid > 16.*.*
I moved from Django to AdonisV5 for a small project and missed the convenient inner join select_related functionality of Django ORM.
So, I built the similar functionality in Adonisjs.
This module provides functionality to load relationships using inner/outer joins instead of the traditional preload (which makes a separate query).
It is also useful when you want to apply where conditions on the query which is something that preload doesn't provide.
Install it using npm or yarn.
# npm
npm i --save adonisjs-select-related
node ace configure adonisjs-select-related
# yarn
yarn add adonisjs-select-related
node ace configure adonisjs-select-relatedFirst, apply the selectRelatedMixin to your model.
// App/Models/User.ts
import { DateTime } from 'luxon'
import { selectRelatedMixin } from '@ioc:Adonis/Addons/SelectRelated'
import { compose } from '@ioc:Adonis/Core/Helpers'
import {
BaseModel,
column,
HasMany,
hasMany,
HasOne,
hasOne,
ModelAttributes,
} from '@ioc:Adonis/Lucid/Orm'
import Profile from 'App/Models/Profile'
export default class User extends compose(BaseModel, selectRelatedMixin) {
@column({ isPrimary: true })
public id: number
@column()
public email: string
@column()
public name: string
@column.dateTime({ autoCreate: true })
public createdAt: DateTime
@column.dateTime({ autoCreate: true, autoUpdate: true })
public updatedAt: DateTime
@hasOne(() => Profile)
public profile: HasOne<typeof Profile>
}// App/Models/Profile.ts
import { DateTime } from 'luxon'
import { selectRelatedMixin } from '@ioc:Adonis/Addons/SelectRelated'
import { compose } from '@ioc:Adonis/Core/Helpers'
import { BaseModel, BelongsTo, belongsTo, column } from '@ioc:Adonis/Lucid/Orm'
import User from 'App/Models/User'
export default class Profile extends compose(BaseModel, selectRelatedMixin) {
@column({ isPrimary: true })
public id: number
@column()
public userId: number
@belongsTo(() => User)
public user: BelongsTo<typeof User>
@column()
public phoneNumber: string | null
@column.date()
public dateOfBirth: DateTime | null
@column.dateTime({ autoCreate: true })
public createdAt: DateTime
@column.dateTime({ autoCreate: true, autoUpdate: true })
public updatedAt: DateTime
}Now, you have a selectRelated method on query builder of User model.
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import User from 'App/Models/User'
export default class UsersController {
/**
* Get list of users with their profiles
* GET /users/
*/
public async index() {
const users = await User.query().selectRelated('profile')
// SQL: select `users`.*, `profile`.`id` as `_profileid`, `profile`.`user_id` as `_profileuserId`, `profile`.`phone_number` as `_profilephoneNumber`, `profile`.`date_of_birth` as `_profiledateOfBirth`, `profile`.`created_at` as `_profilecreatedAt`, `profile`.`updated_at` as `_profileupdatedAt` from `users` inner join `profiles` as `profile` on `users`.`id` = `profile`.`user_id`
return users // or individually serialize them
}
}Example Output:
[
{
"id": 1,
"email": "test@example.com",
"name": "Test User",
"created_at": "2022-02-12T03:10:40.000+05:30",
"updated_at": "2022-02-12T03:10:40.000+05:30",
"profile": {
"id": 1,
"user_id": 1,
"phone_number": "xxx-xxx-xxxx",
"date_of_birth": "2021-05-11",
"created_at": "2022-02-12T03:10:40.000+05:30",
"updated_at": "2022-02-12T03:10:40.000+05:30"
}
}
]You have to filter the results using relationName.column_name format.
This is because Adonis doesn't have any hooks for modifying the column names of where conditions during execution
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import User from 'App/Models/User'
export default class UsersController {
/**
* Get list of users with their profiles
* GET /users/
*/
public async index() {
const users = await User.query()
.selectRelated('profile')
.whereNotNull('profile.phone_number') // only users who have filled their phone numbers will be returned
// SQL: select `users`.*, `profile`.`id` as `_profileid`, `profile`.`user_id` as `_profileuserId`, `profile`.`phone_number` as `_profilephoneNumber`, `profile`.`date_of_birth` as `_profiledateOfBirth`, `profile`.`created_at` as `_profilecreatedAt`, `profile`.`updated_at` as `_profileupdatedAt` from `users` inner join `profiles` as `profile` on `users`.`id` = `profile`.`user_id` where `profile`.`phone_number` is not null
return users
}
}Select related accepts options which you can use to define which type of join should be applied
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import User from 'App/Models/User'
export default class UsersController {
/**
* Get list of users with their profiles
* GET /users/
*/
public async index() {
const users = await User.query().selectRelated('profile', {
joinType: 'leftOuter', // 👈 it can be 'inner', 'leftOuter' or 'rightOuter'
})
// SQL: select `users`.*, `profile`.`id` as `_profileid`, `profile`.`user_id` as `_profileuserId`, `profile`.`phone_number` as `_profilephoneNumber`, `profile`.`date_of_birth` as `_profiledateOfBirth`, `profile`.`created_at` as `_profilecreatedAt`, `profile`.`updated_at` as `_profileupdatedAt` from `users` left outer join `profiles` as `profile` on `users`.`id` = `profile`.`user_id`
return users
}
}import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import User from 'App/Models/User'
export default class UsersController {
/**
* Get data of a user
* GET /users/:id/
*/
public async show({ params }: HttpContextContract) {
const user = await User.query()
.selectRelated('profile.user') // 👈 use dot notation to load nested relations
.where(`users.id`, params.id) // 👈 you have to use `table_name.column_name` format to refer to your parent table's columns.
// If you don't do this then you'll get ambiguity error from sql
.firstOrFail()
// SQL: select `users`.*, `profile`.`id` as `_profileid`, `profile`.`user_id` as `_profileuserId`, `profile`.`phone_number` as `_profilephoneNumber`, `profile`.`date_of_birth` as `_profiledateOfBirth`, `profile`.`created_at` as `_profilecreatedAt`, `profile`.`updated_at` as `_profileupdatedAt`, `profile__user`.`id` as `_profile__userid`, `profile__user`.`email` as `_profile__useremail`, `profile__user`.`name` as `_profile__username`, `profile__user`.`created_at` as `_profile__usercreatedAt`, `profile__user`.`updated_at` as `_profile__userupdatedAt` from `users` inner join `profiles` as `profile` on `users`.`id` = `profile`.`user_id` inner join `users` as `profile__user` on `profile`.`user_id` = `profile__user`.`id` where `users`.`id` = ? limit 1
return user
}
/**
* Filter using nested relations
* GET /users/:id/
*/
public async filterOnNestedRelations({ params }: HttpContextContract) {
const user = await User.query()
.selectRelated('profile.user') // 👈 use dot notation to load nested relations
.where(`profile__user.id`, params.id) // 👈 in nested relations, the table name becomes `parentRelation__childRelation`
.firstOrFail()
// SQL: select `users`.*, `profile`.`id` as `_profileid`, `profile`.`user_id` as `_profileuserId`, `profile`.`phone_number` as `_profilephoneNumber`, `profile`.`date_of_birth` as `_profiledateOfBirth`, `profile`.`created_at` as `_profilecreatedAt`, `profile`.`updated_at` as `_profileupdatedAt`, `profile__user`.`id` as `_profile__userid`, `profile__user`.`email` as `_profile__useremail`, `profile__user`.`name` as `_profile__username`, `profile__user`.`created_at` as `_profile__usercreatedAt`, `profile__user`.`updated_at` as `_profile__userupdatedAt` from `users` inner join `profiles` as `profile` on `users`.`id` = `profile`.`user_id` inner join `users` as `profile__user` on `profile`.`user_id` = `profile__user`.`id` where `profile__user`.`id` = ? limit 1
}
}Take a look at chirgjin/adonisjs-select-related-example for some examples of select related.