has many through

[nibarulabs]

I have the 'old user and roles issue. I've done this numerous time in Rails and other frameworks, so it should be well understood.

Each model has the Store import for postgres at the top of the file:

const Store = require('openrecord/store/postgres');

class User extends Store.BaseModel {
    static definition() {
        this.hasMany('roles_users', {model: 'RoleUser', from: 'id', to: 'user_id'});
        this.hasMany('roles', {model: 'Role', through: 'roles_users'});
    }
}
module.exports = User;

class Role extends Store.BaseModel {
    static definition() {
        this.hasMany('roles_users', {model: 'RoleUser', from: 'id', to: 'role_id'});
        this.hasMany('users', {model: 'User', through: 'roles_users'});
    }
}
module.exports = Role;

class RoleUser extends Store.BaseModel {
    static definition() {
        this.belongsTo('roles', {model: 'Role', from: 'role_id', to: 'id'});
        this.belongsTo('users', {model: 'User', from: 'user_id', to: 'id'});
    }
}
module.exports = RoleUser;

select * from roles;
 id | name  |         created_at         |         updated_at         
----+-------+----------------------------+----------------------------
 1 | member | 2020-08-09 22:47:31.718-07 | 2020-08-09 22:47:31.718-07 

select * from users;
 id | name  |         created_at         |         updated_at         
----+-------+----------------------------+----------------------------
 1 | Mike | 2020-08-09 22:47:31.718-07 | 2020-08-09 22:47:31.718-07 

select * from roles_users;
 id | role_id | user_id |         created_at         |         updated_at         | creator_id | updater_id 
----+---------+---------+----------------------------+----------------------------+------------+------------
  1 |       1 |       1 | 2020-08-05 15:08:52.649-07 | 2020-08-05 15:08:52.649-07 |          1 |          1

When I load the User and query for it and include roles I can get the expected role back. But when I create a new user and then add a role via:

const role = await Role.find(1);
const user = await User.create({name: 'John'});
user.roles.add(role);

I get this error:

You need to implement your own `collection` function! (relation: roles)

I'm at a loss at this point. I've tried different combinations of options on all 3 models with no luck. I'd prefer to use HMT but I'm starting to try to rework this to get it to work. Downside of that though is that I will have other HMT relations and if I can't get this one to work, then the others won't either.

Maybe I'm missing something simple? Thanks.

[PhilWaldmann]

Hi @nibarulabs

You'll need to define the target relation. e.g.: this.hasMany('roles', {model: 'Role', through: 'roles_users', relation: 'roles'});. There is currently no default to use the relation name as the target relation name.

btw:

const role = await Role.find(1);
const user = await User.create({name: 'John'});
user.roles.add(role);
await user.save() // <--- don't forget!

[nibarulabs]

Hi @PhilWaldmann, thanks for your reply.

Hmm, still getting the same error. I actually played with adding relation last night and was seeing the same behavior.

I've noticed something else too that might be fine, but wanted to double check. I added some console log to print out the role after I look it up and I noticed it adds the has many attributes to the class even though I'm using the through.

role: Role {
  relations: {},
  attributes: {
    id: 1,
    name: 'member',
    created_at: 2020-08-05T22:08:52.630Z,
    updated_at: 2020-08-05T22:08:52.629Z,
    creator_id: 1,
    updater_id: 1,
    rolesUserIds: null,
    userIds: null
  }
...

I have all my models introspecting the data structure from the table and my postgres table definition for roles looks pretty standard:

                                       Table "public.roles"
   Column   |           Type           | Collation | Nullable |              Default              
------------+--------------------------+-----------+----------+-----------------------------------
 id         | bigint                   |           | not null | nextval('roles_id_seq'::regclass)
 name       | character varying(255)   |           |          | 
 created_at | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 updated_at | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 creator_id | bigint                   |           |          | 
 updater_id | bigint                   |           |          | 
...

There are no rolesUserIds or userIds arrays on that table. I haven't reached that part of the OR code, but I'm assuming hasMany is adding it and not removing it when a through is used? Does it matter?

I'm not trying to confuse the issue, but I suspect that having those attributes there and the relations object is empty - maybe there's some issue there?

Feels like I'm close and I'm just missing something small.. I'm going to go through your test code to see how you're testing things for through to see if I can spot something there.

[nibarulabs]

Ok, I found a workaround.

In my user.js I added the following function:

async addRole(role) {
    const RoleUser = require('./role-user');
    const sql = `INSERT INTO roles_users (user_id,role_id) values (${this.id},${role.id})`;
    console.log("sql:", sql)
    const result = await RoleUser.raw(sql);
    await this.roles;
}

This now allows me to do something like:

const role = await Role.find(1);
const user = await User.create({name: 'John'});
user.addRole(role);

I get that it's not ideal and not really 'correct', but it gets the association working for me, albeit with more db hits (I don't need to over optimize at this point and can fix later).

If there are any suggestions as to what else I can try to get the user.roles.add(role) syntax working, then I can try that when it becomes available. For now, this allows me to keep moving forward.

[PhilWaldmann]

Yeah, that's not the prettiest solution. I'll try to rebuild your situation in a test case to see where the problem is. I hope I've time to do so in the next few days.