I am using sequelize with nodejs for a RESTful API. I have 2 models, Security and Orders. The order model references the security model using a foreign key via symbol.
Security model:
classMethods: {
associate: function(models) {
// associations
security.hasMany(models.order, { foreignKey: 'security_symbol' });
}
}
Order model:
classMethods: {
// associations
associate: function(models) {
order.belongsTo(models.security, { foreignKey: 'security_symbol' })
}
}
I then perform a query to return all orders which is working OK, though I want some other attributes of the security model to be included in a sort of 'flattened' json. Query:
Order.findAndCountAll({
where: { user_id: user.id},
attributes: [
'id', 'side', 'quantity', 'price', 'status', 'created_at'
],
order: [
['id', 'DESC']
],
include: [{ model: models.security, attributes: ['name', 'symbol'] }]
})
Response:
"orders": [
{
"id": 25,
"side": 1,
"quantity": 1150,
"price": "13.33",
"status": 0,
"created_at": "2017-04-27T09:51:41.479Z",
"security": {
"name": "Car & General (K) Ltd",
"symbol": "C&G"
}
},
{
"id": 24,
"side": 1,
"quantity": 1000,
"price": "5.63",
"status": 4,
"created_at": "2017-04-27T09:50:31.939Z",
"security": {
"name": "Eveready East Africa Ltd",
"symbol": "EVRD"
}
}...
Desired Response:
"orders": [
{
"id": 25,
"side": 1,
"quantity": 1150,
"price": "13.33",
"status": 0,
"created_at": "2017-04-27T09:51:41.479Z",
"security_name": "Car & General (K) Ltd",
"security_symbol": "C&G"
},
{
"id": 24,
"side": 1,
"quantity": 1000,
"price": "5.63",
"status": 4,
"security_name": "Eveready East Africa Ltd",
"security_symbol": "EVRD"
"created_at": "2017-04-27T09:50:31.939Z",
}...
Difference being the 'parent' not being nested inside the order.
You can use the raw attribute like below
Order.findAndCountAll({
where: { user_id: user.id},
attributes: [
'id', 'side', 'quantity', 'price', 'status', 'created_at'
],
order: [
['id', 'DESC']
],
include: [{ model: models.security, attributes: ['name', 'symbol'] }]
raw: true
})
But then sequelize will give you result like
"orders": [
{
"id": 25,
"side": 1,
"quantity": 1150,
"price": "13.33",
"status": 0,
"created_at": "2017-04-27T09:51:41.479Z",
"security.name": "Car & General (K) Ltd",
"security.symbol": "C&G"
},
{
"id": 24,
"side": 1,
"quantity": 1000,
"price": "5.63",
"status": 4,
"security.name": "Eveready East Africa Ltd",
"security.symbol": "EVRD"
}...
I'm not sure if security.name& security.symbol is desirable or not. But this is how sequelize will behave. Hope this helps
Try the following:
Order.findAndCountAll({
where: { user_id: user.id},
attributes: [
'id', 'side', 'quantity', 'price', 'status', 'created_at',
[Sequelize.literal('"order->security".name'), 'security_name'],
[Sequelize.literal('"order->security".symbol'), 'security_symbol']
],
order: [
['id', 'DESC']
],
include: [{ model: models.security, attributes: []}]
})
Please note you might need to update order->security to actual DB table names (orders and securities)