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?
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,
});