sequelize: Sequelize adding ORDER BY to subquery and main query causing ER_BAD_FIELD_ERROR

What you are doing?

When I add these two scopes, my publishedAt order gets added twice, once to the subquery (which is what i want), and once to the encasing query which causes a ER_BAD_FIELD_ERROR

FIELD_ERROR: Unknown column ‘Ticket.published_at’ in ‘order clause’

If I remove it from the outer query, the query works as expected, so my question is why would it be being added to both queries?

// Scope 1
published: function() {
 var data = {
   where: {
     status: 'published',
     publishedAt: {
       $lt: models.sequelize.fn('NOW')
     }
   },
   order: [
     ['publishedAt', 'DESC']
   ]
 };

 return data;
},


// Scope 2
decorated: function() {
  var includes = {
    include: [{
      model: models.Asset,
      as: 'FeaturedAsset'
    }, {
      model: models.Category
    }, {
      model: models.User,
      as: 'Author'
    }]
  };

  return includes;
}

What do you expect to happen?

I wanted it to order the inner query

What is actually happening?

It added the order to both inner and outer query.

SELECT ... FROM (
    SELECT ... FROM `tickets` AS `Ticket` WHERE (`Ticket`.`deleted_at` IS NULL AND (`Ticket`.`status` = 'published' AND `Ticket`.`published_at` < NOW() AND `Ticket`.`channel_id` = 3)) 
    ORDER BY `Ticket`.`published_at` DESC LIMIT 0, 6) AS `Ticket` ... 
ORDER BY `Ticket`.`published_at` DESC;

Dialect: mysql Database version: 5.6 Sequelize version: latest

About this issue

  • Original URL
  • State: closed
  • Created 8 years ago
  • Reactions: 1
  • Comments: 32

Most upvoted comments

Same problem here! Someone has a solution?

For me same issue I have tried with the latest version (5.7.0).

It happens the same to me. I have a relationship “through”. When I order the elements in order of added, this error occurs to me. Above all along with limit and offset.

Any updates until now? it is been 6 years passed since the issue came up

Any updates until now? it is been 5 years passed since the issue came up

Any solution ?

yes use in the order this

`\`table.columane\``

Is there any solution available for this, In my case I cannot do SubQuery as false, because I also have to apply the limit.

still an issue on sequelize 6.29, can someone give an update?

does adding required:true to either user or address help?

Patient.findAll({
  include: [
    {
      model: User, 
      attributes: ['full_name'],
      required:true,  // <-- require a user
      include: [ // <-- error happens if I include this
        {
         required:true,  // <-- require the address
          model: Address,
          attributes: ['city', 'state']
        }
      ]
    }
  ],
  limit: 1,
  offset: 1,
  order: [
    [User, 'full_name']
  ]
})

I have the same issue. It’s not valid to add the clause to both queries, so I’m not sure why it’s happening. This issue needs to be reopened.

Is there a fast way to solve this issue?