Sequelize: how to get flattened data, when joining table based on nested associations?

user_7453707 0
Viewed 2078

I have relations defined like,

device_data.belongsTo(models.device)
device.belongsTo(models.category)
category.belongsTo(models.role)

What I am trying to get is all the device data which falls under a specific role. Right now I have the following working:

const query = {
    include: [{
        model: device,
        attributes: ['id'],
        include: [{
            model: category,
            attributes: ['id'],
            include: [{
                model: role,
                attributes: ['id'],
                where: {
                    id: {
                        [Op.eq]: roleId,
                    },
                },
                required: true,
            }],
            required: true,
        }],
        required: true,
    }],
};

Obviously, this gives returns me formatted data like following:

{
    "id": 1,
    "created_timestamp": "2019-08-12T12:28:36.194Z",
    "device_id": 1,
    "device_value": 0,
    "initiator_id": 2,
    "device": {
        "id": 1,
        "category": {
            "id": 1,
            "role": {
                "id": 1
            }
        }
    }
},

Where role is nested in 3rd level and the role.id in 4th.

What I am trying to achieve is:

{
    "id": 1,
    "created_timestamp": "2019-08-12T12:28:36.194Z",
    "device_id": 1,
    "device_value": 0,
    "initiator_id": 2,
    "device": 1,
    "category": 1,
    "role": 1
},

I would appreciate a lot if someone could point me a hint or possibly the query to generate data like this from sequelize. Thank you very much.

sequelize.js node.js postgresql javascript
1 Answers

  1. To disable nesting by model, use raw: true in your query.

  2. To remove the table name prefix, relocate the attributes to your top-level model but add the table alias.

For example:

TopModel.find(
   raw: true,
   attributes : 
   [
    'id', 'created_timestamp',   // from main table
    'device.id'                  // from included table
   ],
   include [{
        model: device,
        attributes: [],  // nothing at this level
   }]
)

// etc, etc,

I am just guessing that device is the table alias. if you get an error saying (roughly) "Field does not exist", consult the generated SQL to get the proper alias.

user_28649280
Related