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.