sequelize adding field in where clause in top level

Question

I'm having a query like this:

const { count, rows: questions } = await QBQuestion.findAndCountAll({
  where: {
    [Sequelize.Op.or]: [
      { 'QBQuestionSkills.Skill.Skill': { [Sequelize.Op.substring]: 'python' } }, { Text: { [Sequelize.Op.substring]: keyWord } }
    ]
  },
  limit,
  offset,
  include: [
     {},...{}, //Few Joins redacted here
    {
      model: sequelize.models.QBQuestionSkill,
      required: false,
      as: 'QBQuestionSkills',
      include: [
        {
          model: sequelize.models.Skill,
          as: 'Skill',
        },
      ],
    },
  ],
  attributes:
    ['Id', 'Text', 'DomainId', 'SubdomainId', 'TotalAnswerTime', 'McqAnswerId', 'FileId', 'Difficulty', 'SchemaId'],
  order: [['Id', 'DESC']],
});

This above forms query of this type (Count part):

SELECT
    COUNT(`QBQuestion`.`Id`) AS `count`
FROM
    `QBQuestion` AS `QBQuestion`
LEFT OUTER JOIN `QuestionType` AS `QuestionType`
ON
    `QBQuestion`.`QuestionTypeId` = `QuestionType`.`QuestionTypeId`
INNER JOIN `AnswerType` AS `AnswerType`
ON
    `QBQuestion`.`AnswerTypeId` = `AnswerType`.`AnswerTypeId`
LEFT OUTER JOIN `FileStore` AS `File`
ON
    `QBQuestion`.`FileId` = `File`.`ID`
LEFT OUTER JOIN `QBQuestionSkill` AS `QBQuestionSkills`
ON
    `QBQuestion`.`Id` = `QBQuestionSkills`.`QBQuestionId`
LEFT OUTER JOIN `Skill` AS `QBQuestionSkills->Skill`
ON
    `QBQuestionSkills`.`SkillId` = `QBQuestionSkills->Skill`.`SkillId`
WHERE
    (
        `QBQuestion`.`QBQuestionSkills.Skill.Skill` LIKE '%python%' OR `QBQuestion`.`Text` LIKE '%python%'
    );

In the query, if you see, in Where clause you can notice QBQuestion.QBQuestionSkills.Skill.Skill Which is wrong.

I have tried to put like this (Notice the $): { '$QBQuestionSkills.Skill.Skill$': { [Sequelize.Op.substring]: 'python' } }

in this way it forms something like this:

SELECT
    `QBQuestion`.*,
    [..REDACTED..]
    `QBQuestionSkills->Skill`.`Category` AS `QBQuestionSkills.Skill.Category`,
    `QBQuestionSkills->Skill`.`Skill` AS `QBQuestionSkills.Skill.Skill`
FROM
    (
    SELECT
        `QBQuestion`.`Id`,
        `QBQuestion`.`Text`,
        `QBQuestion`.`DomainId`,
       [..REDACTED..]
        `AnswerType`.`AnswerTypeId` AS `AnswerType.AnswerTypeId`,
        `AnswerType`.`Name` AS `AnswerType.Name`
    FROM
        `QBQuestion` AS `QBQuestion`
    INNER JOIN `AnswerType` AS `AnswerType`
    ON
        `QBQuestion`.`AnswerTypeId` = `AnswerType`.`AnswerTypeId`
    WHERE
        (
            `QBQuestionSkills->Skill`.`Skill` LIKE '%python%' OR `QBQuestion`.`Text` LIKE '%python%'
        )
    ORDER BY
        `QBQuestion`.`Id`
    DESC
LIMIT 0,
100
) AS `QBQuestion`
LEFT OUTER JOIN `QuestionType` AS `QuestionType`
ON
    `QBQuestion`.`QuestionTypeId` = `QuestionType`.`QuestionTypeId`
LEFT OUTER JOIN `FileStore` AS `File`
ON
    `QBQuestion`.`FileId` = `File`.`ID`
LEFT OUTER JOIN `QBQuestionSkill` AS `QBQuestionSkills`
ON
    `QBQuestion`.`Id` = `QBQuestionSkills`.`QBQuestionId`
LEFT OUTER JOIN `Skill` AS `QBQuestionSkills->Skill`
ON
    `QBQuestionSkills`.`SkillId` = `QBQuestionSkills->Skill`.`SkillId`
ORDER BY
    `QBQuestion`.`Id`
DESC
    ;

This gives me some error like this: sqlMessage: "Unknown column 'QBQuestionSkills->Skill.Skill' in 'where clause'",

After all this, I see the first query is fairly simple, but I just want to filter with Skill instead of QBQuestionSkills.Skill.Skill. Now, when I replace the same with Skill, it becomes something like this: QBQuestion.Skill which is not present. instead Skill is present.

Does anyone have any idea on how to handle this?

basically what I feel is, when you querying a model with where clause, in SQL, it actually keeps the where like this: Model.Field, what I'm assuming will work for me is Field without the accompany of the Model.

Note: I could put where clause inside includes. but it should be with Or with other fields in the top level.

also, if there are some other recommendations or suggestions I'm all ears!

sequelize adding field in where clause in top level

0 Answers