Is it possible to order intermediate relation table using sequelize?

Question

I have the following scenario, my application has two entities: box and items with N to N relationship. I am using sequelize with MySQL.

I am using pseudocode to represent the tables:

Box {
  id: Integer primary key
  name: String
}

Item {
  id: Integer primary key
  name: String
}

I have set up the schemas with relations hasMany in both directions using the following through relation:

Box.hasMany(Item, { through: Box_Item });
Item.hasMany(Box, { through: Box_Item });

Box_Item {
   id_box: Integer,
   id_item: Integer,
   item_order: Integer
}

With primary_key(id_box, id_item).

I tested it and I can call myBox.getItems() on my instance object myBox and easily get all the items it has.

I can make calls as

BoxModel.findOne({
  where: { id: 1 }, 
  include: [{ model: ItemModel }]
});

And it automatically understands there is a relation between the models through Box_Item and get everything correctly, except that I'm not getting the results sorted by item_order field. This field is a number from 1 to N that represents the item order inside that box.

I tried

BoxModel.findOne({
    where: { id: 1 },
    include: [
       { 
          model: ItemModel, 
          order: 'item_order'
       }           
    ]
});

But it seems sequelizejs does not support order inside include yet (checked on their github repo).

I tried to force

BoxModel.findOne({
    where: { id: 1 },
    order: '`box_model`.`item_order`'
    include: [ { model: ItemModel } ]
})

looking through the query sequelize creates but it just put the ORDER BY in two different places (inside INNER JOIN and at the end of the query, don't know why...) and I got an error.

So I searched for this on stackoverflow (1), found a few questions but I don't get a good way for doing that using the ORM.

How could I get the items sorted by item_order field when asking for specific box items?

bpinhosilva bpinhosilva · Accepted Answer · 2016-11-15T15:36:56

After a few days trying to get it done I found an answer on stackoverflow that helped me.

After creating the relationships between Box and Item I can easily call on an instance:

myBox.getItems({
    order: '`box_model`.`item_order`'
});

And then I get the result I'm expecting. But I had to look through the query sequelize is creating based on the models and get the correct field based on their renaming rules.

If you want you can pass the as parameter and rename your tables.

Is it possible to order intermediate relation table using sequelize?

1 Answers