Saturday, 8 April 2017

Sequelize.js findAll with include to search foreign_key makes page never load

Description

I have a simple database that consists of a Customer, Address, and Order. I'm trying to find all orders where the address is some string. For example I can provide the city or state and I want to lookup based on that.

Orders Table: enter image description here

Address Table: enter image description here

Orders Model:

module.exports = db.define('orders', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    repId: { type: Sequelize.INTEGER },
    totalItemCost: { type: Sequelize.DECIMAL(10,2), allowNull: false},
    shippingCost: { type: Sequelize.DECIMAL(10,2), allowNull: false},
    orderDate: { type: Sequelize.DATE, allowNull: false},
    isPaid: { type: Sequelize.BOOLEAN, allowNull: false},
    taxPercentage: { type: Sequelize.DECIMAL(10,4), allowNull: false},
}, {timestamps: false, freezeTableName: true, tableName: 'Orders'});

Address Model:

module.exports = db.define('address', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    firstName: { type: Sequelize.STRING, allowNull: false},
    lastName: { type: Sequelize.STRING, allowNull: false},
    city: { type: Sequelize.STRING, allowNull: false},
    address: { type: Sequelize.STRING(256), allowNull: false},
    zip: { type: Sequelize.STRING(10), allowNull: false},
}, {timestamps: false, freezeTableName: true, tableName: 'Address'});

Relations:

var models = {};
models.Address = require("./models/address.js");
models.Customer = require("./models/customer.js");
models.Orders = require("./models/orders.js");

// Orders Relations
models.Orders.belongsTo(models.Customer, { foreignKey: { name: 'customerId', allowNull: false }});
models.Orders.belongsTo(models.Address, { foreignKey: { name: 'shippingAddressId', allowNull: false }});

The Problem

Say for example I want to find all orders where the city is Birchwood. I'm using include in my findAll command with the model set to my address and because I've specifically named my shipping address, setting the "as" statement. This results in the page never loading and I'm not sure what I'm doing wrong. If I remove the include command, all my orders load fine. I don't see any examples for include when "belongsTo" was used. When "hasMany" is used it looks like "association" is used instead of "foreign_key". Is that the problem why "as" isn't working?

models.Orders.findAll({
    where: where,
    include: [{
        model: models.Address, 
        as: 'shippingAddressId',
        where: { 
            city: "Birchwood",
        },
    }]
}).then(function(orders) { 
    res.json(orders);
});



via Joe Jankowiak

No comments:

Post a Comment