sequelize: Limit on nested hasMany include returns no data

Hi,

I am seeking some assistance with the use of limit on a nested hasMany include. It simply returns an empty object even though there’s actually data that should be returned.

I’ve looked through SO and the Github issues and it seems others have had similar problems and used things like distinct: true and subQuery: false to get around it - none of which have worked for me. Lots of these issues just pass me around in circles between the same posts.

I am at a loss as to whether this is a bug or if it’s something I am doing wrong, so would be great to hear for certain. I really need this limitation on specific includes as there could be hundreds of records that could possibly be returned, even though I only need the most recent 5. I can manually fetch this data, but that means more queries and processing time, then manual processing to “connect” the data back into the right places.

What are you doing?

Here is the query that has the issue:

// const references = ['PANEL00001', 'PANEL00002', 'PANEL00003']
return LocalDB.Panels
            .findAll({
                where: { reference: references },
                include: [
                    {
                        model: LocalDB.PanelEvents,
                        order: [['timestamp', 'DESC']],
                        limit: 3 //<- DOES NOT WORK - causes query to return [{}]
                    },
                    {
                        model: LocalDB.Communicators,
                        include: [
                            {
                                model: LocalDB.CommunicatorStatusUpdates,
                                order: [['timestamp', 'DESC']],
                                limit: 1 //<- DOES NOT WORK - causes query to return [{}]
                            }
                        ]
                    }
                ],
                returning: true,
                logging: false
            })

I made a full test example, which you can get here. Switch to branch 1-limit-does-not-apply-on-nested-findall on that repository and read the readme to connect to a local PG database.

What do you expect to happen?

I want to be able to apply a limit on a nested include and it return that specific number of records.

What is actually happening?

• With the limits in place on PanelEvents and CommunicatorStatusUpdates, all that gets returned for those specific includes is [{}]
• Without the limits in place, I run up my server and database costs by requesting 00’s of records when they aren’t all needed.

[
    {
        "id": 1,
        "reference": "PANEL00001",
        "model": "PANEL-X",
        "status": "ACTIVE",
        "created_at": "2018-07-11T00:00:01.000Z",
        "updated_at": "2018-07-11T00:00:01.000Z",
        "Communicators": [
            {
                "id": 1,
                "reference": "COMMUNICATO0001",
                "model": "MODEL-A",
                "panelReference": "PANEL00001",
                "created_at": "2018-07-11T00:00:01.000Z",
                "updated_at": "2018-07-11T00:00:01.000Z",
                "CommunicatorStatusUpdates": [
                    {}
                ]
            }
        ],
        "PanelEvents": [
            {}
        ]
    },
    {
        "id": 2,
        "reference": "PANEL00002",
        "model": "PANEL-Y",
        "status": "ACTIVE",
        "created_at": "2018-07-11T00:00:01.000Z",
        "updated_at": "2018-07-11T00:00:01.000Z",
        "Communicators": [
            {
                "id": 2,
                "reference": "COMMUNICATO0002",
                "model": "MODEL-B",
                "panelReference": "PANEL00002",
                "created_at": "2018-07-11T00:00:01.000Z",
                "updated_at": "2018-07-11T00:00:01.000Z",
                "CommunicatorStatusUpdates": [
                    {}
                ]
            }
        ],
        "PanelEvents": [
            {}
        ]
    },
    {
        "id": 3,
        "reference": "PANEL00003",
        "model": "PANEL-Z",
        "status": "ACTIVE",
        "created_at": "2018-07-11T00:00:01.000Z",
        "updated_at": "2018-07-11T00:00:01.000Z",
        "Communicators": [
            {
                "id": 3,
                "reference": "COMMUNICATO0003",
                "model": "MODEL-C",
                "panelReference": "PANEL00003",
                "created_at": "2018-07-11T00:00:01.000Z",
                "updated_at": "2018-07-11T00:00:01.000Z",
                "CommunicatorStatusUpdates": [
                    {}
                ]
            }
        ],
        "PanelEvents": [
            {}
        ]
    }
]

Dialect: postgres Dialect version: PostgreDB 9.6.6 (node pg: 7.4.3) Database version: PostgreDB 9.6.6 (node pg: 7.4.3) Sequelize version: 4.38.0 Tested with latest release: 4.38.0

Reading prior to posting this issue: https://github.com/sequelize/sequelize/pull/9188 https://github.com/sequelize/sequelize/issues/7344 https://github.com/sequelize/sequelize/issues/1897 https://github.com/sequelize/sequelize/issues/6891 https://stackoverflow.com/questions/31309369/sequelize-limit-and-order-inside-an-include-construct