sequelize 之预加载

预加载

• 当你从数据库检索数据时，也想同时获得与之相关联的查询，这被称为预加载。

User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "Instruments": [{
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "userId": 1
      }]
    }]
  */
})
// 请注意，访问者（结果实例中的 Instruments 属性）是复数形式，因为关联是多对一的。
// 如果定义模型关系时有设置别名，用inclue查询时也要有正确的别名

• 当使用 include.where 过滤一个预加载的模型时，include.required 被隐式设置为 true。 这意味着内部联接完成返回具有任何匹配子项的父模型。

User.findAll({
    include: [{
        model: Tool,
        as: 'Instruments',
        where: { name: { [Op.like]: '%ooth%' } }
    }]
}).then(users => {
    console.log(JSON.stringify(users))

    /*
      [{
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],

      [{
        "name": "John Smith",
        "id": 2,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],
    */
  })

• 可以使用$nested.column$将inclued 里面的where放到顶层

User.findAll({
    where: {
        '$Instruments.name$': { [Op.iLike]: '%ooth%' }
    },
    include: [{
        model: Tool,
        as: 'Instruments'
    }]
}).then(users => {
    console.log(JSON.stringify(users));

    /*
      [{
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],

      [{
        "name": "John Smith",
        "id": 2,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],
    */

• 包括所有模型，可以使用 all：true 传递单个对象：

User.findAll({ include: [{ all: true }]});

• 包括软删除的记录,可以通过将 include.paranoid 设置为 false 来实现

User.findAll({
    include: [{
        model: Tool,
        where: { name: { [Op.like]: '%ooth%' } },
        paranoid: false // query and loads the soft deleted records
    }]
});

• 嵌套多层的预加载

  User.findAll({
  include: [
  {model: Tool, as: 'Instruments', include: [
    {model: Teacher, include: [ /* etc */]}
  ]}
  ]
  }).then(users => {
  console.log(JSON.stringify(users))
  
  /*
  [{
    "name": "John Doe",
    "id": 1,
    "createdAt": "2013-03-20T20:31:45.000Z",
    "updatedAt": "2013-03-20T20:31:45.000Z",
    "Instruments": [{ // 1:M and N:M association
      "name": "Toothpick",
      "id": 1,
      "createdAt": null,
      "updatedAt": null,
      "userId": 1,
      "Teacher": { // 1:1 association
        "name": "Jimi Hendrix"
      }
    }]

  • 要返回所有父实例，您应该添加 required: false。
    

    User.findAll({
    include: [{
    model: Tool,
    as: 'Instruments',
    include: [{
      model: Teacher,
      where: {
        school: "Woodstock Music School"
      },
      required: false
    }]
    }]
    }).then(users => {
    /* ... */
    })
    // 以上查询将返回所有用户及其所有乐器，但只会返回与 Woodstock Music School 相关的老师。