为bookshelf.js中的子查询重写左外连接

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.

我不喜欢用书架。raw格式用于使用左侧外部连接作为knex提供的输出。生和书架上是不同的。

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.

我查看了代码，但似乎leftOuterJoin只将表名作为第一个参数，我需要的是查询。

1 个解决方案

#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.

我认为你的主要问题是你使用的书架就像你使用的knex。Bookshelf是用来与您要定义和查询的模型一起使用的。

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

你想要实现的目标是不可能的，在书架上只有一个查询。您可能需要使用knex进行第一个查询，以获取一个动作id列表，然后将它们提供给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

然后使用恢复的id获取类似这样的书架查询

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.

默认情况下，Bookshelf将检索所有列和所有根对象。默认是左外连接。

#1