Allow to order findAll() results by attribute on a join table

[irvingswiftj]

I would like to request that the API would allow me to order by an attribute in a custom join table.

i.e.
Using the example in the documentation:

Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});

If table UserProject had an attribute or priority, I would like to be able to get results in a manner similar to this:

User.find(1).then(function(user) {
    user.getProjects({
        order: [sequelize.someNewFunctionName(UserProjects, "priority"), "DESC"]
    }).then(function(projects) {
        //hoping to get the user's projects with the highest priorities first
    })
} );

[mickhansen]

We would likely look to extend the current order: [[Include, Include, atribute]] syntax to accomodate this.

[arenddeboer]

How would one do this in the current version ?
(sorry if i'am hijacking this feature request)

[drinchev]

It's interesting that this hasn't been implemented, because actually I achieved this with another relationship query:

With this example it would look like this :

Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});
User.belongsTo(AnotherModel);

AnotherModel.getUsers({
    include: [ { model: Project, as: 'projects' } ],
    order: [ [ Project, UserProject, 'priority', 'DESC' ] ]
}).then(function(users) {

   // users.projects will be sorted

});

This actually works, but if I try to get the projects from User model I can't order them.

[irvingswiftj]

But would that not order by a column called priority in the Projects Table, instead of a priority column in the UserProjects table ? (I might be mistaken!)

[drinchev]

Nope my example would order by a column called priority in the UserProjects table. It's tricky, because the include will LEFT OUTER JOIN "userprojects" as "project"."userprojects" and will do ORDER "project"."userprojects"."priority" DESC

[irvingswiftj]

Ah, I see. Might have to go back and update some code to sort like that. As at the moment I have some raw sql in my code! (shudder)

[drinchev]

For now I would suggest :

Sorting the javascript object after the query.

Makes more sense, since it's far more maintainable. I'm putting this issue as reference though. Anyway

[sherpya]

using order: [[Sequelize.literal("project.userprojects"), 'DESC']] does the trick

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[ernani]

In my case the BelongsToMany isn't working as the join table has a createdAt and updatedAt field that I need to have reference to.

The LEFT OUTER JOIN brings only one unique combo of
join_table.a_id_field AND join_table.b_id_field even though I've created a different primary key as I've mapped my join table as a model and gave it an id field and primary key.

The idea is that orders and order changes have a many to many relation and the join table stores the created at field by date and time, and I need its latest order at all times.

Not able to do that as of now, will try mapping it like this:

orders => hasMany => join model => hasMany => order changes

Will report updates here later on. Cheers!

[NobleUplift]

This also works for table aliases, but you have to use the table alias in the literal:

UserProject.belongsTo(Project, {
	foreignKey: "user_project_id", 
	as: "Project"
});

Sequelize.literal("Project.UserProjects.name");

[jedwards1211]

@drinchev sorting in JS is completely pointless if you need to do any kind of paging, which is probably why your comment got so many downvotes. Do you really not need to do any kind of paging in your apps?

[erfanio]

I ran into a weird issue that the generated sql didn't have quotes around the Sequelize.literal so I had to put quotes in the literal itself

  include: [{
    model: Outer,
    as: 'outer',
    through: {
      model: Inner,
      as: 'inner',
    },
    required: false,
  }],
  order: [
    [Sequelize.literal('"outer.inner.createdAt"'), 'asc'],
  ],

I thought I should post here for anyone else getting weird errors

[gitramro]

I was having an issue using Sql Server, I was getting the error The multi-part identifier "winner.new_apv" could not be bound.

let order = [["winner","new_apv","asc"]];

 const include = [
    {
      model: Item_Supplier,
      as: "itemSupplier",
      attributes: ["id", "supplierOrderId", "cost"],
      include: [
        {
          model: Supplier,
          as: "supplier"
        }
      ]
    },
    {
      model: Winner,
      as: "winner",
      attributes: ["supplierId", "new_apv"],
      include: [
        {
          model: Supplier,
          as: "supplier",
          attributes: ["supplierName"]
        }
      ]
    }
  ];

 await Item.findAll({include,order})

Using the Sequelize literal and the single quotes in the literal itself as erfanio says did the trick

order = [[Sequelize.literal('"winner.new_apv"'), desc ? "DESC" : "ASC"]]

[NobleUplift]

@erfanio Thanks for posting! With my luck on Sequelize, it's only a matter of time before I need this. Talk about a dumb solution having to tell the ORM when to use quotes.

[mickhansen]

literal() means literal, obviously it's not going to be quoted when you ask the ORM to put literallly what you tell it to.

[markdid]

I'm still a little confused on this. Was this added as a feature? I have a table "events" and a table "users", with a join table "events-users".
The events is defined as belongsToMany through the events-users table which has a event_id and user_id field to join, so that when I query events, the users that are added to that event show up as well.
The problem is I want to sort the users in each event in a queue. Right now it's not clear how it sorts the users, I assume just by created_by but that's not specified anywhere. How can I specify the events to sort the users by a separate field in my events-users table "queue_index", instead of the default. That way I could rearrange the associated data.

I assumed there would be a field for that somewhere in the belongsToMany definition here for example:
this.belongsToMany(models.users, {through: models['events-users'], foreignKey: 'event_id', order: "queue_index"});
Any help would be appreciated, thanks