Sometimes we want to get more data from the database at once. Sequelize gives us such possibility by including models. But what if we want to do something more with these joined models? Let’s take a look at some cases.

Sample model

In this post we will work on such structure of data:

As you may see, it’s simple structure of survey questions database, organized in groups (Survey_Group). We can create one survey (Survey) per project (Project) and select for this survey questions from the database (Survey_Questions ↔️ Survey_to_Questions ↔️ Survey); Client can answer these questions in application and then we save those answers in Survey_Answers table. Questions are ordered in a specific way, so it’s crucial to show them the same way; Because of that, Survey_Question has one extra column orderIndex to keep order of questions inside the group. This is our main case study for this post.

Sorted data in Sequelize

To keep order in Sequelize we use order param. It gets an array of order specifications. These order specifications are also arrays, but defined in particular way. The simplest variant is just:

Survey_Questions.findAll({
  order: [["orderIndex", "ASC"]],
});

Which means that our data will be sorted by orderIndex column ascending. Of course, we can also use DESC to revert direction.

Order by included model

Getting data from one table seems to be boring case, so let’s check, how it is going when we try to get survey groups with questions:

Survey_Groups.findAll({
  order: [[Survey_Questions, "orderIndex", "ASC"]],
  include: {
    model: Survey_Questions,
  },
});

We added model to include and the same model is used in order params, where we declare that we want to order results by orderIndex from joined table. It’s easy and elegant, but what if we want to order also by group name? This is why we use wrapper array here. It allows us to send more than one order condition, like this way:

Survey_Groups.findAll({
  order: [["name", "ASC"], [(Survey_Questions, "orderIndex", "ASC")]],
  include: {
    model: Survey_Questions,
  },
});

We didn’t use model object here, since it refers to main model we work on.

Order in nested models

Following this solution, we can go deeper and try to get the whole survey with all answers and its questions ordered by groups.

Surveys.findOne({
  where: {
    id,
  },
  order: [
    [Survey_Questions, Survey_Groups, "name", "ASC"],
    [Survey_Questions, "orderNumber", "ASC"],
  ],
  include: [
    {
      model: Survey_Questions,
      include: { model: Survey_Groups },
    },
    {
      model: Survey_Answers,
    },
  ],
});

Ok, it looks finally more serious. We include two external models: Survey_Questions and Survey_Answers. The case is that we want to sort our results by group name and next, by our orderIndex field. But Survey_Groups is included in Survey_Questions model, not directly in Survey. This is why we have to show that nesting in our order param, by adding models as a path to the field. Neat.

Sequelize gives us complete mechanism to sorting our results, but we still have to keep in mind to limit our queries and data in it (eg. by using attributes, to limit fields included in a query).