SequelizeDatabaseError: missing FROM-clause entry for table "mytablename" when I use required

[psixdev]

What are you doing?

I have 3 tables:

create table "parents" ("_id" serial primary key);
create table "somethings" ("_id" serial primary key);
create table "children" ("_id" serial primary key, "parentId" integer not null, "somethingId" integer not null);

And I execute this code:

const parents = await db.parents.findAll({
	include: [{
		model: db.children,
		as: 'children',
		required: true,
		include: [{
			model: db.somethings,
			as: 'something',
			required: true
		}]
	}],
	limit: 5,
	transaction,
	logging: console.log
});

Full code: https://gist.github.com/PsiXdev/09d35df41e8dc9702c4d9c85e3092b87

What do you expect to happen?

I want to get

[ { _id: 1,
    children:
     [ { _id: 1,
         parentId: 1,
         somethingId: 1,
         something: { _id: 1 } },
       { _id: 2,
         parentId: 1,
         somethingId: 1,
         something: { _id: 1 } } ] } ]

What is actually happening?

SequelizeDatabaseError: missing FROM-clause entry for table "children"
    at Query.formatError (.../node_modules/sequelize/lib/dialects/postgres/query.js:363:16)
    at query.catch.err (.../node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
    at tryCatcher (.../node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (.../node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (.../node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (.../node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (.../node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (.../node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (.../node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (.../node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:810:20)
    at tryOnImmediate (timers.js:768:5)
    at processImmediate [as _immediateCallback] (timers.js:745:5)

This code generate such sql:

SELECT "Parent".*,
       "children"."_id" AS "children._id",
       "children"."parentId" AS "children.parentId",
       "children"."somethingId" AS "children.somethingId"
FROM
  (SELECT "Parent"."_id",
          "children->something"."_id" AS "children.something._id"
   FROM "parents" AS "Parent"
   INNER JOIN "somethings" AS "children->something" ON "children"."somethingId" = "children->something"."_id"
   WHERE
       (SELECT "children"."parentId"
        FROM "children" AS "children"
        INNER JOIN "somethings" AS "something" ON "children"."somethingId" = "something"."_id"
        WHERE ("children"."parentId" = "Parent"."_id")
        LIMIT 1) IS NOT NULL
   LIMIT 5) AS "Parent"
INNER JOIN "children" AS "children" ON "Parent"."_id" = "children"."parentId";

Dialect: postgres
Database version: 9.6
Sequelize version: 4.13.8 / 4.37.10

---

Examples of changed code with a successful result (click to expand)

1. add duplicating

const parents = await db.parents.findAll({
	include: [{
		model: db.children,
		as: 'children',
		required: true,
		include: [{
			model: db.somethings,
			as: 'something',
			required: true,
			duplicating: true
		}]
	}],
	limit: 5,
	transaction,
	logging: console.log
});

SELECT "Parent".*,
       "children"."_id" AS "children._id",
       "children"."parentId" AS "children.parentId",
       "children"."somethingId" AS "children.somethingId",
       "children->something"."_id" AS "children.something._id"
FROM
  (SELECT "Parent"."_id"
   FROM "parents" AS "Parent"
   WHERE
       (SELECT "children"."parentId"
        FROM "children" AS "children"
        INNER JOIN "somethings" AS "something" ON "children"."somethingId" = "something"."_id"
        WHERE ("children"."parentId" = "Parent"."_id")
        LIMIT 1) IS NOT NULL
   LIMIT 5) AS "Parent"
INNER JOIN "children" AS "children" ON "Parent"."_id" = "children"."parentId"
INNER JOIN "somethings"AS "children->something" ON "children"."somethingId" = "children->something"."_id";

2. remove required

const parents = await db.parents.findAll({
	include: [{
		model: db.children,
		as: 'children',
		required: true,
		include: [{
			model: db.somethings,
			as: 'something'
		}]
	}],
	limit: 5,
	transaction,
	logging: console.log
});

SELECT "Parent".*,
       "children"."_id" AS "children._id",
       "children"."parentId" AS "children.parentId",
       "children"."somethingId" AS "children.somethingId",
       "children->something"."_id" AS "children.something._id"
FROM
  (SELECT "Parent"."_id"
   FROM "parents" AS "Parent"
   WHERE
       (SELECT "parentId"
        FROM "children" AS "children"
        WHERE ("children"."parentId" = "Parent"."_id")
        LIMIT 1) IS NOT NULL
   LIMIT 5) AS "Parent"
INNER JOIN "children" AS "children" ON "Parent"."_id" = "children"."parentId"
LEFT OUTER JOIN "somethings" AS "children->something" ON "children"."somethingId" = "children->something"."_id";

3. remove limit

const parents = await db.parents.findAll({
	include: [{
		model: db.children,
		as: 'children',
		required: true,
		include: [{
			model: db.somethings,
			as: 'something',
			required: true
		}]
	}],
	transaction,
	logging: console.log
});

SELECT "Parent"."_id",
       "children"."_id" AS "children._id",
       "children"."parentId" AS "children.parentId",
       "children"."somethingId" AS "children.somethingId",
       "children->something"."_id" AS "children.something._id"
FROM "parents" AS "Parent"
INNER JOIN "children" AS "children" ON "Parent"."_id" = "children"."parentId"
INNER JOIN "somethings" AS "children->something" ON "children"."somethingId" = "children->something"."_id";

I do not fully understand why all these changes work this way. In any case, it seems to me that there is a bug in the library.

[zxdvd]

Happens to me too.

[psixdev]

Today there was a similar problem, I did findOne with where: {somefield: somevalue} and I had nested include with required: true. I received a similar error. Although if I had where: {_id: someId}, there was no error. Also, the error disappeared when I added duplicating: true for all nesting levels except the first.
In the first case was generated just a crazy sql with triple SELECT instead single.

[psixdev]

Example for the second case: https://gist.github.com/PsiXdev/fe26a976bde0893ddeb2d7745ff4fa51
Tables:

create table "parents" ("_id" serial primary key, "somefield" text not null);
create table "parentToChild" ("parentId" integer not null, "childId" integer not null, "additional" text not null);
create table "children" ("_id" serial primary key);

I want to get:

{ _id: 1,
  somefield: 'somevalue',
  additionals:
   [ { parentId: 1,
       childId: 1,
       additional: 'some additional info',
       child: { _id: 1 } } ] }

but I get strange SQL query

SELECT "Parent".*,
       "additionals"."parentId" AS "additionals.parentId",
       "additionals"."childId" AS "additionals.childId",
       "additionals"."additional" AS "additionals.additional"
FROM
  (SELECT "Parent"."_id",
          "Parent"."somefield",
          "additionals->child"."_id" AS "additionals.child._id"
   FROM "parents" AS "Parent"
   INNER JOIN "children" AS "additionals->child" ON "additionals"."childId" = "additionals->child"."_id"
   WHERE "Parent"."somefield" = 'somevalue'
     AND
       (SELECT "parentId"
        FROM "parentToChild" AS "additionals"
        WHERE ("additionals"."parentId" = "Parent"."_id")
        LIMIT 1) IS NOT NULL
   LIMIT 1) AS "Parent"
INNER JOIN "parentToChild" AS "additionals" ON "Parent"."_id" = "additionals"."parentId";
-- What?!

and SequelizeDatabaseError

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

The problem does not occur if I use _id in where instead somefield. In this case I get next SQL:

SELECT "Parent"."_id",
       "Parent"."somefield",
       "additionals"."parentId" AS "additionals.parentId",
       "additionals"."childId" AS "additionals.childId",
       "additionals"."additional" AS "additionals.additional",
       "additionals->child"."_id" AS "additionals.child._id"
FROM "parents" AS "Parent"
INNER JOIN "parentToChild" AS "additionals" ON "Parent"."_id" = "additionals"."parentId"
INNER JOIN "children" AS "additionals->child" ON "additionals"."childId" = "additionals->child"."_id"
WHERE "Parent"."_id" = 1;

The second way to avoid the problem is using duplicating: true for child include.

SELECT "Parent".*,
       "additionals"."parentId" AS "additionals.parentId",
       "additionals"."childId" AS "additionals.childId",
       "additionals"."additional" AS "additionals.additional",
       "additionals->child"."_id" AS "additionals.child._id"
FROM
  (SELECT "Parent"."_id",
          "Parent"."somefield"
   FROM "parents" AS "Parent"
   WHERE "Parent"."somefield" = 'somevalue'
     AND
       (SELECT "parentId"
        FROM "parentToChild" AS "additionals"
        WHERE ("additionals"."parentId" = "Parent"."_id")
        LIMIT 1) IS NOT NULL
   LIMIT 1) AS "Parent"
INNER JOIN "parentToChild" AS "additionals" ON "Parent"."_id" = "additionals"."parentId"
INNER JOIN "children" AS "additionals->child" ON "additionals"."childId" = "additionals->child"."_id";

[psixdev]

You do not consider this a bug?

[skray]

I am having this issue as well, using Sequelize 4.38.0 and Postgres 9.6. Adding 'duplicating: true' to the nested required include statement, thank you @psixdev for suggesting that workaround.

[sushantdhiman]

Thanks for various examples, they will eventually help me. Moved to #9869

[galihaulia]

I'm sorry, I've tried using duplicating: false. but, when the data is 8, and I put a limit of: 10.The data taken is only 3

[rawojtowicz]

Adding duplicating: false to the include breaks limit and offset.

[chrisbag]

@rawojtowicz I confirm that adding duplicating = false to the include breaks limit and offset. (It is because a single query is made instead of multiple and therefore the limit is computed before duplicates are removed by sequelize)
Therefore what is the way to go to prevent the missing from Clause entry error while still using limit and offset ?

[rawojtowicz]

@chrisbag I wish i knew that, haha. I gave up on using sequelize. Afaik this bug exists since almost 8 years. I'm a VERY happy knex user now. :-)

[hayksaryan]

Any updates on this?

[JacopoCrocetta]

any update?