How to use sequelize.js select only those records in the database, the nested element of which is an array and it must be of a certain length?

user_19190216 0
Viewed 72

maybe someone who has encountered / knows. I need the students array to be in a certain specified range of numbers (from 1 to 3, for example) inclusive. The students field in the response is obtained through join, I'm trying to do this:

const lessonsAll = await lessons.findAll({
    include: [{
      model: teachers,
      where: {
        id: { [Op.in]: teacherIds.split(',') },
      },
    },
    {
      model: students,
      as: 'students',
    },
    ],
    where:

    [Sequelize.where(sequelize.fn('array_length', Sequelize.literal("lessons->'students'"), 1), { [Op.between]: [1, 3] }),
      {
        status: status || { [Op.ne]: null },
        date: {
          [Op.between]: [new Date(date.split(',')[0]), date.split(',')[1] ? new Date(date.split(',')[1]) : new Date(date.split(',')[0])],
        },
      },
    ],
    limit,
    offset: page ? limit * (page - 1) : 0,
    order: [['id', 'ASC']],

  });

but I get this error:

\node_modules\@sequelize\core\lib\sql-string.js:58
    throw new Error(`Invalid value ${logger.inspect(val)}`);
          ^

Error: Invalid value Where {
  attribute: Fn { fn: 'array_length', args: [Array] },
  comparator: '=',
  logic: { [Symbol(between)]: [Array] }
}
    at Object.escape (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\sql-string.js:58:11)
    at PostgresQueryGenerator.escape (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:715:22)        
    at PostgresQueryGenerator.whereItemQuery (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1717:102)
    at PostgresQueryGenerator.whereItemsQuery (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1633:23)
    at C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1736:30
    at Array.map (<anonymous>)
    at PostgresQueryGenerator._whereGroupBind (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1735:21)
    at PostgresQueryGenerator.whereItemQuery (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1686:19)
    at PostgresQueryGenerator.whereItemsQuery (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1630:25)
    at PostgresQueryGenerator.getWhereConditions (C:\code\отчет по занятиям\lessons\node_modules\@sequelize\core\lib\dialects\abstract\query-generator.js:1973:19)
sequelize.js node.js postgresql
1 Answers

if the column you want to check is coming from a model in your include, then you need to add the condition in the main where like this

const result = await parentTable.findAll({
  where : sequelize.where(sequelize.fn('array_length', 
            sequelize.literal(`"Childs".'arrayColumn'`)), {
              [Op.between]: [1, 3], // your range
            },
          )
  });

to give you this

    SELECT
    "parentTable"."id"
FROM
    "Parents"
    JOIN "Childs" ON "Childs".id = "Parents"."ChildId"
WHERE
    array_length("Childs".'arrayColumn') BETWEEN 1 AND 3;

If you try this it should work !

user_150852740
Related