Sequelize - How to apply Distinct to nested include?

Question

I am using Nodejs, Express, Postgresql, and Sequelize

I have 4 Models

1. Account (has many books)

2. Book (has many parts)

3. Part (has many chapters)
4. Chapter

I'm returning a book and including both 'Part' and 'Chapter' with some specific ordering - This works fine.

The model 'Chapter' has a column 'topic' and I only want to include rows from the 'Chapter' model that have distinct 'topics'.

How do I apply distinct to this 'Chapter' model with this nested include structure?

Code snippet:

return Book

.findOne({
    where: {accountid: req.body.accountid,
        id: req.body.bookid}, 
    include: [{
        model: Part,
        as: 'parts'
        include: [
            {
                model: Chapter,
                required: false,
                as: 'chapters',
                where: {
                    createdAt: {
                        $gte: moment().subtract(24,'hours').format()
                    }
                }
            }],
    }],
    order: [
        [
            { model: Part, as: 'parts' },
            'createdAt',
            'DESC'
        ],
        [
            { model: Part, as: 'parts' },
            { model: Chapter, as: 'chapters' },
            'createdAt',
            'DESC'
        ]
    ]


})
.then(book => res.status(200).send(book))
.catch(error => res.status(400).send(error.toString()));
}

Answer 1

Sequelize distinct feature doesn't work in case of include.

You can use sequelize raw queries to achieve your result.

Answer 2

I know this is a really late answer but in fact you can have distinct on include table columns. The only drawback is that you have to analyze the query that Sequelize generates and include the attributes from each model.

Your query would end up as something like this:

Book.findOne({
    // Literal DISTINCT ON as 1 so it is not included in the resulting json
    // '*' so all table attributes are included in the json response
    attributes: [Sequelize.literal('DISTINCT ON("chapters"."topics") 1'), '*']
    where: {accountid: req.body.accountid,
        id: req.body.bookid}, 
    include: [{
        model: Part,
        as: 'parts'
        include: [
            {
                model: Chapter,
                required: false,
                as: 'chapters',
                where: {
                    createdAt: {
                        $gte: moment().subtract(24,'hours').format()
                    }
                }
            }],
    }],
    order: [
        // This order criteria is required because we are using distinct on
        [
            { model: Chapter, as: 'chapters' },
            'topics',
            'ASC'
        ],
        [
            { model: Part, as: 'parts' },
            'createdAt',
            'DESC'
        ],
        [
            { model: Part, as: 'parts' },
            { model: Chapter, as: 'chapters' },
            'createdAt',
            'DESC'
        ]
    ]


})

This same strategy can be used to make a 'distinct on' more than one column, but youĺl have to remember including the columns as the first search criteria.
Searching 'DISTINCT ON' on the Sequelize's Github leads to several interesting posts on this.

Hope it helps. Best regards from Chile.