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)

Most upvoted comments

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.