Include + Where Inside Include + Limit

[iskandargci]

What I am doing?

So I have this below query:

var db = require(__base + 'models');
var orderItemConditions = {};
var itemConditions = {};
var itemGroupConditions = {};

/**
* The conditions constructed here
*/

db.order.findAndCount({
    include: [{
        model: db.order_item,
        as: 'order_items',
        attributes: ['id', 'price', 'quantity', 'item_id'],
        where: orderItemConditions,
        include: [{
            model: db.item,
            as: 'item',
            attributes: ['name', 'item_group_id', 'sku'],
            where: itemConditions,
            include: [{
                model: db.item_group,
                as: 'item_group',
                attributes: ['name', 'item_category_id'],
                where: itemGroupConditions,
            }]
        }]
    }],
    order: '"order".created_at DESC',
    where: orderCondition,
    limit: 20,
    offset: 0
});

The association at "order" model

db.order.hasMany(db.order_item, {
    as: 'order_items'
});

This association at "order_item" model

db.order_item.belongsTo(db.item, {
    foreignKey: 'item_id',
    as: 'item'
});

db.order_item.belongsTo(db.order, {
    foreignKey: 'order_id',
    as: 'order'
});

The association at "item" model

db.item.hasMany(db.order_item, {
    as: 'order_items'
});

The association at "item_group" model

db.item_group.hasMany(db.item, {
    as: 'items'
});

And the generated query is:

SELECT
	"order".*,
	"order_items"."id" AS "order_items.id",
	"order_items"."price" AS "order_items.price",
	"order_items"."item_id" AS "order_items.item_id"
FROM (
	SELECT
		"order"."id",
		"order"."transaction_no",
		"order"."description",
		"order_items.item"."id" AS "order_items.item.id",
		"order_items.item"."name" AS "order_items.item.name",
		"order_items.item.item_group"."id" AS "order_items.item.item_group.id",
		"order_items.item.item_group"."name" AS "order_items.item.item_group.name",
	FROM "order" AS "order"
	INNER JOIN "item" AS "order_items.item" ON "order_items"."item_id" = "order_items.item"."id"
		AND "order_items.item"."deleted_at" IS NULL
	INNER JOIN "item_group" AS "order_items.item.item_group" ON "order_items.item"."item_group_id" = "order_items.item.item_group"."id"
		AND "order_items.item.item_group"."deleted_at" IS NULL
	WHERE "order"."deleted_at" IS NULL
	AND (
		SELECT "order_id"
		FROM "order_item" AS "order_item"
		WHERE (
			"order_item"."order_id" = "order"."id"
			AND "order_item"."deleted_at" IS NULL
		)
		LIMIT 1
	) IS NOT NULL
	LIMIT 20
	OFFSET 0
) AS "order"
INNER JOIN "order_item" AS "order_items" ON "order"."id" = "order_items"."order_id"
	AND "order_items"."deleted_at" IS NULL;

What is actually happening?

The query produce error:

missing FROM-clause entry for table "order_items"

I know this happen because "order_items" called in subQuery, while it define in the outer query

What do I expect to happen?

I have a list with filter form to search in table "order", "order_item", "item", "item_group", and I need limitation for pagination of "Order" list. I understand sequelize should make subQuery of table "order" to make the limitation on "Order" list works (so I can't add option "subQuery: false"). But my question is why table "item" and "item_group" appears inside the subQuery of table "order" while its association is at outer query? How to send table "item" and "item_group" to outer query? So the generated query should be more like:

SELECT
	"order".*,
	"order_items"."id" AS "order_items.id",
	"order_items"."price" AS "order_items.price",
	"order_items"."item_id" AS "order_items.item_id",
	"order_items.item"."id" AS "order_items.item.id",
	"order_items.item"."name" AS "order_items.item.name",
	"order_items.item.item_group"."id" AS "order_items.item.item_group.id",
	"order_items.item.item_group"."name" AS "order_items.item.item_group.name"
FROM (
	SELECT
		"order"."id",
		"order"."transaction_no",
		"order"."description"
	FROM "order" AS "order"
	WHERE "order"."deleted_at" IS NULL
	LIMIT 20
	OFFSET 0
) AS "order"
INNER JOIN "order_item" AS "order_items" ON "order"."id" = "order_items"."order_id"
	AND "order_items"."deleted_at" IS NULL
-- [orderItemConditions]
INNER JOIN "item" AS "order_items.item" ON "order_items"."item_id" = "order_items.item"."id"
	AND "order_items.item"."deleted_at" IS NULL
-- [itemConditions]
INNER JOIN "item_group" AS "order_items.item.item_group" ON "order_items.item"."item_group_id" = "order_items.item.item_group"."id"
	AND "order_items.item.item_group"."deleted_at" IS NULL;
-- [itemGroupConditions]

__Dialect: PostgreSQL
__Database version: 9.4.10
__Sequelize version: 3.22.0

[janmeier]

#3401
#4364

[janmeier]

Thanks for the thorough issue description though, just trying to keep the number of issues down :)