How to retrieved parent object which haven’t children with specific values? Sequelize

Description & question

Rentals is table with all registered rentals with start and end date as properites. After querying i want to get only this productGroups which have some products that don’t have rentals in conflitct with given time period (‘filtered’ obj).

I think i only need to add (somehow) condition of having empty ‘rentals’ property. And same with products property.

Anyone know how to get this ?

Info

ProductGroup has many Products

Products has many Rentals

filtered.startDate, filtered.endDate are the dates of begin and end of new rental

ProductGroup.findAll({
        include: [
            {
                model: Product,
                include: [
                    {
                        model: Rentals,
                        where: {
                            [Op.or]: [
                                {
                                    startDate: {
                                        [Op.between]: [filtered.startDate, filtered.endDate]
                                    }
                                },
                                {
                                    endDate: {
                                        [Op.between]: [filtered.startDate, filtered.endDate]
                                    }
                                }]
                        },
                        required: false
                    }
                ],
                required: true
            }
        ]
    })

Update:

Models:

module.exports = function(sequelize, DataTypes) {
    let productGroups = sequelize.define('productGroups', {
        id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
            field: 'ID'
        },
        name: {
            type: DataTypes.STRING(255),
            allowNull: false,
            unique: true,
            field: 'name'
        }
    }, {
        timestamps: false,
        tableName: 'product_groups'
    });

    productGroups.associate = ( models ) =>{
        productGroups.hasMany( models.product, { foreignKey: 'productGroupId', sourceKey: 'id' } );
    };

    return productGroups;
};

module.exports = function(sequelize, DataTypes) {
    let product = sequelize.define('product', {
        id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
            field: 'ID'
        },
        name: {
            type: DataTypes.STRING(255),
            allowNull: false,
            field: 'name'
        },
        productGroupId: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            references: {
                model: 'product_groups',
                key: 'ID'
            },
            field: 'product_group'
        }
    }, {
        timestamps: false,
        tableName: 'product'
    });

    product.associate = ( models ) =>{
        product.belongsTo( models.productGroups, { foreignKey: 'productGroupId', targetKey: 'id' } );

        product.hasMany( models.rentals, { foreignKey: 'productId', sourceKey: 'id' } );
    };

    return product;
};

module.exports = function(sequelize, DataTypes) {
    let rentals = sequelize.define('rentals', {
        id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
            field: 'ID'
        },
        startDate: {
            type: DataTypes.DATEONLY,
            allowNull: false,
            field: 'start_date'
        },
        endDate: {
            type: DataTypes.DATEONLY,
            allowNull: false,
            field: 'end_date'
        },
        productId: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            references: {
                model: 'product',
                key: 'ID'
            },
            field: 'product_id'
        }
    }, {
        timestamps: false,
        tableName: 'rentals'
    });

    rentals.associate = ( models ) =>{
        rentals.belongsTo( models.product, { foreignKey: 'productId', targetKey: 'id' } );
    };

    return rentals;
};

Sample Filtered params:

filtered = {
    startDate: 2019-05-20,
    endDate: 2019-05-29,
} //dates are date objects

Sample tables:

product_groups = [
    {ID: 1, name: 'test1'},
    {ID: 2, name: 'test2'}
]

product = [
    {ID: 1, name: 'prod1', product_group: 1},
    {ID: 2, name: 'prod2', product_group: 1},
    {ID: 3, name: 'prod3', product_group: 2},
    {ID: 4, name: 'prod4', product_group: 1},
]

rentals = [
    {ID: 1, start_date: 2017-10-01, end_date: 2017-10-25, product_id: 1},
    {ID: 2, start_date: 2019-05-27, end_date: 2019-05-31, product_id: 1},
    {ID: 3, start_date: 2018-10-12, end_date: 2018-10-14, product_id: 2},
    {ID: 4, start_date: 2019-05-10, end_date: 2019-06-31, product_id: 3}
] //dates are date objects

As result i expect only this productGroups which have some avaiable products, avaiebility of product depends on rentals (if product have rentals that are in collision with provided ‘filtered’ obj don’t return them)

In this example i expect only ProductGroup with ID=1 with Products with IDs=[2, 4]

Answer

Try adding a where clause to your Product level:

        where: { 
            '$rental_table_name.primary_key$' : { [Op.eq]: null }
        },

(replacing table name and PK as appropriate).

Updated solution:

Try the following, at the Product Group section:

   where: { 
       '$products.rentals.ID$' : { [Op.eq]: null } 
   },