I have these 2 models:

1. Orders Models
2. Solutions model

Orders Model

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class Orders extends Model {
        /**
         * Helper method for defining associations.
         * This method is not a part of Sequelize lifecycle.
         * The `models/index` file will call this method automatically.
         */
        static associate(models) {
            // define association here
            Orders.hasMany(models.Payments, {
                foreignKey: {
                    name: 'order',
                    allowNull: false,
                },
                constraints: false,
                onDelete: 'cascade',
            });

            Orders.hasOne(models.Solutions, {
                foreignKey: {
                    name: 'order',
                    allowNull: false,
                },
                constraints: false,
                onDelete: 'cascade',
                as: "solution"

            });
        }
    }
    Orders.init(
        {
            order_no: {
                defaultValue: DataTypes.UUIDV4,
                type: DataTypes.UUID,
                primaryKey: true,
                allowNull: false,
                unique: true,
            },
            order_date: {
                type: DataTypes.DATE,
                defaultValue: DataTypes.NOW,
            },
            
            title: {
                type: DataTypes.STRING,
                allowNull: false,
            },
        },
        {
            sequelize,
            modelName: 'Orders',
            tableName: 'Orders',
        }
    );
    return Orders;
};

#2. Solutions table

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class Solutions extends Model {
        /**
         * Helper method for defining associations.
         * This method is not a part of Sequelize lifecycle.
         * The `models/index` file will call this method automatically.
         */
        static associate(models) {
            // define association here

            Solutions.belongsTo(models.Orders, {
                foreignKey: 'order',
                onDelete: 'cascade',
                constraints: false,
                as: "solution"
            });
        }
    }
    Solutions.init(
        {
            solutionId: {
                defaultValue: DataTypes.UUIDV4,
                type: DataTypes.UUID,
                primaryKey: true,
                allowNull: false,
                unique: true,
            },

            content: {
                type: DataTypes.TEXT,
                allowNull: false,
            },
            additional_instruction: {
                type: DataTypes.TEXT,
                allowNull: true,
            },
            date_submited: {
                type: DataTypes.DATE,
                defaultValue: DataTypes.NOW,
            },
        },
        {
            sequelize,
            modelName: 'Solutions',
        }
    );
    return Solutions;
};

I am trying to get all orders where it's solution has not been submitted to the solutions table, i.e order field(Foreign key in solution table) is null.

I have tried this

Orders.findAndCountAll({
        include: [
            {
                model: Users,
                attributes: ['username', 'email', 'uid'],
            },
            {
                model: Solutions,
                as: "solution",
                where: {
                    solutionId: {
                        [Op.notIn]: Solutions.findAll({
                            attributes: ['solutionId']
                        })
                    }
                }
            }
        ],
        offset: page,
        limit,
    })

I was expecting to get a list of all orders where the solutions in the solution table has not been added. Am a bit new to sequelize.

CodePudding user response：

You can perform a left join with a filter which excludes records from Solutions table if the order does not exit.

Orders.findAndCountAll({
        include: [
            {
                model: Users,
                attributes: ['username', 'email', 'uid'],
            },
            {
                model: Solutions,
                as: "solution",
                required: false,
                where: {
                  order: null
                }
            },
        ],
        offset: page,
        limit,
    })

CodePudding user response：

You can try to filter after left join, Sequelize can apply where clause directly on the join or after join.

Orders.findAndCountAll({
  where: {
    '$orders.solution$': null,
  },
  include: [
    {
      model: Solutions,
      as: "solution",
      required: false
    },
  ],
})

In SQL it's like :

SELECT COUNT(*) 
FROM orders o 
LEFT JOIN solutions s ON o.id = s.order AND s.order IS NULL

VS

SELECT COUNT(*) 
FROM orders o 
LEFT JOIN solutions s ON o.id = s.order 
WHERE s IS NULL