Self related table and model relationship

[ivanvinicius]

Hello!

In my database I have a category table, and this table is self-related, so I can create categories and subcategories. Check the image below:

And the image below represents the records saved:

As you can see, parent_id can be a string ou null sometimes.

I would like to know how to create the relationship to the example because, I want the query the records using preload method in the future!

🍂 You can check my GIST

⚠️ I have tried to follow the steps mentioned here, but no success

Appreciate your help!

[ivanvinicius]

I managed to solve the problem.

The model will look like this:

/* eslint-disable no-use-before-define */

import { DateTime } from 'luxon'
import {
  BaseModel,
  BelongsTo,
  beforeCreate,
  belongsTo,
  column,
  hasMany,
  HasMany,
} from '@ioc:Adonis/Lucid/Orm'
import { v4 as uuid } from 'uuid'

import Image from './Image'

export default class Category extends BaseModel {
  public static selfAssignPrimaryKey = true

  @beforeCreate()
  public static async createID(model: Category) {
    if (!model.id) {
      model.id = uuid()
    }
  }

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

  @column()
  public name: string

  @column()
  public parentId: string | null

  @column()
  public imageId: string

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime

  // ---

  @hasMany(() => Category, { foreignKey: 'parentId' })
  subcategory: HasMany<typeof Category>

  @belongsTo(() => Image, { localKey: 'id' })
  public image: BelongsTo<typeof Image>
}

And the controller query will be like:

 const query = Category.query().preload('subcategory').preload('image')