Many-to-Many-to-Many relationship in Objection.js

Question

I have a project where an user can have many platforms. These platforms can have many passwords. Currently I have following database structure:

Im trying to use eager loading to get the following object:

{
    "id": 1,
    "username": "Keith",
    "platforms": [
        {
            "id": 1,
            "name": "Jira",
            "passwords": [
                {
                    "id": 1,
                    "password": "hash"
                },
                {
                    "id": 2,
                    "password": "otherhash"
                }
            ]
        },
        {
            "id": 2,
            "name": "Confluence",
            "passwords": [
                {
                    "id": 3,
                    "password": "anotherhash"
                },
                {
                    "id": 4,
                    "password": "anotherone"
                }
            ]
        }
    ]
}

I spent a few hours and couldnt figure out. How could I define the relations to get this structure? Is this possible?

score 2 · Accepted Answer · answered Apr 08 '19 at 19:35

As far as I know that is not possible to do without creating own model for that 3-way join table.

So models would look something like this:

class User extends objection.Model {
  static get tableName() {
    return 'user';
  }

  static get relationMappings() { 
    return {
      platformPasswords: {
        relation: Model.HasManyRelation,
        modelClass: UserPlatformPassword,
        join: {
          from: 'user.id',
          to: 'user_platform_password.user_id'
        }
      }
    }
  }
}

class Platform extends objection.Model {
  static get tableName() {
    return 'platform';
  }
}

class Password extends objection.Model {
  static get tableName() {
    return 'password';
  }
}

class UserPlatformPassword extends objection.Model {
  static get tableName() {
    return 'user_platform_password';
  }

  static get relationMappings() { 
    return {
      password: {
        relation: Model.HasOne,
        modelClass: Password,
        join: {
          from: 'user_platform_password.password_id',
          to: 'password.id'
        }
      },
      platform: {
        relation: Model.HasOne,
        modelClass: Platform,
        join: {
          from: 'user_platform_password.platform_id',
          to: 'platform.id'
        }
      }
    }
  }
}

Maybe there are some other ways to model those relations at least in a way that they work when doing eager selects, but I'm having hard time to understand how it could work in case when you would like to insert / upsert that nested data, where multiple relations are dealing with different fields of the same join table.

Thanks! What Im looking for is indeed impossible. I ended up changing my database structure to this one: https://jsfiddle.net/1bwLomgz/. Now eager loading is easier, but I feel like the database gets more difficult to read... So I wonder whats the best way/practice to implement this kind of many-to-many-to-many relation — Rashomon, Apr 09 '19 at 08:08
Maybe by having 2 separate join tables, one for platforms, which links user and platform and then second one for passwords which links user, password and platform with composite key having user_id and platform_id set https://vincit.github.io/objection.js/recipes/composite-keys.html#examples or in case of postgresql you could also store list of passwords in JSONB column as an extra field in join table. It is mentioned in comments in last example here https://vincit.github.io/objection.js/api/model/static-properties.html#static-relationmappings — Mikael Lepistö, Apr 09 '19 at 08:20

Many-to-Many-to-Many relationship in Objection.js

1 Answers1