Sequelize: find entries with no association

Question

Given the following simple models:

class A extends Model {}
A.init({
    aField: DataTypes.STRING,
}, { sequelize });

class B extends Model {}
B.init({
    bField: DataTypes.STRING,
}, { sequelize });

A.hasMany(B); // this creates the B.AId column


// populate

await A.create({
    aField: 'fooA',
    Bs: [{ bField: 'fooB' }]
}, { include: [B] });

await A.create({ aField: 'barA' });
await A.create({ aField: 'bazA' });

I cannot figure out how to select the first A model instance that has no B entries.
The the previous case, return just barA and bazA.

The following does not work, it returns foo:

await A.findOne({
    include: [{
        model: B,
        required: false,
        where: {
            'AId': null,
        }
    }],
});

Generated query is: SELECT A.*, Bs.id AS Bs.id, Bs.bField AS Bs.bField, Bs.AId AS Bs.AId FROM (SELECT A.id, A.aField FROM As AS A LIMIT 1) AS A LEFT OUTER JOIN Bs AS Bs ON A.id = Bs.AId AND Bs.AId IS NULL

Answer 1

This is an issue with limit and association. Similar issue: https://github.com/sequelize/sequelize/issues/7585

Sequelize turns association to sub query but in your use case, you need an association then limit on overall the association result. For that, you can use subQuery: false to disable the sub query.

await A.findOne({
    include: [{
        model: B,
        required: false,
        where: {
            'AId': null,
        }
    }],
    subQuery: false
});

This still fails to achieve what you want, since where in include will generate AND condition for ON.

Generated Query:

SELECT `A`.`id`, `A`.`aField`, `A`.`createdAt`, `A`.`updatedAt`, `Bs`.`id` AS `Bs.id`, 
    `Bs`.`bField` AS `Bs.bField`, `Bs`.`createdAt` AS `Bs.createdAt`, 
    `Bs`.`updatedAt` AS `Bs.updatedAt`, `Bs`.`AId` AS `Bs.AId` 
FROM `As` AS `A` 
LEFT OUTER JOIN `Bs` AS `Bs` ON `A`.`id` = `Bs`.`AId` AND `Bs`.`AId` IS NULL 
LIMIT 1;

What you want is

...
... ON `A`.`id` = `Bs`.`AId` WHERE `Bs`.`AId` IS NULL 
LIMIT 1;

To get this WHERE, you can put where clause for A.

await A.findOne({
    include: [{
        model: B,
        required: false
    }],
    where: {
        '$Bs.AId$': null
    },
    subQuery: false
});