sequelize - order by column from included model

user_571723 0
Viewed 717

I can't figure out how to order by a column that is in my included model. But I'm not even returning that field in the attributes. I just want to order by it if that is possible. Or I can return it and not use as long as I can sort by it.

const users = await db.User.findAll({
    raw: true,
    attributes: [
        'id',
        'first_name',
        'last_name',
        'email',
        'Permission.type'
    ],
    include: [
    {
        model: db.Permission,
        where: { id: { [Op.lte]: 2 } },
        attributes: [],
        order: [[{ model: db.Permission }, 'id', 'DESC']]
    }
    ]
});

In the include block the order block does nothing right now. But I have an attribute in my Permission model called id that I want to order all results by. I am fine with adding Permission.id to my attributes if that is necessary. But I tried it and it still didn't work.

sequelize.js sql-order-by
1 Answers

Just move order option from include to main options:

const users = await db.User.findAll({
    raw: true,
    attributes: [
        'id',
        'first_name',
        'last_name',
        'email',
        'Permission.type'
    ],
    include: [
    {
        model: db.Permission,
        where: { id: { [Op.lte]: 2 } },
        attributes: []
    }
    ],
    order: [[{ model: db.Permission }, 'id', 'DESC']]
});

Also maybe you should indicate id in include attributes.

user_13766180
Related