Note : I have not shared database schema as I am mainly looking for a help only w.r.t. last step which is 'left outer join' on 2 sub-queries.
select * from (select id from Action where id = 3) AS act1 left Outer Join (select Action.name, completed_At as completedAt, deadline, notes, ActionAssignedTo.action_Id as actionId, from Action inner join Employee on Action.created_By_Id = Employee.id and Employee.vendor_Id = 2 inner join ActionAssignedTo on Action.id = ActionAssignedTo.action_Id and ActionAssignedTo.action_Id = 3 where Action.created_By_Id = 7 group by Action.id limit 2) AS act2 on act1.id = act2.actionId I need to write this above query using Bookshelf
let options = {columns: [ 'Action.name', 'completed_At as completedAt', 'deadline', 'notes', 'ActionAssignedTo.action_Id as actionId', ]}; let action2 = new Action(); action2.query().innerJoin('Employee', function () { this.on('Action.created_By_Id', 'Employee.id') .andOn('Employee.vendor_Id', bookshelf.knex.raw(1)); }); action2.query().innerJoin('ActionAssignedTo', function () { this.on('Action.id', 'ActionAssignedTo.action_Id') .andOn('ActionAssignedTo.action_Id', bookshelf.knex.raw(5)); }); action2.query().where(function() { this.where('Action.created_By_Id', empId) }); action2.query().groupBy('Action.id'); action2.query().limit(2); action2.query().columns(options.columns); let action1; action1 = Action.where('id', actionId); action1.query().columns('id'); return bookshelf.knex.raw('select * from ' + '(' + action1.query().toString() + ') AS act1' + ' left Outer Join ' + '(' + action2.query().toString() + ') AS act2' + ' on act1.id = act2.actionId'); I am not keen on using bookshelf.knex.raw for using the left Outer Join as the output given by knex.raw and bookshelf differ.
Is there a way I can do the 'left Outer Join' directly using bookshelf library.
I looked into the code but it seems leftOuterJoin only takes table name as the first parameter and what I need is a query.
1 Answers
Answers 1
I think your main problem is that you're using Bookshelf like you would be using knex. Bookshelf is meant to be used with models you would define and then query on them.
Here is an example of what you should have as model
// Adding registry to avoid circular references // Adding camelcase to get your columns names converted to camelCase bookshelf.plugin(['bookshelf-camelcase', 'registry']); // Reference: https://github.com/brianc/node-pg-types // These two lines convert all bigint values coming from Postgres from JS string to JS integer. // Removing these lines will mess up with Bookshelf count() methods and bigserial values pg.types.setTypeParser(20, 'text', parseInt); const Action = db.bookshelf.Model.extend({ tableName: 'Action', createdBy: function createdBy() { return this.belongsTo(Employee, 'id', 'created_By_Id'); }, assignedTo: function assignedTo() { return this.hasMany(ActionAssignedTo, 'action_id'); }, }); const Employee = db.bookshelf.Model.extend({ tableName: 'Employee', createdActions: function createdActions() { return this.hasMany(Action, 'created_By_Id'); }, }); const ActionAssignedTo = db.bookshelf.Model.extend({ tableName: 'ActionAssignedTo', action: function action() { return this.belongsTo(Action, 'id', 'action_Id'); }, employee: function employee() { return this.belongsTo(Employee, 'id', 'employee_Id'); }, }); module.exports = { Action: db.bookshelf.model('Action', Action), Employee: db.bookshelf.model('Employee', Employee), ActionAssignedTo: db.bookshelf.model('ActionAssignedTo', ActionAssignedTo), db, }; You would then be able to fetch your results with a query like this
const Model = require('model.js'); Model.Action .where({ id: 3 }) .fetchAll({ withRelated: ['createdBy', 'assignedTo', 'assignedTo.employee'] }) .then(data => { // Do what you have to do }); What your want to achieve is not possible with only one query in Bookshelf. You probably need to do a first query using knex to get a list of Action ids and then give them to Bookshelf.js
db.bookshelf.knex.raw(` select ActionAssignedTo.action_Id as actionId, from Action inner join Employee on Action.created_By_Id = Employee.id and Employee.vendor_Id = ? inner join ActionAssignedTo on Action.id = ActionAssignedTo.action_Id and ActionAssignedTo.action_Id = ? where Action.created_By_Id = ? group by Action.id limit ?`, [2, 3, 7, 2] ) .then(result => { const rows = result.rows; // Do what you have to do }) And then use the recovered Ids to get your Bookshelf query like this
Model.Action .query(qb => { qb.whereIn('id', rows); }) .fetchAll({ withRelated: [{ 'createdBy': qb => { qb.columns(['id', 'firstname', 'lastname']); }, 'assignedTo': qb => { qb.columns(['action_Id', 'employee_Id']); }, 'assignedTo.employee': qb => { qb.columns(['id', 'firstname', 'lastname']); }, }], columns: ['id', 'name', 'completed_At', 'deadline', 'notes'] }) .fetchAll(data => { // Do what you have to do }); Note that the columns used for joins MUST BE in the columns list for each table. If you omit the columns, all the columns will be selected.
By default, Bookshelf will retrieve all columns and all root objects. The default is kind of LEFT OUTER JOIN.
0 comments:
Post a Comment