This is the 22nd day of my participation in the More text Challenge. For details, see more text Challenge
The query
The primary key query
Model.findByPk(1)
Copy the codeSearch does not exist to create
The findOrCreate method can be used to check whether an element already exists in the database. If this is the case, the method will generate the appropriate instance. If the element does not exist, it will be created.
If this is the case, the method will result in the appropriate instance. If the element does not exist, it will be created.
Suppose we have an empty database and a User model with a username and a job.
User
.findOrCreate({
where: {
username: 'sdepold'
},
defaults: {
job: 'Technical Lead JavaScript'
}
})
. then(([user, created]) = > {
console.log(user.get({
plain: true
}))
console.log(created)
/* findOrCreate returns an array of objects that have been found or created, with a Boolean value, true if a new object is created, false otherwise, like this: [{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) }, In the example above, the array on the third row will be divided into two parts and passed as arguments to the callback function, in this case treating them as "user" and "created".(So "user" will be the object that returns the array with index 0, and "created" will be equal to. "true".) * /
})
Copy the codeThe code creates a new instance. So when we’ve got an example…
User.create({ username: 'fnord'.job: 'omnomnom' })
.then(() = > User.findOrCreate({
where: {
username: 'fnord'
},
defaults: {
job: 'something else'
}
}))
.then(([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 23:28: 34 GMT + 0100(CET), updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET) }, False] The array returned by findOrCreate is extended into two parts by the array on the third row, and these parts are passed to the callback function as two arguments, in this case as "user" and "created".(So "user" will be the object that returns the array with index 0, and "Created" will equal "false".) * /
})
Copy the code. Existing entries will not change. You see the “job” for the second user, and in fact the creation operation is bogus.
Look and count
FindAndCountAll – Searches the database for multiple elements, returning data and totals
This is a handy method that combines findAll and count(see below), which is useful when dealing with paging related queries where you want to retrieve data with limit and offset, but also need to know the total number of records that match the query:
A successful handler will always receive an object with two properties:
count– An integer, the total number of records matching the WHERE statement and other associated filtersrows– An array object that matches the WHERE statement and associated filters 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);
});
Copy the codeIt supports include. only include marked as required will be added to the count:
Suppose you want to find all users with personal information:
User.findAndCountAll({
include: [{model: Profile, required: true}].limit: 3
});
Copy the codeBecause the Profile’s include is required, this will result in internal connections and only users with profiles will be counted. If we remove Required from the include, users with and without profiles will be counted. Adding a WHERE statement to include automatically makes it required:
User.findAndCountAll({
include: [{model: Profile, where: { active: true}}].limit: 3
});
Copy the codeThe query above counts only users with active Profiles because required is implicitly set to true when the WHERE statement is added to include.
The options object passed to findAndCountAll is the same as for findAll (described below).
Query multiple (common)
// Find multiple entries
Project.findAll().then(projects= > {
// Projects will be an array of all Project instances
})
// Search for specific attributes - using a hash
Project.findAll({ where: { name: 'A Project' } }).then(projects= > {
// Projects will be an array of Project instances with the specified name
})
// Search within a specific area
Project.findAll({ where: { id: [1.2.3] } }).then(projects= > {
// projects will be a series of projects with id 1,2 or 3
// This is actually doing an IN query
})
Project.findAll({
where: {
id: {
[Op.and]: {a: 5}, (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'./ / contains' % hat '
[Op.notLike]: '%hat'.// do not include '%hat'
[Op.iLike]: '%hat'.// Contains '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'.// Does not include '%hat' (PG only)
[Op.overlap]: [1.2].// && [1, 2] (PG array overlap operator)
[Op.contains]: [1.2].// @> [1, 2] (PG array contains operators)
[Op.contained]: [1.2].// <@ [1, 2] (PG array contained in operator)
[Op.any]: [2.3].// Any array [2, 3]::INTEGER (PG only)
},
status: {
[Op.not]: false.// Status is not FALSE}}})Copy the codeCompound filter/OR/NOT query
You can make a compound WHERE query using multiple levels of nested AND,OR, AND NOT conditions. To do this, you can use the OR, and, or not operators:
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}}})Copy the codeThese two 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;
Copy the codeNot the sample:
Project.findOne({
where: {
name: 'a project',
[Op.not]: [
{ id: [1.2.3] {},array: { [Op.contains]: [3.4.5}}]}});Copy the codeWill be generated:
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;
Copy the codeManipulate data sets with constraints, offsets, sequences, and groupings
To get more relevant data, you can use limits, offsets, orders, and groups:
// Limit the results of the query
Project.findAll({ limit: 10 })
// Skip the first 10 elements
Project.findAll({ offset: 10 })
// Skip the first 10 elements and get 2
Project.findAll({ offset: 10.limit: 2 })
Copy the codeThe syntax for grouping and sorting is the same, so here’s a separate example to explain grouping, while the rest is sorting. Everything you see below can also be grouped
Project.findAll({order: [['title'.'DESC']]})
// Generate ORDER BY title DESC
Project.findAll({group: 'name'})
// Generate GROUP BY name
Copy the codeNote that in both examples above, the supplied strings are inserted verbatim into the query, so the column names are not escaped. This is always the case when you supply a string to an Order/group. If you want to escape column names, you should provide an array of arguments, even if you only want to do order/group through a single column
something.findOne({
order: [
// returns' name '
['name'].// Return 'username' DESC
['username'.'DESC'].// return Max (' age ')
sequelize.fn('max', sequelize.col('age')),
// Returns Max (' age ') DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'].// Returns otherFunction (' col1 ', 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12.'lalala'), 'DESC'].// return otherFunction (awesomeFunction (' col ')) DESC. This nesting is infinite!
[sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']]})Copy the codeTo review, the elements of an order/group array can be the following:
- String – will be referenced
- Array – The first element will be referenced and the second element will be appended verbatim
- Object –
- A verbatim reference will be added to RAW
- If raw is not set, everything is ignored and the query will fail
- Sequelize.fn and sequelize.col return the name of the function and referenced column
Field filtering
To select only certain properties, use the Attributes option. Usually we pass an array:
Model.findAll({
attributes: ['foo'.'bar']});Copy the codeSELECT foo, bar …
Properties can be renamed using nested arrays:
Model.findAll({
attributes: ['foo'['bar'.'baz']]});Copy the codeSELECT foo, bar AS baz …
You can also use sequelize.fn to aggregate:
Model.findAll({
attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]});Copy the codeSELECT COUNT(hats) AS no_hats …
When you use the aggregation capability, you must give it an alias so that it can be accessed from within the model. In the example above, you can use instance.get(‘no_hats’) to get the number of hats.
Sometimes, listing all the attributes of a model can be tiresome if you just want to add an aggregation:
// This is a tiresome way of getting the number of hats...
Model.findAll({
attributes: ['id'.'foo'.'bar'.'baz'.'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]});// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]}}); SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...Copy the codeAlso, it can exclude some specified table fields:
Model.findAll({
attributes: { exclude: ['baz']}}); SELECT id, foo, bar, quz ...Copy the code