How to joined 2 tables and output fields from the adjacent table?

Question

everybody, could you please tell me

I use the objection.js (ORM) library for knex.js

joined Users and Roles tables, everything is great.

I use it:

const users = await User.query().eager().roles')

I'm getting it:

{"id":1, "email": "ann@mail.com", "password": "qwe", "role":1, "roles":{"id":1, "name": "admin"}}

But how can I get a flat structure? :

{"id":1, "email": "ann@mail.com", "password": "qwe", "role "admin"}

score 1 · Answer 1 · answered Nov 04 '19 at 04:11

I would normally expect Objection to do this with a HasOneRelation relationship, so I'm going to assume that's what you have here:

{
  "id": 1,
  "email": "ann@mail.com",
  "password": "qwe",
  "role": 1,
  "roles": {
    "id":1,
    "name": "admin"
  }
}

If your users can only have one role at a time, that should be fine. To get a flat structure, you can either post-process it in JavaScript:

return {
  ...user,
  role: roles.name
}

or create a virtual attribute:

export default class User extends Model {
  static get virtualAttributes() {
    return ['roleName'];
  }

  roleName() {
    return this.roles.name;
  }
  // ...
}

This will not stop the roles object from being added, but it'll provide a first-level alias for the name in the JSON.

How to joined 2 tables and output fields from the adjacent table?

1 Answers1