Can't use array of sequelize.where() on having or where properties of the options object

[lucastaliberti]

What are you doing?

I'm trying to build a query with multiple having filters, like the code below

Patient.findAndCountAll({
    attributes: [
      [
        sequelize.fn('sum', sequelize.col('deliveredProcedures.totalAmount')),
        'totalAmount',
      ],
      [
        sequelize.fn('count', sequelize.col('deliveredProcedures.id')),
        'proceduresCount',
      ],
    ],
    include: [
      {
        model: DeliveredProcedure,
        as: 'deliveredProcedures',
        where: {
          entryDate: {
            gt: moment()
              .subtract(1, 'years')
              .toISOString(),
            lt: new Date(),
          },
        },
        attributes: [],
        duplicating: false,
        required: true,
      },
    ],
    group: ['Patient.id'],
    having: [
      sequelize.where(sequelize.fn('sum', sequelize.col('totalAmount')), {
        gte: minValue,
        lte: maxValue,
      }),
      sequelize.where(sequelize.fn('count', sequelize.col('deliveredProcedures.id')), {
        gte: minValue,
        lte: maxValue,
      })],
  });

This code will throw
[ERROR] : Support for literal replacements in the where object has been removed.

but if I add an empty object to the having property it works perfectly, which seems weird to me.

  having: [
	  {},
      sequelize.where(sequelize.fn('sum', sequelize.col('totalAmount')), {
        gte: minValue,
        lte: maxValue,
      }),
      sequelize.where(sequelize.fn('count', sequelize.col('deliveredProcedures.id')), {
        gte: minValue,
        lte: maxValue,
      })],

This is probably caused by the fact the Utils.canTreatArrayAsAnd(value) returns false to arrays that don't contain any plain object, which I'm afraid is not correct since the functionality works if I "cheat" the canTreatArrayAsAnd check.

What do you expect to happen?

Query works and having clause is properly built when passing an array of sequelize.where objects to the having or where properties of the options object.

What is actually happening?

get [ERROR] : Support for literal replacements in the where object has been removed.

Dialect: postgres / any(?)
Database version: postgres 9.6
__Sequelize version: 4.4
__Tested with latest release: No

[maiquelleonel]

I was going to report the same problem but your trick on clause having saved my day 😄

Thanks a lot!

[RafeOO]

Your trick on clause having saved my day.

Thanks

[MTodorov94]

Save my day too :D

[papb]

You have posted a code snippet that shows the problem but is not entirely self-contained (i.e. I can't just copy-paste it and run it). Can you (or someone else) please provide a SSCCE (also known as MCVE/reprex)? This will help tackling the issue faster.

[lucastaliberti]

Hey @papb sorry about that. Do you have any recomendation on how to that properly ? I can only think of mocking the database, if that is what you have in mind. I can do it.

[papb]

@lucastaliberti I just want a code that I can copy-paste and run and see the problem. If I try to run the code in your first post, I will obviously get a Patient is not defined error in the first line. So just provide a model definition, and create() some instances if necessary.

In short: just put yourself in my shoes trying to reproduce your problem with the least effort as possible 😬 imagine what I will do: create an empty folder, install sequelize, and run your code...

Shouldn't take too long 👍

[lucastaliberti]

Hey, @papb sorry for the delay.
Here is an MCVE in codesandbox. Please let me know if you need anything else.

https://codesandbox.io/s/sequelizeissue10142-vqozc

[murongg]

Has this issue been resolved?

[lucastaliberti]

@papb did you have the chance to check my example? I believe we could remove the awaiting response tag by now.