Tying together multiple DB relationships

Well, I’m back again. I’ve made some very good progress since I first started out, but I’ve now got to a point where I’m a little muddled up. I’m hoping someone can give me a bit of direction.

So far I have been using belongsToMany relationships to allocate locations, titles, verticals etc to users, so they have a limited number of choices when they’re using the system. That was then going to lead into listing out vacancies (there’s going to be more later, but that doesn’t matter for now).

I’ve started on the vacancies, and have put in hasMany relationships to the locations, titles, verticals etc for the vacancies. For example…

Location Model:

'use strict'

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

class Location extends Model {
    users() {
        return this.belongsToMany('App/Models/User')
    }
    teams() {
        return this.belongsToMany('App/Models/Team')
    }
    vacancies() {
        return this.hasMany('App/Models/Vacancy')
    }
}

module.exports = Location

And Vertical

'use strict'

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

class Vertical extends Model {
    vacancies() {
        return this.hasMany('App/Models/Vacancy')
    }
}

module.exports = Vertical

I read somewhere that the reverse relationship doesn’t need to be defined. I’m not sure I understand why, but I’ll chalk that down to magic :slight_smile:

Now finally getting to the point of the post. I have managed to add entries to the vacancies table with references to each of these relationships with just an id column (location_id, vertical_id, ect), but now getting the information out of the tables has me in a bit of a spin.

I was expecting to do something like this in order to get the data needed:

const vacancies = await auth.user.vacancies().fetch(); Return the vacancies associated with the current user (This works)
const verticals = await vacancies.verticals().fetch(); Return the verticals (and any other relationships) associated with the vacancies that have been returned.

Then loop through all the vacancies, building up an object to pass to a view by referencing the arrays that have been returned. Something like:

      let output = [];
      let i = 0;
      for (i = 0; i < vacanciesJSON.length; i++) {
        output.push({
            id: vacanciesJSON[i].id,
            vertical: verticalsJSON[vacanciesJSON[i].vertical_id].name
            ...
        });

A. Am I on the right track?
B. If so, I’m getting an error vacancies.verticals is not a function when attempting something similar.
C. If I’m totally barking up the wrong tree, then is there a better approach I should be taking?
D. Bonus question #1. I’ve put in how I’m saving the data to the vacancies table after these questions. I’m not using an attach() function like I have done with the belongsToMany relationships. Is this right, or is this where the problem is originating?
E. I can’t seem to get the foreign references working between the verticals table and the vacancies table. This is the first one I’ve tried, and I assume I’ll need to do the same for all the related tables. I’ve attached the migration and error I’m getting at the end of these questions.

How I’m saving vacancies to the DB

    async create({request, response, session, auth}){
        // Get all submitted data
        const vacancy = await Vacancy.create(request.only([
          'vertical_id',
          'RAFnumber',
          'vacancytype_id',
          'replacementFor',
          'employmenttype_id',
          'manager',
          'map_title_id',
          'location_id',
          'team_id',
          'user_id',
          'vacancystatus_id',
          'startDate']));

        session.flash({ messageSuccess: 'Vacancy successfully added!' })

        return response.redirect('/vacancies');
    }

Migration(select table) for adding foreign references:

'use strict'

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

class VacancySchema extends Schema {
  up () {
    this.table('vacancies', (table) => {
      table.foreign('vertical_id')
      .references('id')
      .inTable('verticals')
      .onDelete('CASCADE')
      .onUpdate('CASCADE');
    })
  }

  down () {
    this.table('vacancies', (table) => {
      // reverse alternations
    })
  }
}

module.exports = VacancySchema

Error:

  sqlMessage: "Referencing column 'vertical_id' and referenced column 'id' in foreign key constraint 'vacancies_vertical_id_foreign' are incompatible.",
  sqlState: 'HY000',
  index: 0,
  sql: 'alter table `vacancies` add constraint `vacancies_vertical_id_foreign` foreign key (`vertical_id`) references `verticals` (`id`) on update CASCADE on delete CASCADE'
1 Like

Hey @kJones. Glad to see the immense progress you are making. Well done.

The result of the above statement is an array. So you cannot call verticals() on the array result since verticals()was not defined in the prototype of the array.

The vacancies array contains models i.e. individual Vacancy models. The verticals() methods were defined on the prototype of Vacancy. So, in order to get the verticals for each vacancy, you need to use a forEach or for...in loop to loop through each vacancy and get their associated verticals.

Make sure that column vertical_id on table vacancies is an integer type and unsigned.

Thank you @ndianabasi, that’s very kind of you to say so. I think I’m getting closer to getting my head around the way it all fits together. That’s in no small part due to your help!

Just to make sure I understand. I will be looping through the vacancies array, each time making a database call to the table in question to get the information out that’s needed. Or are you saying that the information is already stored in the vacancies array because of the relationship that has been defined? (If not I guess I’m not sure why relationships are defined in the first place :slight_smile:)

If I need to be calling from the database each time, that seems fairly intensive. It would mean roughly 10 calls for each vacancy.

On a slightly different line, I tried to do a forEach loop on the result of await auth.user.vacancies().fetch() and I get an error `vacancies.forEach is not a function. It looks like I have to convert it to JSON first using toJSON(), but I’m assuming that would remove any additional data in that result?

Many thanks again for your continued help!

I think I understand your question better now. Try:

auth.user.vacancies().with(‘verticals’).fetch()

Yes, you need to convert to JSON and loop through the raw array. But that brings up the N+1 problem. So, use the statement above.

Thanks, I think I’m making progress. That looks a lot closer to what I’m expecting there to be. :slight_smile:

I’m now getting a sql error though

select * from `verticals` where `verticals`.`vacancy_id` in (4, 5) - ER_BAD_FIELD_ERROR: Unknown column 'verticals.vacancy_id' in 'where clause'

The obvious issue there is that it should be looking for id in the verticals table rather than vacancy_id. Is this why I was assuming there should be a reverse relationship? At the moment the only relationship that’s set is in the Vertical model. Not Vacancy.

Thanks!

Try to set up the reverse relationships and check.

Can you show your schema file for verticals?

I added

    verticals(){
        return this.belongsTo('App/Models/Vertical')
    }

to the Vacancy model. hasOne only gave me the same error.

It now returns the vacancy information, but the verticals value is null.

Here’s my vacancies schema:

'use strict'

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

class VacancySchema extends Schema {
  up () {
    this.create('vacancies', (table) => {
      table.increments()
      table.string('vertical_id')
      table.string('RAFnumber')
      table.string('vacancytype_id')
      table.string('replacementFor')
      table.string('employmenttype_id')
      table.string('manager')
      table.string('map_title_id')
      table.string('location_id')
      table.string('team_id')
      table.string('user_id')
      table.string('vacancystatus_id')
      table.date('startDate')
      table.timestamps()
    })
  }

  down () {
    this.drop('vacancies')
  }
}

module.exports = VacancySchema

I meant the schema file for verticals. Error shows that verticals.vacancy_id is missing.

Oh yes, of course. It’s worth noting that I’m not getting that error anymore. Since I added the belongsTo relationship to the Vacancy model.

Anyway, here you go:

'use strict'

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

class VerticalSchema extends Schema {
  up () {
    this.create('verticals', (table) => {
      table.increments()
      table.string('name')
      table.timestamps()
    })
  }

  down () {
    this.drop('verticals')
  }
}

module.exports = VerticalSchema

And of course vacancy_id isn’t in there, as it’s stored the other way around. vertical_id is stored in vacancies, and just references the id in the verticals table.

Thanks!

I’ve enabled DB debugging and this is what’s being requested for the auth.user.vacancies().with(‘verticals’).fetch() request.

{
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '3', '1' ],
  __knexQueryUid: '3cb35a61-ae73-43f0-ac2a-eaf19a533eca',
  sql: 'select * from `verticals` where `verticals`.`id` in (?, ?)'
}

If I’m readying that correctly it’s selecting all rows that have the id (not vertical_id) of 3 or 1. If that’s correct then it should be returning two results as there are rows with those IDs.

However after converting the result of that query to JSON, I’m getting the following (please excuse the weird dummy data):

[
  {
    id: 4,
    vertical_id: '3',
    RAFnumber: 'dsfghmghfgf cha',
    vacancytype_id: '2',
    replacementFor: 'Some person',
    employmenttype_id: '2',
    manager: 'aszgxcnfghfdgf cha',
    map_title_id: '3',
    location_id: '5',
    team_id: '7',
    user_id: '3',
    vacancystatus_id: '2',
    startDate: 2020-04-01T23:00:00.000Z,
    created_at: '2020-04-28 09:15:23',
    updated_at: '2020-04-28 12:04:44',
    verticals: null
  },
  {
    id: 5,
    vertical_id: '1',
    RAFnumber: 'dfcgbmnvg gchb',
    vacancytype_id: '1',
    replacementFor: 'Some person',
    employmenttype_id: '1',
    manager: 'f',
    map_title_id: '1',
    location_id: '3',
    team_id: '3',
    user_id: '3',
    vacancystatus_id: '1',
    startDate: 2020-04-28T23:00:00.000Z,
    created_at: '2020-04-28 13:41:49',
    updated_at: '2020-04-28 13:41:49',
    verticals: null
  }
]

As you can see the verticals that being returned are null. I’m really not sure where to go next to debug this problem.

Thanks!

Holy Moley! I got it working.

I went back and looked over all your suggestions as to why it wasn’t working. And it turns out that the vertical_id in vacancies was set to a varchar rather than an int. Changing that and resetting the foreign relationships, has done the trick.

For anyone who is interested, here’s the migration:

'use strict'

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

class VacancySchema extends Schema {
  up () {
    this.table('vacancies', (table) => {
      table.integer('vertical_id').unsigned().references('id').inTable('verticals').alter()
    })
  }

  down () {
    this.table('vacancies', (table) => {
      // reverse alternations
    })
  }
}

module.exports = VacancySchema

1 Like