Sequelize missing FROM-clause entry for table Postgres

Question

I am trying to filter my DataRow objects by where the included DataPoints data is loc and sortOrder is 2. Below is the query I am trying. I keep getting the following error.

SequelizeDatabaseError: missing FROM-clause entry for table "dataPoints"

I have tried setting required: true and duplicating: false with no luck. I tried these in both the DataRows include as well as DataPoints.

    attributes: ['id', 'name', 'labId'],
    include: [{
      as: 'dataColumns',
      model: DataColumn,
      attributes: ['id', 'name', 'sortOrder', 'dataType', 'isDate', 'isLocation'],
    }, {
      as: 'dataSets',
      model: Study,
      attributes: ['id', 'name'],
    }, {
      as: 'dataRows',
      model: DataRow,
      attributes: ['id'],
      where: {
        [Op.and]: [
          {
            '$dataPoints.data$': 'loc',
          },
          {
            '$dataPoints.sortOrder$': 2,
          },
        ],
      },
      include: [{
        as: 'dataPoints',
        model: DataPoint,
        attributes: ['id', 'sortOrder', 'data'],
      }],
    },],
    order: [
      [{ model: DataColumn, as: 'dataColumns' }, 'sortOrder', 'ASC'],
      [{ model: DataRow, as: 'dataRows' }, { model: DataPoint, as: 'dataPoints' }, 'sortOrder', 'ASC'],
    ],
  }

Did you find any solution?

Ali Hesari
– Ali Hesari

2021-04-09 17:26:21 +00:00
Commented Apr 9, 2021 at 17:26 — Ali Hesari
– Ali Hesari, Commented Apr 9, 2021 at 17:26

buddemat · Accepted Answer · 2021-06-24 10:05:58Z

16

Try required: true and duplicating: false on all the models that you're trying to include. This would solve the problem. Worked for me.

edited Jun 24, 2021 at 10:05

buddemat

5,32816 gold badges37 silver badges61 bronze badges

answered Jun 24, 2021 at 9:00

Mohanapriya

1691 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucas Fontes Gaspareto Over a year ago

Worked nice here! Can you explain why this properties work?

Shubham Singh Over a year ago

if we need left join then how we can do required true

Abdul Rab Memon · Accepted Answer · 2022-06-28 13:38:47Z

9

If you are here like me who has used a where condition on a column of included table in the primary where and got this error, just use this in the options of primary query.

{ subQuery: false }

answered Jun 28, 2022 at 13:38

Abdul Rab Memon

4355 silver badges19 bronze badges

2 Comments

Paweł O. Over a year ago

Here's a Sequelize docs on that: sequelize.org/docs/v6/other-topics/sub-queries Unfortunately it does not explain the case with subQuery when using this kind of "Primary where" search with included models.

Tanner Summers Over a year ago

This breaks limit for me sadly

Tyler2P · Accepted Answer · 2022-08-28 14:28:38Z

1

Follow this code:

attributes: ['id', 'name', 'labId'],
    include: [{
      as: 'dataColumns',
      model: DataColumn,
      attributes: ['id', 'name', 'sortOrder', 'dataType', 'isDate', 'isLocation'],
    }, {
      as: 'dataSets',
      model: Study,
      attributes: ['id', 'name'],
    }, {
      as: 'dataRows',
      model: DataRow,
      attributes: ['id'],
      where: {
        [Op.and]: [
          {
            '$dataPoints.data$': 'loc',
          },
          {
            '$dataPoints.sortOrder$': 2,
          },
        ],
      },
      include: [{
        as: 'dataPoints',
        model: DataPoint,
        attributes: ['id', 'sortOrder', 'data'],
      }],
    },],
    order: [
      [{ model: model.DataColumn, as: 'dataColumns' }, 'sortOrder', 'ASC'],
      [{ model: model.DataRow, as: 'dataRows' }, { model: DataPoint, as: 'dataPoints' }, 'sortOrder', 'ASC'],
    ],
  }

edited Aug 28, 2022 at 14:28

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Aug 24, 2022 at 11:54

Bhavesh Singh

111 bronze badge

1 Comment

Subhan Ahmed Over a year ago

Looks good approach, though I have not tested myself yet.

MaximillianCohen · Accepted Answer · 2021-09-28 16:38:59Z

0

Required and duplicating set to true and false worked , the issue comes when you try to eager load a model and the search field is not unique, in my case i was able to set my field as unique, getting rid of setting required and duplicating properties in the eager load query.

answered Sep 28, 2021 at 16:38

MaximillianCohen

1

Comments

user17862633 · Accepted Answer · 2022-02-23 09:24:23Z

0

You need to move this code

where: {
    [Op.and]: [
        {
            "$dataPoints.data$": "loc",
        },
        {
            "$dataPoints.sortOrder$": 2,
        },
    ],
},

to up below attributes and change to

where: {
    [Op.and]: [
        {
            "$dataRows.dataPoints.data$": "%loc%",
        },
        {
            "$dataRows.dataPoints.sortOrder$": 2,
        },
    ],
},

answered Feb 23, 2022 at 9:24

user17862633

Collectives™ on Stack Overflow

Sequelize missing FROM-clause entry for table Postgres

5 Answers 5

2 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related