Sequelize-nodejs-4-Model usage

Data retrieval / Finders

Finder methods are intended to query data from the database. They do not return plain objects but instead return model instances. Because finder methods return model instances you can call any model instance member on the result as described in the documentation for instances.

Finder方法用于从数据库中查询数据。它们不返回普通对象，而是返回模型实例。因为Finder方法返回的是模型实例，所以你就可以像在文档instances所描述的那样调用结果中的任意的模型实例

In this document we'll explore what finder methods can do:

在该文档中将探索Finder方法可以做什么

find - Search for one specific element in the database

搜索数据库中的具体元素

// search for known ids
Project.findById(123).then(project => {
  // project will be an instance of Project and stores the content of the table entry
  // with id 123. if such an entry is not defined you will get null
})

// search for attributes
Project.findOne({ where: {title: 'aProject'} }).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
})


Project.findOne({
  where: {title: 'aProject'},
  attributes: ['id', ['name', 'title']]
}).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
  // project.title will contain the name of the project
})

findOrCreate - Search for a specific element or create it if not available

搜索具体元素并在没有时创建一个

The method findOrCreate can be used to check if a certain element already exists in the database. If that is the case the method will result in a respective instance. If the element does not yet exist, it will be created.

方法findOrCreate可以用于查看某个元素是否存在于数据库中。如果存在将返回相应的实例，否则就会创建一个

Let's assume we have an empty database with a User model which has a username and a job.

假设我们有一个带着包含username和job字段的User模型的空数据库

User
  .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  .spread((user, created) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /*
     findOrCreate returns an array containing the object that was found or created and a boolean that will be true if a new object was created and false if not, like so:

    [ {
        username: 'sdepold',
        job: 'Technical Lead JavaScript',
        id: 1,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      },
      true ]

 In the example above, the "spread" on line 39 divides the array into its 2 parts and passes them as arguments to the callback function defined beginning at line 39, which treats them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "true".)
    */
  })

The code created a new instance. So when we already have an instance ...

上面的代码创建了一个实例。如果我们已经有了实例的话：

User.create({ username: 'fnord', job: 'omnomnom' })
  .then(() => User.findOrCreate({where: {username: 'fnord'}, defaults: {job: 'something else'}}))
  .spread((user, created) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /*
    In this example, findOrCreate returns an array like this:
    [ {
        username: 'fnord',
        job: 'omnomnom',
        id: 2,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      },
      false
    ]
    The array returned by findOrCreate gets spread into its 2 parts by the "spread" on line 69, and the parts will be passed as 2 arguments to the callback function beginning on line 69, which will then treat them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "false".)
    */
  })

... the existing entry will not be changed. See the job of the second user, and the fact that created was false.

存在的条目不会被改变

findAndCountAll - Search for multiple elements in the database, returns both data and total count

搜索数据库中的多个元素，返回数据和总计数

This is a convenience method that combinesfindAll and count (see below) this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query:

这是一个包含combinesfindAll和count两个方法的更方便的方法（如下）。当你想要解决相关页码的查询，该页码有着带有limit和offset的想要得到的检索数据并且还想知道匹配该查询的记录总数。

The success handler will always receive an object with two properties:

成功的处理器将总是得到带有下面两个属性的对象

• count - an integer, total number records matching the where clause and other filters due to associations
• rows - an array of objects, the records matching the where clause and other filters due to associations, within the limit and offset range

Project
  .findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })
  .then(result => {
    console.log(result.count);
    console.log(result.rows);
  });

It support includes. Only the includes that are marked as required will be added to the count part:

支持includes属性。只有includes中标记成required的将会添加到计数部分中

Suppose you want to find all users who have a profile attached:

想要找到有着相关profile的所有用户：

User.findAndCountAll({
  include: [
     { model: Profile, required: true}
  ],
  limit: 3
});

Because the include for Profile has required set it will result in an inner join, and only the users who have a profile will be counted. If we remove required from the include, both users with and without profiles will be counted. Adding a where clause to the include automatically makes it required:

因为Profile的include中设置了required，所以将导致内连接，只有有着profile的用户会被计数。如果把required移除，有或没有profile的用户都会被计数。也可以通过在include中添加where子句使其required

User.findAndCountAll({
  include: [
     { model: Profile, where: { active: true }}
  ],
  limit: 3
});

The query above will only count users who have an active profile, because required is implicitly set to true when you add a where clause to the include.

上面的查询只有带有active profile的用户会被计数，因为当你添加where子句到include时required被隐式设置了。

The options object that you pass to findAndCountAll is the same as for findAll (described below).

传递给findAndCountAll的选项对象对findAll是相同的（如下）

findAll - Search for multiple elements in the database

查找在数据库中的多重元素

// find multiple entries
Project.findAll().then(projects => {
  // projects will be an array of all Project instances
})

// also possible:
Project.all().then(projects => {
  // projects will be an array of all Project instances
})

// search for specific attributes - hash usage
Project.findAll({ where: { name: 'A Project' } }).then(projects => {
  // projects will be an array of Project instances with the specified name
})

// search within a specific range
Project.findAll({ where: { id: [1,2,3] } }).then(projects => {
  // projects will be an array of Projects having the id 1, 2 or 3
  // this is actually doing an IN query
})

Project.findAll({
  where: {
    id: {
      [Op.and]: {a: 5},           // AND (a = 5)
      [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR a = 6)
      [Op.gt]: 6,                // id > 6
      [Op.gte]: 6,               // id >= 6
      [Op.lt]: 10,               // id < 10
      [Op.lte]: 10,              // id <= 10
      [Op.ne]: 20,               // id != 20
      [Op.between]: [6, 10],     // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
      [Op.in]: [1, 2],           // IN [1, 2]
      [Op.notIn]: [1, 2],        // NOT IN [1, 2]
      [Op.like]: '%hat',         // LIKE '%hat'
      [Op.notLike]: '%hat',       // NOT LIKE '%hat'
      [Op.iLike]: '%hat',         // ILIKE '%hat' (case insensitive)  (PG only)
      [Op.notILike]: '%hat',      // NOT ILIKE '%hat'  (PG only)
      [Op.overlap]: [1, 2],       // && [1, 2] (PG array overlap operator)
      [Op.contains]: [1, 2],      // @> [1, 2] (PG array contains operator)
      [Op.contained]: [1, 2],     // <@ [1, 2] (PG array contained by operator)
      [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
    },
    status: {
      [Op.not]: false           // status NOT FALSE
    }
  }
})

Complex filtering / OR / NOT queries

It's possible to do complex where queries with multiple levels of nested AND, OR and NOT conditions. In order to do that you can use or, and or notOperators:

可以将查询变复杂，通过多级嵌套AND, OR and NOT条件。为了这样你可以使用or，且or不是操作符

Project.findOne({
  where: {
    name: 'a project',
    [Op.or]: [
      { id: [1,2,3] },
      { id: { [Op.gt]: 10 } }
    ]
  }
})
//等同于
Project.findOne({
  where: {
    name: 'a project',
    id: {
      [Op.or]: [
        [1,2,3],
        { [Op.gt]: 10 }
      ]
    }
  }
})

Both pieces of code will generate the following:

上面的两个代码与下面的效果是相同的

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id` > 10)
)
LIMIT 1;

not example:

Project.findOne({
  where: {
    name: 'a project',
    [Op.not]: [
      { id: [1,2,3] },
      { array: { [Op.contains]: [3,4,5] } }
    ]
  }
});

Will generate:

上面的例子讲生成：

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND NOT (`Projects`.`id` IN (1,2,3) OR `Projects`.`array` @> ARRAY[3,4,5]::INTEGER[])
)
LIMIT 1;

Manipulating the dataset with limit, offset, order and group

通过limit, offset, order and group操作数据集

To get more relevant data, you can use limit, offset, order and grouping:

为了得到更大哦的相关数据，你可以使用limit, offset, order and grouping

// limit the results of the query
Project.findAll({ limit: 10 })

// step over the first 10 elements
Project.findAll({ offset: 10 })

// step over the first 10 elements, and take 2
Project.findAll({ offset: 10, limit: 2 })

The syntax for grouping and ordering are equal, so below it is only explained with a single example for group, and the rest for order. Everything you see below can also be done for group

grouping和ordering的语法是相同的，所以下面只解释一个group的例子，其他都为order，当然group也能实现同样的功能

Project.findAll({order: 'title DESC'})
// yields ORDER BY title DESC

Project.findAll({group: 'name'})
// yields GROUP BY name

Notice how in the two examples above, the string provided is inserted verbatim into the query, i.e. column names are not escaped. When you provide a string to order/group, this will always be the case. If you want to escape column names, you should provide an array of arguments, even though you only want to order/group by a single column

上面的两个例子中提供的字符串是逐字插入查询中的，即列名是不会遗漏的。当你提供字符串给order/group的时候都会是这样的。如果你想要逃避列名，你应该提供参数数组，即使你只想通过一列进行order/group

something.findOne({
  order: [
    // will return `name`
    ['name'],
    // will return `username` DESC
    ['username', 'DESC'],
    // will return max(`age`)
    sequelize.fn('max', sequelize.col('age')),
    // will return max(`age`) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    // will return otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    // will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
  ]
})

To recap, the elements of the order/group array can be the following:

回顾，可见order/group数组的元素可为：

• String - will be quoted
• Array - first element will be quoted, second will be appended verbatim
• Object -
  • Raw will be added verbatim without quoting
  • Everything else is ignored, and if raw is not set, the query will fail
• Sequelize.fn and Sequelize.col returns functions and quoted column names

Raw queries

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates an instance with functions for update, delete, get associations etc. If you have thousands of rows, this might take some time. If you only need the raw data and don't want to update anything, you can do like this to get the raw data.

有时你可能期待你将要展示一个巨大的数据集，并不进行处理。对于你选择的每一行，Sequelize都会创建其带有update, delete, get关联等函数的实例。如果你有很多行，将会使用很长时间。如果你只需要原始数据并不需要更新任何东西，你可以进行如下操作去得到原始数据

// Are you expecting a massive dataset from the DB,
// and don't want to spend the time building DAOs for each entry?
// You can pass an extra query option to get the raw data instead:
Project.findAll({ where: { ... }, raw: true })

count - Count the occurrences of elements in the database

计算数据库中出现的元素

There is also a method for counting database objects:

Project.count().then(c => {
  console.log("There are " + c + " projects!")
})

Project.count({ where: {'id': {[Op.gt]: 25}} }).then(c => {
  console.log("There are " + c + " projects with an id greater than 25.")
})

max - Get the greatest value of a specific attribute within a specific table

得到具体表中具体属性的最大值

And here is a method for getting the max value of an attribute:f

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.max('age').then(max => {
  // this will return 40
})

Project.max('age', { where: { age: { [Op.lt]: 20 } } }).then(max => {
  // will be 10
})

min - Get the least value of a specific attribute within a specific table

得到具体表中具体属性的最小值

And here is a method for getting the min value of an attribute:

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.min('age').then(min => {
  // this will return 5
})

Project.min('age', { where: { age: { [Op.gt]: 5 } } }).then(min => {
  // will be 10
})

sum - Sum the value of specific attributes

得到具体属性的值的总和

In order to calculate the sum over a specific column of a table, you can use the sum method.

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.sum('age').then(sum => {
  // this will return 55
})

Project.sum('age', { where: { age: { [Op.gt]: 5 } } }).then(sum => {
  // will be 50
})

Eager loading预先加载

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. Lets assume the following setup:

当你从数据库中检索数据时，这里有着同等的机会去与相同的查询关联-这被称为预先加载。在这后面的基本概念就是当你调用find o或findAll方法去查询某模型时，能够使用属性include去包含相关联的其他模型信息：

const User = sequelize.define('user', { name: Sequelize.STRING })
const Task = sequelize.define('task', { name: Sequelize.STRING })
const Tool = sequelize.define('tool', { name: Sequelize.STRING })

Task.belongsTo(User)
User.hasMany(Task)
User.hasMany(Tool, { as: 'Instruments' })//起别名

sequelize.sync().then(() => {
  // this is where we continue ...
})

OK. So, first of all, let's load all tasks with their associated user.

首先先加载所有带着相关user的task

Task.findAll({ include: [ User ] }).then(tasks => {
  console.log(JSON.stringify(tasks))

  /*
    [{
      "name": "A Task",
      "id": 1,
      "createdAt": "2013-03-20T20:31:40.000Z",
      "updatedAt": "2013-03-20T20:31:40.000Z",
      "userId": 1,
      "user": {
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z"
      }
    }]
  */
})

Notice that the accessor (the User property in the resulting instance) is singular because the association is one-to-something.

可见访问器（即在结果中的user）是单数(表示格式是{})，因为它和task之间的关系是一对多的关系

Next thing: Loading of data with many-to-something associations!

接下来是加载多对多关系的数据

User.findAll({ include: [ Task ] }).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",
      "tasks": [{
        "name": "A Task",
        "id": 1,
        "createdAt": "2013-03-20T20:31:40.000Z",
        "updatedAt": "2013-03-20T20:31:40.000Z",
        "userId": 1
      }]
    }]
  */
})

Notice that the accessor (the Tasks property in the resulting instance) is plural because the association is many-to-something.

可见访问器（即结果中的tasks属性）是复数（表示格式是[{},{}]），这是因为它和user之间是多对多的关系

If an association is aliased (using the as option), you must specify this alias when including the model. Notice how the user's Tools are aliased as Instruments above. In order to get that right you have to specify the model you want to load, as well as the alias:

如果为关联性起别名（即使用选项as来实现），你必须在包含模型时指定别名。

⚠️如上面声明user的Tools的别名为Instruments：

User.hasMany(Tool, { as: 'Instruments' })//起别名

为了保证正确性，你必须指定你想要加载的模型和别名：

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
      }]
    }]
  */
})

You can also include by alias name by specifying a string that matches the association alias:

一开始定义关系的时候就已经起完别名了，因此除了上面的方法，你还可以通过指定一个匹配关联别名的字符串，即别名的名字来包含它，如下两种方法：

User.findAll({ include: ['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
      }]
    }]
  */
})

User.findAll({ include: [{ association: '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
      }]
    }]
  */
})

When eager loading we can also filter the associated model using where. This will return all Users in which the where clause of Tool model matches rows.

在预先加载时，我们可以使用where来过滤关联模型。这将会返回所有匹配Tool模型的where子句的Users的行

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
        }]
      }],
    */
  })

When an eager loaded model is filtered using include.where then include.required is implicitly set to true. This means that an inner join is done returning parent models with any matching children.

当预先加载模型使用include.where进行过滤时，include.required已经隐式设置为true。这意味着内连接已经完成，返回带有匹配子模型的父模型

Top level where with eagerly loaded models

To move the where conditions from an included model from the ON condition to the top level WHERE you can use the '$nested.column$' syntax:

我们能够使用'$nested.column$'语法去将where条件从include模型、ON条件中移到最高级的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
        }]
      }],
    */

Including everything

To include all attributes, you can pass a single object with all: true:

包含所有的属性

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

Including soft deleted records

包含软删除记录

In case you want to eager load soft deleted records you can do that by setting include.paranoid to false

你可以通过设置include.paranoid为false来预先加载软删除记录：

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

Ordering Eager Loaded Associations

排序预先加载的关联性

In the case of a one-to-many relationship.

一对多的例子：

Company.findAll({ include: [ Division ], order: [ [ Division, 'name' ] ] });
Company.findAll({ include: [ Division ], order: [ [ Division, 'name', 'DESC' ] ] });
Company.findAll({
  include: [ { model: Division, as: 'Div' } ],
  order: [ [ { model: Division, as: 'Div' }, 'name' ] ]
});
Company.findAll({
  include: [ { model: Division, as: 'Div' } ],
  order: [ [ { model: Division, as: 'Div' }, 'name', 'DESC' ] ]
});
Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, Department, 'name' ] ]
});

In the case of many-to-many joins, you are also able to sort by attributes in the through table.

多对多的例子，可以通过属性进行分类

Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, DepartmentDivision, 'name' ] ]
});

Nested eager loading嵌套的预先加载

You can use nested eager loading to load all related models of a related model:

你可以使用嵌套的预先加载（即include嵌套include）去加载相关模型的关联模型

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"
        }
      }]
    }]
  */
})

This will produce an outer join. However, a where clause on a related model will create an inner join and return only the instances that have matching sub-models. To return all parent instances, you should add required: false.

这样会生成外连接。可是，在关联模型中的where子句将创建内连接并只返回有着匹配子模型的实例。为了返回所有父模型，需要添加required: false

User.findAll({
  include: [{
    model: Tool,
    as: 'Instruments',
    include: [{
      model: Teacher,
      where: {
        school: "Woodstock Music School"
      },
      required: false
    }]
  }]
}).then(users => {
  /* ... */
})

The query above will return all users, and all their instruments, but only those teachers associated with Woodstock Music School.

上面的查询将会返回所有的与Woodstock Music School关联的teachers users和他们的所有instruments

Include all also supports nested loading:

包含所有属性的查询也支持嵌套加载

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