Sequelize-nodejs-11-Raw queries

Raw queries原始查询

就是使用了原始的查询语句，如UPDATE users SET y = 42 WHERE x = 12

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function sequelize.query.

当经常有使用更简单的执行原始/已经准备好的SQL查询的情况出现时，你可以使用函数sequelize.query。

By default the function will return two arguments - a results array, and an object containing metadata (affected rows etc.). Note that since this is a raw query, the metadata (property names etc.) is dialect specific. Some dialects return the metadata "within" the results object (as properties on an array). However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.

默认函数将返回两个参数-一个结果数组和一个包含元数据（被影响的行等数据）的对象。记住因为这是一个原始查询，元数据（属性名等）使用的语言是指定的。一些语言返回的元数据带有结果对象（像数组中的属性）。可是两个参数总是被返回的，但是对于MSSQL and MySQL来说这两个参数将是同个对象的两个引用

sequelize.query("UPDATE users SET y = 42 WHERE x = 12").spread((results, metadata) => {
  // Results will be an empty array and metadata will contain the number of affected rows.
})

In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:

在这个例子中，你不需要访问元数据，你可以传递一个查询类型去告诉sequelize怎样格式化结果。比如下面的简单select查询：

sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
  .then(users => {
    // We don't need spread here, since only the results will be returned for select queries
  })

Several other query types are available. Peek into the source for details

其他的查询类型也是可用的

A second option is the model. If you pass a model the returned data will be instances of that model.

模型的第二个选项。如果你在第二个选项中传递的是模型，那么返回数据将是模型的实例

// Callee is the model definition. This allows you to easily map a query to a predefined model
sequelize.query('SELECT * FROM projects', { model: Projects }).then(projects => {
  // Each record will now be a instance of Project
})

 

 

 

 

Replacements

Replacements in a query can be done in two different ways, either using named parameters (starting with :), or unnamed, represented by a ?. Replacements are passed in the options object.

在查询中替代能够以两种不同方式实现，要么使用命名变量（以：开始），要么不命名，以？来替代。替代将在options对象中被传递

• If an array is passed, ? will be replaced in the order that they appear in the array如果传递的是数组，？将会以其出现在数组中的顺序被替代
• If an object is passed, :key will be replaced with the keys from that object. If the object contains keys not found in the query or vice versa, an exception will be thrown.如果传递的是对象，：键将被来自对象的键值所替代。如果对象包含的键在查询中没有找到，将抛出一个异常，反之亦然

sequelize.query('SELECT * FROM projects WHERE status = ?',
  { replacements: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

sequelize.query('SELECT * FROM projects WHERE status = :status ',
  { replacements: { status: 'active' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Array replacements will automatically be handled, the following query searches for projects where the status matches an array of values.

数组替代将会自动处理，即下面对对象中status满足数组值的查询搜索

sequelize.query('SELECT * FROM projects WHERE status IN(:status) ',
  { replacements: { status: ['active', 'inactive'] }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

To use the wildcard operator %, append it to your replacement. The following query matches users with names that start with 'ben'.

为了使用通配符%并添加其道替代中。以下的查询匹配名字是以'ben'开始的用户

sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
  { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

 

 

 

Bind Parameter绑定变量

Bind parameters are like replacements. Except replacements are escaped and inserted into the query by sequelize before the query is sent to the database, while bind parameters are sent to the database outside the SQL query text. A query can have either bind parameters or replacements. Bind parameters are referred to by either $1, $2, ... (numeric) or $key (alpha-numeric). This is independent of the dialect.

绑定变量与替换相似。除了替换是在查询传送给数据库之前通过sequelize进行除去和插入查询的，而绑定变量是在SQL查询文本外传递给数据库的。查询能够有绑定变量或有替换。绑定变量将通过$1, $2, ... (numeric) 或$key (alpha-numeric)提及。与使用的语言不相关

• If an array is passed, $1 is bound to the 1st element in the array (bind[0])如果传递的是数组，$1将会于数组中的第一个元素(bind[0])绑定
• If an object is passed, $key is bound to object['key']. Each key must begin with a non-numeric char. $1 is not a valid key, even if object['1'] exists.如果传递的是对象，$key与object['key']绑定。每个键一定是以非数字字符开始的。$1不是一个有效键，即使object['1']存在
• In either case $$ can be used to escape a literal $ sign.在上面两种情况下，$$都可以用来转义字符$

The array or object must contain all bound values or Sequelize will throw an exception. This applies even to cases in which the database may ignore the bound parameter.

数组或对象必须包含所有边界值，否则Sequelize将抛出异常。这将应用于数据库可能忽略边界变量的情况

The database may add further restrictions to this. Bind parameters cannot be SQL keywords, nor table or column names. They are also ignored in quoted text or data. In PostgreSQL it may also be needed to typecast them, if the type cannot be inferred from the context $1::varchar.

数据库可能添加更多的限制。绑定变量不能作为SQL的关键字，也不能作为表或列名。他们将会在引用文本或数据中被忽略。在PostgreSQL中，如果类型不能从上下文 $1::varchar中被推导出的话，它可能也需要将他们定型。

sequelize.query('SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
  { bind: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

sequelize.query('SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $status',
  { bind: { status: 'active' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})