How to join table one to many using objection js

Question

I just used knex objection js. Currently I have a one to many table relationship as follows.

Table user_groups:

   Column   |           Type           | Collation | Nullable |                  Default                   | Storage  | Stats target | Description 
------------+--------------------------+-----------+----------+--------------------------------------------+----------+--------------+-------------
 id         | integer                  |           | not null | nextval('dl_user_groups_id_seq'::regclass) | plain    |              | 
 title      | character varying(128)   |           | not null |                                            | extended |              | 
 slug       | character varying(128)   |           | not null |                                            | extended |              | 
 desc       | character varying(512)   |           |          |                                            | extended |              | 
 created_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                          | plain    |              | 
 updated_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                          | plain    |              | 

Table roles:

    Column     |           Type           | Collation | Nullable |               Default                | Storage  | Stats target | Description 
---------------+--------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id            | integer                  |           | not null | nextval('dl_roles_id_seq'::regclass) | plain    |              | 
 user_group_id | integer                  |           | not null |                                      | plain    |              | 
 title         | character varying(128)   |           | not null |                                      | extended |              | 
 slug          | character varying(128)   |           | not null |                                      | extended |              | 
 desc          | character varying(512)   |           |          |                                      | extended |              | 
 created_at    | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                    | plain    |              | 
 updated_at    | timestamp with time zone |           | not null | CURRENT_TIMESTAMP                    | plain    |              | 

This may models roles.js:

'use strict';

const { Model } = require('objection');
const UserGroupsModel = require(__basedir + '/app/models/user_groups');


class RolesModel extends Model {
    static get tableName() {
        return 'dl_roles';
    }

    static get jsonSchema() {
        return {
            type: 'object',
            required: ['user_group_id', 'title'],
            properties: {
                id: { type: 'integer' },
                user_group_id: { type: 'integer' },
                title: { type: 'string', minLength: 1, maxLength: 128 },
                slug: { type: 'string', minLength: 1, maxLength: 128 },
                desc: { type: 'string', maxLenght: 512 }
            }
        }
    }

    static get relationMappings() {
        return {
            user_groups: {
                relation: Model.BelongsToOneRelation, 
                modelClass: UserGroupsModel, 
                join: {
                    from: 'dl_roles.user_group_id',
                    to: 'dl_user_groups.id'
                }
            }
        };
    };
}

module.exports = RolesModel;

user_groups.js

'use strict';

const { Model } = require('objection');
const Crypto = require('crypto');
const RolesModel = require(__basedir + '/app/models/roles');


class UserGroupsModel extends Model {
    static get tableName() {
        return 'dl_user_groups';
    }

    static get jsonSchema() {
        return {
            type: 'object',
            required: ['title'],
            properties: {
                id: { type: 'integer' },
                title: { type: 'string', minLength: 1, maxLength: 128 },
                slug: { type: 'string', minLength: 1, maxLength: 128 },
                desc: { type: 'string', maxLength: 512 }
            }
        }
    }

    static get relationMappings() {
        return {
            roles: {
                relation: Model.HasManyRelation, 
                modelClass: RolesModel,
                join: {
                    from: 'dl_user_groups.id',
                    to: 'dl_roles.user_group_id'
                }
            }
        };
    };
}

module.exports = UserGroupsModel;

This is my query I want to convert to objection js.

select ug.title as group, r.title as role, r.slug, r.desc 
from public.dl_roles r 
join public.dl_user_groups ug on r.user_group_id = ug.id;

Results:

 group  |     role      |     slug      | desc 
--------+---------------+---------------+------
 TELKM  | Administrator | administrator | 
 TELKM  | Instructor    | instructor    | 
 TELKM  | Learner       | learner       | 
 TELKM  | Guest         | guest         | 
 APPS   | Apps          | Apps          | 

This is my objection query:

'use strict';

const RolesModel = require(__basedir + '/app/models/roles');
const Slugify = require('slugify');


exports.getRolesAll = async function() {
    return new Promise(async (resolve, reject) => {
        try {
            let _data = await RolesModel
            .query()
            .joinRelated('user_groups');
            return resolve({
                statusCode: 200,
                data: _data,
                message: 'All data'
            });
        } catch (err) {
            return reject(err);
        }
    });
};

I have tried using joinRelated and eager but not work. I hope advanced can give me the answer how to join one to many and get all field from parent table like my query above.

Thanks.

Answer 1

I have solved my problem by using raw query. This is my query use objection js.

RolesModel
.query()
.select('user_groups.*', 'roles.*')
.join('user_groups', 'user_groups.id', 'roles.user_group_id');

I hope this can help someone to.