mardi 14 juin 2016

Sequelize: Eager Loading and Ordering (on parent table)

I'm Sequelize in a FeathersJS project and I'm having an issue when attempting to eager load associations and ordering by parent table. If I just add the order OR the include to the query everything works fine, it's when I use both that I get the error: Unhandled rejection Error: 'sales_flat_order' in order / group clause is not valid association

Query:

sequelize.models.orders
    .findAll({
      limit: 10,
      include: [ sequelize.models.items ],
      order: [[ sequelize.models.orders, 'createdAt' ]],
    })
    .then(orders => {
      // Error
    });

Order Model:

const Sequelize = require('sequelize');

module.exports = function(sequelize) {
  const order = sequelize.define('orders', {
    id: { type: Sequelize.INTEGER, primaryKey: true, field: 'entity_id' },
    customerEmail: { type: Sequelize.STRING, field: 'customer_email' },
    customerFirstName: { type: Sequelize.STRING, field: 'customer_firstname' },
    customerLastName: { type: Sequelize.STRING, field: 'customer_lastname' },
    state: { type: Sequelize.STRING },
    grandTotal: { type: Sequelize.FLOAT, field: 'grand_total' },
    createdAt: { type: Sequelize.DATE, field: 'created_at' },
    updatedAt: { type: Sequelize.DATE, field: 'updated_at' },
  }, {
    freezeTableName: true,
    tableName: 'sales_flat_order',
    classMethods: {
      associate(models) {
        order.hasMany(models.items, { foreignKey: 'orderId' });
      }
    }
  });

  return order;
};

Item Model:

const Sequelize = require('sequelize');

module.exports = function(sequelize) {
  const item = sequelize.define('items', {
    id: { type: Sequelize.INTEGER, primaryKey: true, field: 'item_id' },
    orderId: { type: Sequelize.INTEGER, field: 'order_id' },
    productId: { type: Sequelize.INTEGER, field: 'product_id' },
    productType: { type: Sequelize.STRING, field: 'product_type' },
    sku: { type: Sequelize.STRING },
    name: { type: Sequelize.STRING },
    qtyOrdered: { type: Sequelize.INTEGER, field: 'qty_ordered' },
    price: { type: Sequelize.FLOAT },
    createdAt: { type: Sequelize.DATE, field: 'created_at' },
    updatedAt: { type: Sequelize.DATE, field: 'updated_at' },
  }, {
    freezeTableName: true,
    tableName: 'sales_flat_order_item',
    classMethods: {
      associate(models) {
        item.belongsTo(models.orders, { foreignKey: 'orderId' });
      }
    }
  });

  return item;
};

Aucun commentaire:

Enregistrer un commentaire