Making relationship using 6 tables

I have 6 tables to make relationship,

  1. tbl_User (user_id as pk and company_id as fk)
    //user belongs to only one company
  2. tbl_Vehicle (vehicle_id as pk and company_id as fk)
    //vehicle belongs to only one company
  3. tbl_Route (route_id as pk)
  4. tbl_Company (company_id as pk)
    // Vehicle_Id from Vehicle table and Route_Id from route_table - vehicle can have multiple routes
  5. tbl_Vehicle_Route_Map
    // user_id from User and vehicle_id from Vehicle, one user have one vehicle
  6. tbl_User_Vehicle_Map

I need to create relationship with each table
user with company
did like this - this.belongsTo('App/Models/Company', 'company_id', 'id')
user with assigned vehicle

  driver() {
    return this
      .belongsToMany('App/Models/User', 'vehicle_id', 'user_id', 'vehicle_id', 'user_id')
      .pivotTable('vehicle_driver_map')
  }

How would I get the details for below mappings, these requires more than 3 tables

  1. user with route
  2. user with assigned vehicle and route
  3. user with company, assigned vehicle and assigned route(s)
1 Like

Hi @trighati!

Make multiple smaller relations, something like this:

  1. User belongs to company
  2. Vehicle belongs to company
    https://adonisjs.com/docs/4.1/relationships#_belongs_to
  3. Vehicle has many routes
    https://adonisjs.com/docs/4.1/relationships#_has_many
  4. User has one vehicle
    https://adonisjs.com/docs/4.1/relationships#_has_one
  5. User has many routes through vehicle
    https://adonisjs.com/docs/4.1/relationships#_many_through

And then to answer questions:

Use relation nr 5. But this will return array, since vehicle can have many routes. You can make some filter like active or get latest or something, depending on your application needs

Use relationships 4 and 5 with multiple .with()

await User.query().with('vehicle').with('routes').fetch()

Use relations 1, 4 and 5 again with multiple with()

await User.query()
  .with('vehicle')
  .with('routes')
  .with('company')
  .fetch()

But there are some questions that will depend on application logic.
Can user have more than one route? (Show only current / latest)
Can user routes differ from vehicle routes? (User has changed vehicles)

4 Likes