findAndCount, error: missing FROM-clause entry for table
See original GitHub issueWhen 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
Issue Analytics
- State:
- Created 7 years ago
- Comments:16 (2 by maintainers)
Top Results From Across the Web
TypeORM: [364] ERROR: missing FROM-clause entry for table ...
I have 3 tables set up on my database: user , organisation , user_organisation . The idea for this is that a user...
Read more >Fix “ERROR: missing FROM-clause entry for table” in ...
If you're getting “ERROR: missing FROM-clause entry for table” in PostgreSQL when using an operator such as UNION , INTERSECT , or EXCEPT ......
Read more >Complex query crashes with "missing FROM-clause entry for ...
Hello. I guess the problem is in Query.trim_start function. if a table is a reference between two related tables and its columns are...
Read more >Missing FROM-clause entry for table in postgres CTE
You are facing with SQL Error [42P01]: ERROR: missing FROM-clause entry for table "a_cte" error because you never select from a_cte .
Read more >A brand new website interface for an even better experience!
findAndCount, error: missing FROM-clause entry for table.
Read more >
Top Related Medium Post
No results found
Top Related StackOverflow Question
No results found
Troubleshoot Live Code
Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free
Top Related Reddit Thread
No results found
Top Related Hackernoon Post
No results found
Top Related Tweet
No results found
Top Related Dev.to Post
No results found
Top Related Hashnode Post
No results found
This issue will be fixed by appending duplicating:false on include
subQuery: false
on main query will fix this