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.

Published
Apr 02
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

Join The Discussion! (2 Comments)

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

  1. Commented 16 days 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. Commented 16 days 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