This is the ninth day of my participation in the First Challenge 2022

preloaded

Preloading is the act of querying data from multiple models at once, a query with one or more joins

const User = sequelize.define('user', { name: DataTypes.STRING }, { timestamps: false });
const Task = sequelize.define('task', { name: DataTypes.STRING }, { timestamps: false });
const Tool = sequelize.define('tool', {
  name: DataTypes.STRING,
  size: DataTypes.STRING
}, { timestamps: false });
User.hasMany(Task);
Task.belongsTo(User);
User.hasMany(Tool, { as: 'Instruments' });
Copy the code

Gets a single associated element

The associated user loads all tasks

const tasks = await Task.findAll({ include: User });
console.log(JSON.stringify(tasks, null.2));
Copy the code

Print result:

[{
  "name": "A Task"."id": 1."userId": 1."user": {
    "name": "Zhang"."id": 1}}]Copy the code

Conclusion:

The associated model is added to a new field named User. Sequelize automatically selects the name of this field based on the name of the associated model, using the plural form of the field if applicable (that is, the association is hasMany or belongsToMany)

Gets all associated elements

const users = await User.findAll({ include: Task }); \console.log(JSON.stringify(users, null.2));
Copy the code

Print result:

[{
  "name": "Zhang"."id": 1."tasks": [{
    "name": "A Task"."id": 1."userId": 1}}]]Copy the code

The association is one-to-many, so the accessors (the Tasks attribute in the result instance) are complex

Get alias association

If the association is aliased (using the AS parameter), this alias must be specified when the model is included, but you can provide two options for the object: Model and AS

const users = await User.findAll({
  include: { model: Tool, as: 'Instruments'}});console.log(JSON.stringify(users, null.2));
Copy the code

Print result:

[{
  "name": "Zhang"."id": 1."Instruments": [{
    "name": "hammer"."id": 1."userId": 1}}]]Copy the code

You can also specify the associated alias to match the string

User.findAll({ include: 'Instruments' }); // Can also be used normally
User.findAll({ include: { association: 'Instruments'}});// Can also be used normally
Copy the code

Need to preload

When preloaded, you can force the query to return only records with associated models, shifting the query from the default OUTER JOIN to INNER JOIN with the required: true parameter

User.findAll({
  include: {
    model: Task,
    required: true}});Copy the code

Add filtering to preloading at the model level

When preloaded, you can use the WHERE parameter to filter the associated model

User.findAll({
  include: {
    model: Tool,
    as: 'Instruments'
    where: {
      size: {
        [Op.ne]: 'small'}}}});Copy the code

Apply a WHERE clause to the model to reference values in the relational model, using the sequelize.col function

// Find all projects with at least one task, where task.state === project.state
Project.findAll({
  include: {
    model: Task,
    where: {
      state: Sequelize.col('project.state')}}})Copy the code

Add complex WHERE clauses

The top-level WHERE clause for nested columns, which Sequelize references to the nested column method: ‘$nested.column$’, can be used to move the WHERE condition from the ON condition of the contained model to the top-level WHERE clause

User.findAll({
  where: {
    '$Instruments.size$': { [Op.ne]: 'small'}},include: [{
    model: Tool,
    as: 'Instruments'}}]);Copy the code

Use includefindAndCountAll

FindAndCountAll utility supports include. Only include items marked required are treated as count

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

Because the Profile’s include has been set to required, it causes an internal join and only counts users who have profiles. If required is removed from the include, users who include and do not include profiles will be counted. Adding a WHERE clause to include automatically makes it required

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

The above query only counts users with valid profiles, because required is implicitly set to true when the WHERE clause is added to the include