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

Question

注意：我没有分享数据库架构，因为我只是在寻求有关最后一步的帮助，即在两个子查询上进行“左外连接”。

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

我需要使用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');

我不太喜欢使用bookshelf.knex.raw来进行左外连接，因为knex.raw和bookshelf的输出结果不同。

有没有一种直接使用bookshelf库进行“左外连接”的方法。

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

- j10

---

另外，由于我正在使用bookshelf-paranoia模块进行软删除，因此需要手动添加Action.deleted_At！= null。 - j10

我可以建议您从简化查询开始。有一些常量可以进行传播;第一个派生表可以完全删除;等等。 - Rick James

Answer 1

我认为你的主要问题在于你像使用knex一样使用了Bookshelf。 Bookshelf 应该与你定义的模型一起使用，然后对它们进行查询。

这里是一个作为模型应该拥有的示例：

// 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,
};

您可以通过以下查询语句获取结果：

const Model = require('model.js');

Model.Action
    .where({ id: 3 })
    .fetchAll({ withRelated: ['createdBy', 'assignedTo', 'assignedTo.employee'] })
    .then(data => {
        // Do what you have to do
    });

仅使用Bookshelf无法实现您想要的目标。您可能需要使用knex进行第一次查询，以获取Action 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
})

然后使用恢复的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
    });

请注意，用于连接的列必须在每个表的列列表中。如果省略这些列，则将选择所有列。

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

- Eric Ly