typescriptsequelize.jssequelize-typescript

Sequelize: missing FROM-clause entry for table


I'm using Sequelize to make a pagination query with QuizModel. My query will include 2 models QuizCategoryModel, QuizInfoModel. My where condition in parent model (QuizModel) includes a reference to attribute question of QuizInfoModel:

this.quizModel.findAll({
  limit,
  offset,
  where: {
    ...(search && {
      [Op.or]: [
        {
          "$quiz_infos.question$": {
            [Op.iLike]: `%${search}%`,
          },
        },
        !Number.isNaN(search) && {
          prefix_id: this.sequelize.where(
            this.sequelize.cast(
              this.sequelize.col("quizs.prefix_id"),
              "varchar"
            ),
            {
              [Op.iLike]: search ? `${Number(search)}%` : "%%",
            }
          ),
        },
      ],
    }),
  },
  include: [
    {
      model: QuizCategoryModel,
      attributes: {
        exclude: ["description"],
      },
    },
    {
      model: QuizInfoModel,
      where: { ...quizInfoFilter },
      required: !!quizInfoFilter,
    },
  ],
  order,
});

Using the above code gives me missing FROM-clause entry for table 'quiz_infos'. After researching, I know I can use subQuery to avoid the error but this will return incorrect number of data, i.e: If I set limit to 10, the return data might be 9 instead.
So is there a way to make my query correct but can still avoid using subQuery all together?


Solution

  • I solved the problem by bringing the query down to the included model instead of using it on the parent where query. So the query will become:

    this.quizModel.findAll({
      limit,
      offset,
      include: [
        {
          model: QuizCategoryModel,
          attributes: {
            exclude: ["description"],
          },
        },
        {
          model: QuizInfoModel,
          where: {
            ...quizInfoFilter,
            ...(search && {
              [Op.or]: [
                {
                  question: {
                    [Op.iLike]: `%${search}%`,
                  },
                },
                !Number.isNaN(search) && {
                  "$quizs.prefix_id": this.sequelize.where(
                    this.sequelize.cast(
                      this.sequelize.col("quizs.prefix_id"),
                      "varchar"
                    ),
                    {
                      [Op.iLike]: search ? `${Number(search)}%` : "%%",
                    }
                  ),
                },
              ],
            }),
          },
          required: !!quizInfoFilter,
        },
      ],
      order,
    });