Pivot Table Seeding

[maplenk]

I have 3 tables:

roles:
      table.increments()
      table.string('name', 80).notNullable().unique()
      table.string('slug', 80).notNullable().unique()
      table.string('description', 80)
      table.boolean('is_active', 80).defaultTo(true)
      table.timestamps()
permissions:
      table.increments()
      table.string('name', 80).notNullable().unique()
      table.string('slug', 80)
      table.string('description', 80)
      table.boolean('is_active', 80).defaultTo(true)
      table.timestamps()

permission_role:
      table.increments()
      table.integer('permission_id').notNullable().unsigned()
              .references('id').inTable('permissions').onDelete('CASCADE').onUpdate('RESTRICT')
      table.integer('role_id').notNullable().unsigned()
              .references('id').inTable('roles').onDelete('CASCADE').onUpdate('RESTRICT')
      table.boolean('is_active').defaultTo(true)
      table.timestamps()

Now I have 3 seeders one each for role -> permission -> permission_role Names are in ascending order. As in 01-RoleSeeder.js 02-PermissionsSeeder.js 03-RolePermissionSeeder.js

Whenever I run the seeders the second entry on 03-RolePermissionSeeder.js gives error. Error: insert into permission_role (permission_id, role_id) values ('1', '2') - ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (adonis.permission_role, CONSTRAINT permission_role_role_id_foreign FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE)

I have no idea what to do. @thetutlage

My seeder:

'use strict'
const Database = use('Database')
class PermissionRoleerSeeder {
  * run () {

    const mapping = [{"role_id":"1","permission_id":"1"}] // WORKS
   // const mapping = [{"role_id":"1","permission_id":"2"}] // DOESN'T WORK
   // const mapping = [{"role_id":"2","permission_id":"1"}] // DOESN'T WORK
   // const mapping = [{"role_id":"1","permission_id":"1"},{"role_id":"1","permission_id":"2"}] // DOESN'T WORK

    for (var i = 0; i < mapping.length; i++) {
      var id = yield Database
        .insert({ 
              role_id: mapping[i]['role_id'],
              permission_id: mapping[i]['permission_id']
            })
        .into('permission_role')
    }
  }
}
module.exports = PermissionRoleerSeeder

[webdevian]

@maplenk Have you tried using lucid methods for seeding? That way you can be sure model instances exists before trying to join them.

I use this for adding values to a pivot table (randomly, but it could be done more sequentially too):

  /**
   * Loop through offers and create fake clicks
   * for random users
   * @param {Integer} clickCount How many clicks per offer
   */
  * addClicks (clickCount) {
    let offers = yield Offer.all()
    offers = offers.value()

    let users = yield User.all()
    users = users.value()

    yield offers.map(function * (offer) {
      for (let i = 0; i < Math.floor(Math.random() * 10) + clickCount - 5; i++) {
        const user = users[Math.floor(Math.random() * users.length)]
        yield offer.clicks().attach([user.id])
      }
    })
  }

[maplenk]

This won't work either:

'use strict'
const Role = use('App/Model/Role')
const Permission = use('App/Model/Permission')
class PermissionRoleerSeeder {
  * run () {
    const super_admin = yield Role.find(1)
    yield super_admin.permissions().attach([1,6,7,18,19,20]) // Doesn't Work
    yield super_admin.permissions().attach([1]) // Works
  }
}
module.exports = PermissionRoleerSeeder

Gives:

Error: insert into `permission_role` (`permission_id`, `role_id`) values ('1', 1), ('6', 1), ('7', 1), ('18', 1), ('19', 1), ('20', 1) - ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`adonis`.`permission_role`, CONSTRAINT `permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE)

It only works with 1,1

[webdevian]

Is there a record in permissions for each of those IDs? You'll need to create/save them first

[maplenk]

@webdevian Yes there is. I run the Permission seeder before. Plus if I use the same query on mysql directly using phpmyadmin it works.

insert into `permission_role` (`permission_id`, `role_id`) values ('1', 1), ('6', 1), ('7', 1), ('18', 1), ('19', 1), ('20', 1)

[webdevian]

Is the permission seeder definitely running (and finishing) first? I ran into this problem with asynchronous behaviour and all the Seeder run methods starting at the same time

[maplenk]

How do I make them in sync?

[webdevian]

Set them up all in one seederclass each with a generator function. Then call them in sequence, with yield, from the run function.

class Permission_Roles_Seeder {
  * run () {
    yield this.createRoles()
    yield this.createPermissions()
    yield this.joinPermissions()
  }

 * createRoles () {
    // Create roles here
  }

  * createPermissions () {
    // Create permissions here
  }

  * joinPermissions () {
    // Attach permissions to roles here
  }
}

[maplenk]

@webdevian Thanks!! But for some reason multiple functions was giving error: SyntaxError: Unexpected token { So I pasted all the separate seeders in run and it worked.

But now when I try:

const role = yield Role.find(1)
const permission = yield Permission.find(1)
const roleperm = role.permissions().fetch()
const permrole = permission.roles().fetch()
response.send({role: role, roleperm: roleperm, permission: permission, permrole: permrole})

I get:

{
"role": {
"id": 1,
"name": "SUPER_ADMIN",
"slug": "super",
"description": null,
"is_active": 1,
"created_at": "2017-03-22 15:49:28",
"updated_at": "2017-03-22 15:49:28"
},
"roleperm": {},
"permission": {
"id": 1,
"name": "ADD_ORG",
"slug": null,
"description": null,
"is_active": 1,
"created_at": "2017-03-22 15:49:28",
"updated_at": "2017-03-22 15:49:28"
},
"permrole": {}
}

roleperm and permrole are empty.

[webdevian]

Whoops, fixed syntax errors above.

Do the records that have been created look ok?

Do you have belongsToMany relationships set up in the models? Do they have the correct fields configured etc?

[maplenk]

This somehow works:

const role = yield Role.query().with('permissions').where('id', 1).fetch()
const permission = yield Permission.query().with('roles').where('id', 1).fetch()

The models: Roles.js

'use strict'
const Lucid = use('Lucid')
class Role extends Lucid {
  static get table () {
    return 'roles'
  }
  permissions () {
    return this.belongsToMany('App/Model/Permission', 'permission_role')
  }
}
module.exports = Role

Permissions.js

'use strict'
const Lucid = use('Lucid')
class Permission extends Lucid {
  static get table () {
    return 'permissions'
  }
  roles () {
    return this.belongsToMany('App/Model/Role', 'permission_role')
  }
}
module.exports = Permission

I am so much confused right now. I was hoping to port my php application to adonis rather than laravel but now I am not sure :/

Plus is there a way I can run a specific seed and not all seed files?

[webdevian]

I'm not sure if there is out of the box. I use node cli arguments to control settings (like how many records to create), the same could definitely be done for choosing which seeders to run if you just had one run command that then fires off other seeders depending on the options given

[maplenk]

Still giving SyntaxError error :'(

class Permission_Roles_Seeder {
  * run () {
    yield this.createRoles()
    yield this.createPermissions()
    yield this.joinPermissions()
  }

 * createRoles {
    // Create roles here
  }

  * createPermissions {
    // Create permissions here
  }

  * joinPermissions {
    // Attach permissions to roles here
  }
}

[webdevian]

If you've got hardcoded data to seed, that will only ever be seeded once, it might make more sense to insert it in a migration than a seeder.

I see seeders more as a way of inserting fake data (again and again) for testing.

^^^ Refresh the page, missing brackets in function declaration was the problem

[maplenk]

Thanks got the braces.

Hmmm I should use migrations as well.

[maplenk]

What about this:

const role = yield Role.find(1)
const permission = yield Permission.find(1)
const roleperm = role.permissions().fetch()
const permrole = permission.roles().fetch()
response.send({role: role, roleperm: roleperm, permission: permission, permrole: permrole})

I get:

{
"role": {
"id": 1,
"name": "SUPER_ADMIN",
"slug": "super",
"description": null,
"is_active": 1,
"created_at": "2017-03-22 15:49:28",
"updated_at": "2017-03-22 15:49:28"
},
"roleperm": {},
"permission": {
"id": 1,
"name": "ADD_ORG",
"slug": null,
"description": null,
"is_active": 1,
"created_at": "2017-03-22 15:49:28",
"updated_at": "2017-03-22 15:49:28"
},
"permrole": {}
}

roleperm and permrole are empty.

[webdevian]

Ah I missed it, you need to yield the relational calls

const role = yield Role.find(1)
const permission = yield Permission.find(1)
const roleperm = yield role.permissions().fetch()
const permrole = yield permission.roles().fetch()
response.send({role: role, roleperm: roleperm, permission: permission, permrole: permrole})

[maplenk]

Thank you so much 😄