sequelize: findAndCount, error: missing FROM-clause entry for table
When I add limit and offset to this query I get error: missing FROM-clause entry for table “Instruments”. When limit and offset are not in this query, it works perfectly.
let searchParams = {
TeacherId: req.params.teacherId,
$or: [
{ 'title': { $iLike: `%${req.query.text}%` }},
{ '$Instruments.name$': { $iLike: `%${req.query.text}%` }},
{ '$Genres.name$': { $iLike: `%${req.query.text}%` }},
]
};
if (req.query.skillLevel) searchParams.skillLevel = req.query.skillLevel;
const offset = req.query.page > 1 ? (req.query.page - 1) * req.query.perPage : 0;
models.Lesson.findAndCount({
where: { ...searchParams },
include: [{ model: models.Instrument }, { model: models.Genre }],
order: '"createdAt" DESC',
attributes: { exclude: ['video'] },
distinct: true,
limit: req.query.perPage,
offset: offset
})
.then((lessons) => {
let pageTotal = Math.ceil(lessons.count / req.query.perPage);
res.json({
success: true,
lessons: lessons.rows,
pageTotal: pageTotal,
totalLessons: lessons.count
});
})
.catch((error) => {
console.log(error);
return res.status(500).json({ success: false, error: error });
});
I expected it to work the same with or without limit or offset. There is just an error:
message: 'missing FROM-clause entry for table "Instruments"',
parent:
{ error: missing FROM-clause entry for table "Instruments"
at Connection.parseE (/home/michael/Documents/Code/qelodyUniversal/node_modules/pg/lib/connection.js:569:11)
at Connection.parseMessage (/home/michael/Documents/Code/qelodyUniversal/node_modules/pg/lib/connection.js:396:17)
at Socket.<anonymous> (/home/michael/Documents/Code/qelodyUniversal/node_modules/pg/lib/connection.js:132:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:189:7)
at readableAddChunk (_stream_readable.js:176:18)
at TCP.onread (net.js:551:20)
name: 'error',
length: 116,
severity: 'ERROR',
code: '42P01',
detail: undefined,
hint: undefined,
position: '829',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3129',
routine: 'errorMissingRTE',
Dialect: postgres Database version: 9.5 Sequelize version: 3.30.2
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Comments: 18 (3 by maintainers)
This issue will be fixed by appending duplicating:false on include
subQuery: false
on main query will fix this
still a problem here
Not stale. We’re still having to perform a double query and manual count in order to get this working.
Someone found a solution ?
Appending
duplicating: false
breaks offset for me - anyone else seeing this? Limit works fine.The issue occurs only when you append limit.I’am also waiting for the fix on this.