AdonisJS | Parse Joined ORM Model Before Returning

I'm trying to convert my raw queries into using AdonisJS's built in ORM and QueryBuilder systems, and currently in my raw queries I build objects out of different tables to make for better organization when passed to the front-end for different object types. For example, fetching a user could look something like this, with a role object being created

SELECT
  u.id,
  json_build_object(
    'name', role.name
  ) AS role
FROM users u
LEFT JOIN roles role
  ON role.id = u.role_id

If the user ORM looks like

export default class User extends BaseModel {
  public static table = TABLES.USERS

  @column({ isPrimary: true })
  public id: number

  @column()
  public unique_id: string

  @column()
  public email: string

  @column({
    serializeAs: null,
  })
  public first_name: string
  @column({
    serializeAs: null,
  })
  public last_name: string
  @computed()
  public get name() {
    return {
      first: this.first_name,
      last: this.last_name,
      full: `${this.first_name} ${this.last_name}`,
    }
  }

  @column()
  public active: boolean

  @hasOne(() => Role)
  public role: HasOne<typeof Role>
}

And the role ORM looks like

export default class Role extends BaseModel {
  public static table = TABLES.ROLES

  @column({ isPrimary: true })
  public id: number

  @column()
  public name: string

  @column()
  public options: string[]
  @column()
  public permissions: string[]
  @column()
  public widgets: string[]

  @column()
  public locked: boolean

  @belongsTo(() => User)
  public user: BelongsTo<typeof User>
}

Is there any way to automatically join the role linked to the user from within the User ORM/QueryBuilder? I've looked through the Adonis documentation, specifically regarding relationships, but most of what I see is that you can get the related table, roles in this case, after the fact, but not within the query itself, save for writing a join statement for the table. And if it does have to come from a join, is there a way to use the Role ORM to handle fields similar to how await User.all() works, or does it have to be a custom select?

Not sure if it's necessary, but I'm currently on Adonis Core v5.8.3, Adonis Lucid v18.0.1, and Adonis Repl v3.1.11, if that helps. If what I'm asking seems convoluted, just let me know where clarifications are needed and I'll clarify. Thanks!

Solution

I discovered that a combination of the hasMany decorator on the role ORM and the belongsTo decorator on the user ORM links the two, and then using the preload query function allows for the automatic joining of role to user

Users Property In Role ORM

@hasMany(() => User, {
  foreignKey: 'role_id',
})
public users: HasMany<typeof User>

Role Property In User ORM

@column({
  serializeAs: null
})
public role_id: number
@belongsTo(() => Role, {
  foreignKey: 'role_id',
})
public role: BelongsTo<typeof Role>

Fetch Logic

const users = await User.query().preload('role').paginate(params.page, params.limit)