Previous
Sign Up to Track Progress
Next

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.

Tom Gobich
Author
Tom Gobich
Published
Apr 02, 24
Duration
9m 48s

Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.

Adocasts

Burlington, KY

Visit Website

Get the Code

Download or explore the source code for this lesson on GitHub

Repository

Join The Discussion! (4 Comments)

Please sign in or sign up for free to join in on the dicussion.

  1. bruce-irakoze
    Commented 1 year ago

    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

    Please sign in or sign up for free to reply

    1. tomgobich
      Adocasts Plus Subscriber
      Commented 1 year ago

      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!

      0

      Please sign in or sign up for free to reply

  2. n2zb
    Commented 6 days ago

    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

    Please sign in or sign up for free to reply

    1. tomgobich
      Adocasts Plus Subscriber
      Commented 5 days ago

      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 }
  },
}

      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

      Please sign in or sign up for free to reply