Returning related rows

Hi there,

I’m incredibly new to Adonis, and it’s been a long time since I have done any kind of real dev, so please forgive me if I’m being thick.

I have managed to build an app that maintains Users (mostly the built-in model), and Locations. The usual CRUD stuff.

The purpose of this is to make it so that certain users are only able to administer data that belongs to certain locations. I have managed to blunder my way through attaching locations to users, updating the locations for users (this is just an overwrite at the moment, as you’ll soon discover), and removing the entries in the pivot tables when I delete a user.

I am now completely stuck with trying to return a list of locations against a specific user. This would then be turned into checkboxes on a user edit page so that locations could be updated.

So far I have the following in my controller:

    async edit({view, params}) {

        // fetch user data
        const user = await User.find(params.id);
        const locations = await user
            .locations()
            .fetch()
        
        console.log(locations);

        return view.render('users.edit', { 
            user: user.toJSON(),
            locations: locations.toJSON() 
        });
    }

The user id is being passed through the params perfectly fine, and user data is being returned.

As for the locations, it seems as though absolutely nothing is being returned, despite there being a number of locations allocated to that user.

I have read the documentation a number of times and I’m still getting totally stuck. If anyone can offer me any kind of help, I really would appreciate it!

In case it helps, here are the migrations for all 3 tables, as well as the models for each:

User Migration:

'use strict'

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

class UserSchema extends Schema {
  up () {
    this.create('users', (table) => {
      table.increments()
      table.string('username', 80).notNullable().unique()
      table.string('email', 254).notNullable().unique()
      table.string('password', 60).notNullable()
      table.timestamps()
    })
  }

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

module.exports = UserSchema

Location Migration:

'use strict'

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

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

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

module.exports = LocationsSchema

Location/User Pivot Table Migration:

'use strict'

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

class LocationUserSchema extends Schema {
  up () {
    this.create('location_user', (table) => {
      table.increments()
      table.integer('location_id').unsigned().index('location_id')
      table.integer('user_id').unsigned().index('user_id')
    })
  }

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

module.exports = LocationUserSchema

User Model:

'use strict'

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

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

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

    /**
     * A hook to hash the user password before saving
     * it to the database.
     */
    this.addHook('beforeSave', async (userInstance) => {
      if (userInstance.dirty.password) {
        userInstance.password = await Hash.make(userInstance.password)
      }
    })
  }

  locations(){
    return this.belongsToMany('App/Models/Location')
  }

  /**
   * A relationship on tokens is required for auth to
   * work. Since features like `refreshTokens` or
   * `rememberToken` will be saved inside the
   * tokens table.
   *
   * @method tokens
   *
   * @return {Object}
   */
  tokens () {
    return this.hasMany('App/Models/Token')
  }
}

module.exports = User

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')
    }
}

module.exports = Location

1 Like

Hello @kJones and welcome to forums!

Is this line actually printing out right data for you?
Or is data going missing inside the view itself?

Hey,

Thanks for the reply. It’s encouraging that it’s not a “It’s right here, idiot” kind of problem :stuck_out_tongue:

The console.log is returning the following:

VanillaSerializer { rows: [], pages: null, isOne: false }

To be honest, I tried to figure out what that meant (if possibly the rows array wasn’t printing out for example), but I didn’t get anywhere with that.

Thanks again for the reply.

Welcome @kJones. You are doing fantastically well with your code. I don’t see any blundering here. :wink:

You missed adding foreign relationships definitions in your LocationUserSchema. See below:

this.create('location_user', (table) => {
  table.increments()
  table.integer('location_id').unsigned().index('location_id').references('locations.id').onDelete('CASCADE').onUpdate('CASCADE')
  table
  .integer('user_id')
  .unsigned()
  .index('user_id')
  .references('users.id')
  .onDelete('CASCADE')
  .onUpdate('CASCADE');
})

Cheers!

Edit:

Adonis.js uses Knex.js for Database queries. A good understanding of the Knex.js API will help you tremendously.

Start here: http://knexjs.org

For how to define foreign relationships, see: http://knexjs.org/#Schema-foreign

You can use the references('foreign_column').inTable('foreign_table) syntax or references('foreign_table.foreign_column') syntax. Also see: http://knexjs.org/#Schema-inTable.

You can chain the reference method to the same statement where you are defining the column properties as suggested above or define the foreign relationship as a standalone statement, like this:

table.foreign('column').references('foreign_column').inTable('foreign_table).onDelete('...').onUpdate('...')

or

table.foreign('column').references('foreign_table.foreign_column').onDelete('...').onUpdate('...')

1 Like

I’m quite sure it’s typo or something similar somewhere.

Can you turn on database debugging
https://adonisjs.com/docs/4.1/database#_debugging

And share it’s output? (It should log all queries to terminal / stdout)

1 Like

Here’s the output from the debug:

{
  method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '3', 1 ],
  __knexQueryUid: '37712ecb-7daf-43a7-bfd3-3dd34f581547',
  sql: 'select * from `users` where `id` = ? limit ?'
}
{
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 3 ],
  __knexQueryUid: 'aa94149c-792b-45dd-9186-6db50638f9b9',
  sql: 'select `locations`.*, `location_user`.`location_id` as `pivot_location_id`, `location_user`.`user_id` as `pivot_user_id` from `locations` inner join `location_user` on `locations`.`id` = `location_user`.`location_id` where `location_user`.`user_id` = ?'
}

I’m surprised to see the ?s in there, but I have no idea what that means :slight_smile:

Any thoughts?

PS: Looks like the database debug docs are wrong. It references database/config.js, as opposed to config/database.js. Is there somewhere I can flag that to be changed?

Thanks!

1 Like

Thanks ndianabasi, appreciate the kind words.

I have read a fair amount into knex, and I think I’m okay with building up the queries. I was under the impression that it was Lucid where I was falling down a bit :slight_smile:

I’m going to look into changing the schemas as suggested above, but it brings another question to mind. That is how am I able to alter the database along the lines that you’ve mentioned, without dropping all tabled and creating them again using a migration:refresh? It does my head in that I have to lose all my data when that happens, and I have no idea how that would be feasible in a production environment.

Many thanks again!

1 Like

Good morning from here. You can create a new migration and add just the foreign relationship statements.

adonis make:migration LocationUser

Choose select Schema from the options.

Then add the last two lines I suggested.

1 Like

I’m embarrassed to say that I have found the problem. In an attempt to restructure the databases, I deleted all the data from them. So there were in fact no locations to return :man_facepalming:

Now that there have been some locations added, I can see that the object being returned has data in it, and the right number of rows, but I can’t seem to access the information in it. Everything I try returns undefined.

So far I have tried the following in my vew:

        @each(location in locations)

        <li>{{ loction.id }}</li>
        <li>{{ loction.location_id }}</li>
        <li>{{ loction.user_id }}</li>
        <li>{{ loction.name }}</li>

        @endeach

Nothing seems to be working.

Any advice on getting the info I need?

Thanks!

I think you must check this link
https://adonisjs.com/docs/4.1/database#_debugging

:man_facepalming:

And now I’m embarrassed again!!!

loction !== location

I really do love spelling mistakes!

Thanks again to everyone for their help! I wouldn’t have got there without you.

I expect I’ll be back for another dumb problem :rofl:

2 Likes

:joy: :joy: :joy: :joy: :joy:

All the best, mate!

2 Likes

Sorry to dredge up a dead thread, if you would prefer me to create a new post, please just say.

I have implemented your suggestions around adding foreign relationships in the Locations User Schema. I have added the following migration below for anyone that might be interested.

Now that that’s done, I have been able to remove the await user.locations().detach() line from my delete controller.

However, if I update the locations allocated to the users, that doesn’t seem to make any changes at all. As such I still have the await user.locations().sync(locations) line in my update controller. Is this the expected behaviour? If so, what is the point of .onUpdate('CASCADE')? If not, what am I doing wrong? :slight_smile:

Many thanks as always!

'use strict'

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

class LocationUserSchema extends Schema {
  up () {
        this.table('location_user', (table) => {
      table.foreign('location_id')
        .references('id')
        .inTable('locations')
        .onDelete('CASCADE')
        .onUpdate('CASCADE')
      table.foreign('user_id')
        .references('id')
        .inTable('users')
        .onDelete('CASCADE')
        .onUpdate('CASCADE');
    })
  }

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

module.exports = LocationUserSchema

It seems you removed the column definitions from the LocationUserSchema or did you omit them when you pasted the code? The foreign relationship definitions should come below the column definitions you had already.

The up method should look like this:

up () {
  this.table('location_user', (table) => {
    table.increments()
    table.integer('location_id').unsigned().index('location_id')
    table.integer('user_id').unsigned().index('user_id')
    
    table.foreign('location_id')
      .references('id')
      .inTable('locations')
      .onDelete('CASCADE')
      .onUpdate('CASCADE')
    
    table.foreign('user_id')
      .references('id')
      .inTable('users')
      .onDelete('CASCADE')
      .onUpdate('CASCADE');
  })
}

Yes, that is the expected behaviour. You have to manually sync or detach/attach the locations.

I prefer to detach then attach the new values to avoid you appending new rows if the intention was to replace all current locations assigned to the user with the new locations.

await user.locations().detach()
await user.locations().attach(locations)
// locations being an array of locations

I did remove the column definitions from the updated schema. The reason for that is that it was editing a table that already existed with those columns. I assumed that I would get a duplicate column error if I specified them again. Is that not correct?

And thank you for confirming that I still need to sync the locations when editing. Could you let me know what the point of the .onUpdate('CASCADE') is then please? What does this actually do?

Many thanks again for all your help!

I haven’t used .onUpdate(‘CASCADE’) for such reason before, but it’s quite interesting and it should work after some fast Googleing

But there might be case that Lucid still is referencing to old objects. Lucid is not aware of onUpdate() and thus not updating objects (there’s no such functionality)
I’m not sure if this might be the case or not.

Take a look into DB right after it and see if values are correct in database. If they are correct in DB but Lucid is still returning something old, then it might be because it’s not aware of new IDs