Sunday, August 19, 2018

Sequelize generates a not working SQL when use order option

when I use order option in #findAll it generates SQL:

SELECT
  `id`, `first_name` AS `firstName`,
  `last_name` AS `lastName` FROM `customers` AS `Customer`
ORDER BY `Customer`.`firstName` DESC;

but this SQL causes error:

ER_BAD_FIELD_ERROR: Unknown column 'Customer.firstName' in 'order clause'

Code example:

var Customer = sequelize.define("Customer", {
  id: {
    type: Sequelize.INTEGER({unsigned: true}),
    primaryKey: true
  },
  firstName: {
    type: Sequelize.STRING(32),
    field: "first_name"
  },
  lastName: {
    type: Sequelize.STRING(32),
    field: "last_name"
  }
}, {
  name: {
    singular: "customer",
    plural: "customers"
  },
  tableName: "customers",
  timestamps: false,
  underscored: true
});

Customer.findAll({
  order: [["firstName", "DESC"]]
}).then(function(list) {
  console.log(list);
}).catch(function(err) {
  console.log(err);
});

Mysql: 5.6.20

Sequelize: 3.14.2

Are there any solutions of this issue?

Solved

Use order: [["first_name", "DESC"]]; the ORDER BY looks at column names, not your SELECT alias firstName.


No comments:

Post a Comment