Can't get relations to work about openrecord

[PhilWaldmann]

Awesome! Thanks Matheus

[PhilWaldmann]

Hi Matheus,
without further investigations I would say that your relationship names and the names in your include statement are not the same. clutch_module vs clutch_modules. I think that‘s the poblem!
I would suggest to put your hasMany relations names in plural - it’s nicer to ready.

Thanks,
Philipp

[matheusgrieger]

Nope, doesn't work either:

module.exports = function ClutchUsers () {
  this.hasMany('clutch_user_permissions')
      .hasMany('clutch_modules', {
        through: 'clutch_user_permissions',
        primary_key: 'id',
        foreign_key: 'user'
      })

      .scope('active', function() {
        this.where({status: 1})
      })
}

The "Can't find relation "clutch_modules" for ClutchUsers" persists...

[PhilWaldmann]

Hi Matheus,

yep, I've found the problems:

• The hasMany through relation need the name of the target relation clutch_module - something that could be done better by OpenRecord (try the singular version of the relation name)
• The hasMany through relation does not need to know the primary or foreign key - it uses it from it's target relation.
• The relations of the ClutchUserPermission module needed extra info regarding it's relation keys (module and user). OpenRecord was searching for a user_id and module_id field.

here is a working example (with an sqlite3 store)

var OpenRecord = require('./store')

var store = new OpenRecord({
  type: 'sqlite3',
  file: 'db-test.sqlite3'
})

/*
CREATE TABLE clutch_users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, status integer);
CREATE TABLE clutch_user_permissions(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER, module INTEGER);
CREATE TABLE clutch_modules(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
INSERT INTO clutch_users(name, status) VALUES ('A', 1);
INSERT INTO clutch_users(name, status) VALUES ('B', 0);
INSERT INTO clutch_modules(name) VALUES ('M1');
INSERT INTO clutch_modules(name) VALUES ('M2');
INSERT INTO clutch_user_permissions(user, module) VALUES (1, 1);
INSERT INTO clutch_user_permissions(user, module) VALUES (1, 2);
INSERT INTO clutch_user_permissions(user, module) VALUES (2, 2);
*/

store.Model('ClutchUser', function ClutchUsers() {
  this.hasMany('clutch_user_permissions', {foreign_key: 'user'})
  .hasMany('clutch_modules', {through: 'clutch_user_permissions', relation: 'clutch_module'})

  .scope('active', function() {
    this.where({status: 1})
  })
})

// ClutchUserPermissions.model.js
store.Model('ClutchUserPermission', function() {
  this.belongsToMany('clutch_user', {primary_key: 'user'})
  .belongsToMany('clutch_module', {primary_key: 'module'})
})

// ClutchModules.model.js
store.Model('ClutchModule', function() {
  this.hasMany('clutch_user_permissions', {foreign_key: 'module'})
  .hasMany('clutch_users', {through: 'clutch_user_permissions'})
})

store.ready(function(){
  const ClutchUsers = store.Model('ClutchUser')
  ClutchUsers.active().include('clutch_modules').exec((records) => {
    console.log(records)
  })
})

[matheusgrieger]

I see in your example that the clutch_user_permissions table you created has an id column. Mine doesn't, only user and module columns, that are foreign keys pointing to the other tables. Tried your solution, didn't work either... Should I be using an id column as well?

Edit: tried adding an id column to the table, still doesn't work 😢

[PhilWaldmann]

Hi Matheus,

nope, my example also works without the id column on clutch_user_permissions ! So that shouldn't be the problem - it's just a preference of designing you schema.

If my above example does not work for you, than there is something else fishy.
You are using [email protected] right? Wich database and version do you use?

[matheusgrieger]

Ok so I deleted everything and started from scratch and now it's working. Probably was some mispelling that I didn't see before. Thank you for your help and sorry for taking so much of your time. Your library works great!

Eager to see the new version and full documatation. Keep up the good work and support.