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.

I would suggest that you first try to make simpler test to figure out how those relation mappings and eager queries work. So just declare couple of models and do the query and strip everything else out. It is hard also for you to try to figure out how debug code that has so many potential places causing the problems. When you have made small pieces working separately first, then you can start trying to combine them to complete solution. https://stackoverflow.com/help/minimal-reproducible-example — Mikael Lepistö, Sep 04 '20 at 07:47

score 3 · Accepted Answer · answered Sep 04 '20 at 10:24

3

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.

answered Sep 04 '20 at 10:24

Sukma Saputra

1,539
17
32

1

If you have a different schema for these 2 tables , add .join('.user_groups', .... – lakjeewa Wijebandara Aug 30 '21 at 08:33

How to join table one to many using objection js

1 Answers1