Sequelize-typescript: where on included object

[lverledens]

Hello all,

This is a question, not an issue.
I would like to use a where clause on my included model and I can't find the correct way to realise it.

This is what i'm doing:

StoredObjects.findAndCountAll({
        limit: +(req.query.limit) || 10,
        offset: +(req.query.offset) || 0,
        include: [
            { model: LinkedObject },
        ],
        subQuery: false
    });

There are multiple LinkedObjects possible for every StoredObject.
I would like to add a where clause that makes it possible to filter some of the StoredObjects.

StoredObjects.findAndCountAll({
        limit: +(req.query.limit) || 10,
        offset: +(req.query.offset) || 0,
        include: [
            { model: LinkedObject },
        ],
        subQuery: false,
        **where: { 
               linkedObjectsList is null 
               or 
               linkedObjectsList contains a linkedObject with specificAttribute == 1 }**
    });

I can't put the where in { model: LinkedObject } as this would change the result set...
Any idea how I can do this?

Thanks in advance!

[tzellman]

If I am understanding your question correctly, you need to add the related where clause directly on the object literal in the include array.

Full documentation is here: http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-findAll

[lverledens]

@tzellman, @RobinBuschmann , thanks for reading my question. I realize that it might not have been clear what my problem is exactly.

My issue is the fact that I want to include on null OR when the included object has an attribute with a specific value.

This is the code I am using. I added required: false because i want to join on null and I added the where statement like you described in your answer.

await Job.findAndCountAll({
        limit: +(req.query.limit) || 10,
        offset: +(req.query.offset) || 0,
        order: [['createdAt', 'DESC']], 
        include: [
            { model: UserJob, required: false, where: { status: {$notIn : ['DECLINED']} } }
        ],
        subQuery: false
    });

This code generates the following query. The problem with this query is that when the where clause is not fullfilled, the result will be joined with null, because of the left outer join. Making the result useless, because i want to know which ones were really null in the database.

SELECT [Job].*, [applications].*
FROM [Job] AS [Job] 
    LEFT OUTER JOIN [UserJob] AS [applications] ON [Job].[id] = [applications].[jobId] 
        AND [applications].[status] NOT IN (N'DECLINED')  
ORDER BY [Job].[createdAt] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

The sql query i would like to create disconnects the where clause from the join, like this:

SELECT [Job].*, [applications].*
FROM [Job] AS [Job] 
    LEFT OUTER JOIN [UserJob] AS [applications] ON [Job].[id] = [applications].[jobId] 
where applications.status is null or  applications.status not in (N'DECLINED')
ORDER BY [Job].[createdAt] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

But I can't seem to figure out how to write this using this library. Any ideas?

Thanks in advance!
Lothar

[RobinBuschmann]

@lverledens Ah ok, I get it know: This is not documented very well, but you can achieve it roughly like so:

      include: [{ model: LinkedObject, required: false}],
      where: {
        [Sequelize.Op.or]: [
          {['$yourLinkedObjectAlias.status$' as any]: null},
          {['$yourLinkedObjectAlias.status$' as any]: {[Sequelize.Op.notIn]: ['a', 'b', 'c']}},
        ],
      }

Please note that yourLinkedObjectAlias is the name of the property you annotated in StoredObjects. For example:

class StoredObjects extends Model<StoredObjects> {
  ...
  @HasMany(() => LinkedObject)
  yourLinkedObjectAlias: LinkedObject;
}

Hope this helps!

[lverledens]

Thanks @RobinBuschmann
I had read about the $xxx$ options in sequelize documentation but I couldn't find out how to use it with sequelize-typescript.

Issue solved :)