CODEWITHSUNDEEP
node.jspostgresqlexpresssequelize.js
Denny
Denny

Reputation: 1759

Sequelize nodejs return flat JSON

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.

Upvotes: 1

Views: 2375

Answers (3)

Kavin Kumar
Kavin Kumar

Reputation: 1

If you don't want to include any additional flags and you want to flattened object in for only one caller and not in all the places you can do something like this

async function getOrders(){
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'] }]

    })}
const orders = await getOrders();
return orders.map(order=>order.get({raw:true}))

Upvotes: 0

Shahar Hadas
Shahar Hadas

Reputation: 2827

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)

Upvotes: 1

Hannan
Hannan

Reputation: 514

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

Upvotes: 3

Related Questions