V6: Unable to select pivot columns

[simonjcarr]

I have the following two models User and Organisation where I declaser my ManyToMany relationship

//User


  @manyToMany(() => Organisation, {
    pivotTable: 'organisation_users',
    pivotColumns: ['role'],
    pivotTimestamps: true,
  })
  declare organisation: ManyToMany<typeof Organisation>

  
}

// Organisation


  @manyToMany(() => User, {
    pivotTable: 'organisation_users',
    pivotColumns: ['role'],
    pivotTimestamps: true,
  })
  declare users: ManyToMany<typeof User>

  

I have the following organisation_users schema

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'organisation_users'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.integer('organisation_id').unsigned().references('organisations.id').onDelete('CASCADE')
      table.integer('user_id').unsigned().references('users.id').onDelete('CASCADE')
      table.string('role', 255).notNullable().defaultTo('member')
      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}

When I try to select User and preload organisation with the following

async index({ response, auth }: HttpContext) {
    // Get the users organisations and return them
    // const user = auth.user
    const organisations = await User.query().preload('organisation', (query) => {
      query.pivotColumns(['role'])
    })
    console.log(organisations)
    return response.json(organisations)
  }

The $extras is empty

'$preloaded': { organisation: [Array] },
'$extras': {},
'$sideloaded': {},
'$isPersisted': true,
'$isDeleted': false,
'$isLocal': false

The user and the organisation are returned correctly, and I do have data in my pivot column. See the attached image.

I also tried to create a Model for the pivot table, but that made no difference.

[simonjcarr]

I have realised now, that I only get to see the pivot colums when I access the organisation object in the result. This means I need to map over my results and access the $attributes and $extras manually.

async index({ response, auth }: HttpContext) {
    // Get the users organisations and return them
    const user = auth.user
    const orgs = await user?.related('organisation').query()

    return response.json(
      orgs?.map((org) => {
        return {
          ...org.$attributes,
          role: org.$extras.pivot_role,
        }
      })
    )
  }

Is there a cleaner way to do this?

[thetutlage]

Yes, the pivot columns are part of the $extras object, since they do not exist on the Org model.

If you need more control over how the $extras object is seralized, you can define the serializeExtras method on your model. Here are the relevant docs. https://lucid.adonisjs.com/docs/serializing-models#serializing-extras-object