Adonis.js Relationships

I have these tables: companies, branches, users, users_branches

Every branch is linked to a company . The user can be linked to a branch that captures in the users_branches pivot table

In the index and show methods of the company I need to display only as companies that the user has access to, that is, only as companies that are linked to any branch that he is registered with. Can you help me?

What I have today is this, very basic and listing everything:

async index () {
  const companies = await Company.all()
  return companies
}

async show ({ params }) {
  const company = await Company.findOrFail(params.id)

  await company.load('branches')

  return company
}

I’ve tried to do it in two other ways:

async index ({ auth }) {
  const { user } = auth

  return user.branches().company().fetch()
}

and

async index () {
  const companies = await Company.query()
    .whereHas('branches', branchesQuery => {
      branchesQuery.wherePivot('user_id', 1)
    })
    .fetch()

  return companies
}

But it does not work

1 Like

Hi @pedroentringer

Can you share your models?

2 Likes

Hello @CrBast , thanks for your reply.

User

'use strict'

/** @type {typeof import('@adonisjs/lucid/src/Lucid/Model')} */
const Model = use('Model')

/** @type {import('@adonisjs/framework/src/Hash')} */
const Hash = use('Hash')

class User extends Model {
  static boot () {
    super.boot()

    this.addHook('beforeCreate', 'UserHook.sendNewUserEmail')

    this.addHook('beforeSave', async userInstance => {
      if (userInstance.dirty.password) {
        userInstance.password = await Hash.make(userInstance.password)
      }
    })
  }

  static get hidden () {
    return ['password']
  }

  static get traits () {
    return [
      '@provider:Adonis/Acl/HasRole',
      '@provider:Adonis/Acl/HasPermission'
    ]
  }

  tokens () {
    return this.hasMany('App/Models/Token')
  }

  branches () {
    return this.belongsToMany('App/Models/Branch').pivotTable('user_branches')
  }
}

module.exports = User

Company

'use strict'

/** @type {typeof import('@adonisjs/lucid/src/Lucid/Model')} */
const Model = use('Model')

class Branch extends Model {
  company () {
    return this.belongsTo('App/Models/Company')
  }

  sla () {
    return this.hasMany('App/Models/ServiceLevelAgreement')
  }

  users () {
    return this.belongsToMany('App/Models/Branch').pivotTable('user_branches')
  }
}

module.exports = Branch

Company

'use strict'

/** @type {typeof import('@adonisjs/lucid/src/Lucid/Model')} */
const Model = use('Model')

class Company extends Model {
  branches () {
    return this.hasMany('App/Models/Branch')
  }
}

module.exports = Company
2 Likes

Sorry for late answer

I’d do something like that:

// SubQuery - Get all branch ids
let branchIdsQuery = Database.from("users_branches")
  .select("branch_id")
  .where("user_id", 2); // User id

// SubQuery - Get company ids with branchIdsQuery
let companiesId = Database.from("branches")
  .select("company_id")
  .whereIn("id", branchIdsQuery)
  .distinct();

// Get all companies where ids in companiesId
let companies = await Company.query()
  .whereIn("id", companiesId)
  .fetch();

return companies;

I used subqueries because it’s simpler.

My DB :

image

You have to adapt with your fields

1 Like