Adocasts Adocasts
Adocasts Adocasts
Sign In Sign Up
Playing Next Lesson In
seconds
Let's Learn AdonisJS 6

Let's Learn AdonisJS 6 #5.9

Defining Many-To-Many Relationships and Pivot Columns

In This Lesson

We'll learn how you can define many-to-many relationships within your Lucid ORM Models. We'll also discuss the relationship options, and how you can automatically include pivot table data with all queries.

Created by
@tomgobich
Published

Join the Discussion 4 comments

Share your thoughts!
Create a free account to join in on the discussion
  1. @n2zb
    @n2zb

    Hi Tom, the project I'm working on to learn how to use Adonis is a Reddit-like project. I'd like to define a "many to many" relationship to manage the registration of users to a subreddit. So I have a pivot that records subscriptions: subreddit id, id who joined, subscription date, and the member's local role (administrator, moderator, …). Since the additional columns (in this case, the role) are managed by the pivotColums, I'm wondering how to preload the role since we can't define the relation

    1
    1. Responding to n2zb
      @tomgobich
      @tomgobich Adocasts Plus

      Hi n2zb! You can preload pivot table data two ways.

      First option is to define them directly in the relationship definition, as shown in this lesson at 6:50. This will always include them when the relationship is preloaded. So, this is a great option is you'll frequently need that pivot data.

      export default class Movie extends BaseModel {
  // ...

  @manyToMany(() => Cineast, {
    pivotTable: 'cast_movies',
    pivotTimestamps: true,
    pivotColumns: ['character_name', 'sort_order'], // 👈
  })
  declare castMembers: ManyToMany<typeof Cineast>

  // ...
}
      
  
      
    
      
      Copied!

      The second option is to add them on a per-query basis, as needed.

      const crew = await movie
  .related('crewMembers')
  .query()
  .pivotColumns(['title', 'sort_order']) // 👈
  .orderBy('pivot_sort_order')
      
  
      
    
      
      Copied!

      This will then add those pivot columns into the $extras object of the preloaded relationship.

      const crewMember = {
  // ...

  '$extras': {
    pivot_title: 'Art Director', // 👈
    pivot_sort_order: 0, // 👈
    pivot_movie_id: 417,
    pivot_cineast_id: 40,
    pivot_created_at: DateTime { ts: 2024-03-30T11:59:58.911+00:00, zone: UTC, locale: en-US },
    pivot_updated_at: DateTime { ts: 2024-03-30T11:59:58.911+00:00, zone: UTC, locale: en-US }
  },
}
      
  
      
    
      
      Copied!

      Note that the column names are prefixed with pivot_ and that the ids of the relationship are always included. Also note, if you're serializing your models, you'll want to turn on extras serialization via the below on the model, and they'll be serialized as meta.

      export default class Cineast extends BaseModel {
  serializeExtras = true

  // ...
}
      
  
      
    
      
      Copied!

      We get into all of this in various later lessons in this series as well, if you'd like a deeper dive!

      0
  2. @bruce-irakoze
    @bruce-irakoze

    Hello @tomgobich ,

    I am currently working with multi-level hierarchical data. I have identified a pattern called Closure Table(https://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html), which appears to be a great fit for my requirements. Now, I want to model this using Lucid, and this is where I'm facing some challenges. Below are the table structures:

    // Users table

export default class extends BaseSchema {

  protected tableName = 'users';

  async up() {

    this.schema.createTable(this.tableName, (table) => {

      table.increments('id').primary().notNullable();

      table.string('full_name').notNullable();

      table.timestamp('created_at').notNullable();

      table.timestamp('updated_at').nullable();

    });

  }

  async down() {

    this.schema.dropTable(this.tableName);

  }

}
    
  
    
    
    
      Copied!
      
    
        
    
      
    
    
    
  
    
  

    // Referrals table

export default class extends BaseSchema {

  protected tableName = 'referrals';

  async up() {

    this.schema.createTable(this.tableName, (table) => {

      table.increments('id').primary().notNullable();

    table.integer('referrer').unsigned().references('id').inTable('users').notNullable(); // Node parent

      table.integer('referee').unsigned().references('id').inTable('users').notNullable(); // Node child

      table.integer('level').notNullable().defaultTo(0);

      table.timestamp('created_at').nullable();

      table.timestamp('updated_at').nullable();

  });

}

  async down() {

    this.schema.dropTable(this.tableName);

  }

}
    
  
    
    
    
      Copied!

    For example, here is how we can insert a node into the referrals table:

    INSERT INTO referrals(referrer, referee, level)

SELECT p.referrer, c.referee, p.level + c.level + 1

FROM referrals p, referrals c

WHERE p.referee = 2 AND c.referrer = 1;
    
  
    
    
    
      Copied!

    Could you please help me understand how I can model this structure using Lucid and provide an example of how to translate the above query using Lucid?

    Thank you.

    0
    1. Responding to bruce-irakoze
      @tomgobich
      @tomgobich Adocasts Plus

      Hi Bruce!

      I'm going to preface this by saying, I'm not familiar with the closure table approach, though I did give the link a read-through.

      That said, I'm not sure exactly how well this will translate to Lucid. You'll likely need to perform the actual insert as a raw query, and at that point, it may be easier to just define the entire thing as a raw query as well.

      import db from '@adonisjs/lucid/services/db'

await db.rawQuery(`
  INSERT INTO referrals(referrer, referee, level)
  SELECT p.referrer, c.referee, p.level + c.level + 1
  FROM referrals p, referrals c
  WHERE p.referee = :refereeId AND c.referrer = :referrerId;
`, {
  refereeId: 2,
  referrerId: 1
})
      
  
      
    
      
      Copied!

      However, something like the below might work, at least for the select portion, if you're looking to keep with the query builder.

      const records = await Referral.query()
  .join('referrals as child', 'referrals.referee', '=', 'child.referrer')
  .where('referrals.referee', 2)
  .where('child.referrer', 1)
  .select('referrals.referrer', 'child.referee', db.raw('referrals.level + child.level + 1 as level'))
      
  
      
    
      
      Copied!

      I hope this helps!

      1