按子句排序的列不存在

Question

我正在使用Sequelize来查询这样的表：

const students = await User.findAll({
attributes: ['id', [Sequelize.literal(`"firstName" || ' ' || "lastName"`), 'name']],
where: {
[Op.or]: [
{
firstName: {
[Op.iLike]: `%${search}%`
}
},
{
lastName: {
[Op.iLike]: `%${search}%`
}
},
Sequelize.literal(`"firstName" || ' ' || "lastName" ILIKE '%${search}%'`)
]
},
order: Sequelize.literal(`"organization_users"."firstName" ${NATURAL_SORT} ${sort}`),
limit,
offset: limit * (page - 1),
distinct: true
});

它工作正常，但如果我包括一些模型，"firstName"列就不存在了：

const students = await User.findAll({
attributes: ['id', [Sequelize.literal(`"firstName" || ' ' || "lastName"`), 'name']],
where: {
[Op.or]: [
{
firstName: {
[Op.iLike]: `%${search}%`
}
},
{
lastName: {
[Op.iLike]: `%${search}%`
}
},
Sequelize.literal(`"firstName" || ' ' || "lastName" ILIKE '%${search}%'`)
]
},
include: [
{ model: ProgressGrade, as: 'student_progress_grade', required: true, where: { courseId } },
{
model: CourseRole,
as: 'course_roles',
attributes: [],
where: { roleName: 'Student' },
required: true
},
{ model: Course, as: 'courses', attributes: [], where: { id: courseId }, required: true }
],
order: Sequelize.literal(`"organization_users"."firstName" ${NATURAL_SORT} ${sort}`),
limit,
offset: limit * (page - 1),
distinct: true
});

错误消息：

错误：SequelizeDatabaseError:column organization_users.firstName不存在

这是我第一次遇到这个问题，我做了很多搜索，但没有希望。有人知道Sequelize在这里是如何工作的吗？

我使用的是运行在Nodejs 13.8.0、PostgreSQL 10.12 上的Sequelize 5.21.5

Answer 1

请将subQuery属性设置为false，如下所示-

const students = await User.findAll({
attributes: ['id', [Sequelize.literal(`"firstName" || ' ' || "lastName"`), 'name']],
where: {
[Op.or]: [
{
firstName: {
[Op.iLike]: `%${search}%`
}
},
{
lastName: {
[Op.iLike]: `%${search}%`
}
},
Sequelize.literal(`"firstName" || ' ' || "lastName" ILIKE '%${search}%'`)
]
},
include: [
{ model: ProgressGrade, as: 'student_progress_grade', required: true, where: { courseId } },
{
model: CourseRole,
as: 'course_roles',
attributes: [],
where: { roleName: 'Student' },
required: true
},
{ model: Course, as: 'courses', attributes: [], where: { id: courseId }, required: true }
],
order: Sequelize.literal(`"organization_users"."firstName" ${NATURAL_SORT} ${sort}`),
limit,
offset: limit * (page - 1),
distinct: true,
subQuery: false
});

我希望它能有所帮助！