Sequelize: Show only entries, that do not exist is some other table

During my job, I came across a problem, where I wanted to show the results that did not have an entry in some other table. I spend quite some time on it, so I am documenting it in my blog, so I will have a record of it, if I ever need it in the future. If it helps somebody else, that is even better.

Since the problem appeared during my job, I am going to be using a different example to help me demonstrate this.

For this example, let us go into the fantasy isekai (adventure in another world) setting. The gods of the multiverse have decided that they want to crack down on the gods and goddesses that help the citizens in their own territory by giving them magical powers. Because of the lobbying of certain individuals, the interference to the lives of the people transported to the different world are exempt from this, because otherwise we will not have as many good stories as we have right now. They decided to give this job to one of the newer gods, let's call him... Light. Not that we really need a name here. Now, Light does not have direct access to the celestial database - so no changing the structure. But they do allow him to query it, as long as he goes through ORM they provided.

The tables in the database in question are like this (tables are lowercase, the models are uppercase):

• intervention - includes the id of a person (person_id) and the type of the intervention (intervention_type).
• person - includes the data about the person (id)
• otherworlders - includes the ids of the people, that were transported from one world to another (otherworlder_id)

Now, somebody else had already created a table with interventions, which also showed the people's information, filtered by the type of intervention. So the job was to filter out the ones that were summed from another world.

Eventually the job was done. The code looked something like this:

models.Interventions
             .findAll({
                 where: {
                     intervention_type: 'power upgrade',
                 },
                 include: [{
                     model: models.Person,
                 }],
             })

Now, in order to do the filtering, the relevant associations have to be created. Normal hasOne and belongs without any options worked for may case. So Person.hasOne(models.Otherworlders) and Otherworlders.belongsTo(models.Person). Now, first the person part of the code gets another include, where we also include the data from the Otherworlders table. Since not every Person has an Otherworlders entry, we therefore want a left join, so we need to add the required: false option to the model. We also want to remove the PersonId from the attributes, since this would be the field that the Sequelize would want to add as a key by default.

Now we are already adding the data that we need in the response. But since the gods and goddesses do not want to contribute to environmental change, they want to make sure that the data, that they do not need, never gets sent.

In order to do this, it is possible to use the references to the include objects in the main where. They are strings that start and end with the $ sign. In between these signs is the same string that would be used to reference this element in JavaScript, if having access to the JSON. It is also possible to see this string, by adding the raw: true to the call.

So this would be the final code:

models.Interventions
             .findAll({
                 where: {
                     intervention_type: 'power upgrade',
                     // required to filter out the transported people
                     '$person.otherworlders.otherworlder_id$': null,
                 },
                 include: [{
                     model: models.Person,
                     // adding the data about transported people in the response
                     include: [{
                         model: models.Otherworlders,
                        // indicating that there can be people without
being transported to another world
                         required: false,
                        // removing the field, that Sequelize adds by default
                         attributes: {
                             exclude: ["PersonId"]
                         },
                     }]
                 }],
             })