Sequelize options.nest in query() method doesn't work

user_6152338 0
Viewed 4789

I have a raw query like

squelize.query(
   `select "order".id, "orderCustomer".id as "orderCustomer.id", "orderCustomer".forename as "orderCustomer.forename" 
    from orders as "order"
    join customers as "orderCustomer" on "orderCustomer".id = "order"."orderCustomerId"
    where "orderCustomer".forename ilike '%petra%';`,
{
    type: Sequelize.QueryTypes.SELECT, 
    model: order,
    nest: true,
    mapToModel: true
})

When I query this in psql I get a correct result set:

-[ RECORD 1 ]----------+------
id                     | 383
orderCustomer.id       | 446
orderCustomer.forename | Petra
-[ RECORD 2 ]----------+------
id                     | 419
orderCustomer.id       | 9
orderCustomer.forename | Petra

The problem is, that Sequelize is apparently not able to form this into an Array of the kind

[
 {
   id: 383,
   orderCustomer: {
      id: 446,
      forename: 'Petra'
   }
 },
   ...
]

Instead I get something like this:

[
 {
   id: 383,
   'orderCustomer.id': 446,
   'orderCustomer.forename': 'Petra'
 },
   ...
]

Do I need to include the customer-model in the query's option-object?

UPDATE

I logged the result of my query. There is this property _options on all of the returned order-instances:

_options:{ 
   isNewRecord: false,
   _schema: null,
   _schemaDelimiter: '',
   raw: true, // <--------- possible cause?
   attributes: undefined 
}

Somehow, I cannot set options.raw to false on my query definition! Maybe that is the reason why …

…sequelize will not try to format the results of the query, or build an instance of a model from the result (see docs > query() > options.raw)

Any ideas?

sequelize.js node.js postgresql javascript
5 Answers

I'm using your solution: nest: true and it works perfectly!

[err, rows] = await to(sequelize.query(query,{ replacements: { search: '%'+search+'%', limit: limit, offset: skip}, type: sequelize.QueryTypes.SELECT,nest: true}));
user_43405160

Try it without the

model: order,

this solved it for me

user_86494530

It is 2021 and seems that this is still doesn't work with Sequelize 6.6.2. Or rather I can't work out how to make the mapToModel work in conjunction with the nested properties.

I worked around the issue by removing the model: MyModel, mapToModel: true, part so that the query options look like this:

{ type: QueryTypes.SELECT, bind: [mainAssetId], nest: true, raw: false }

I get the object back with all the fields and nested works but it is of course not an instance of the Model. It looks like the model from data perspective so as long as you don't have methods on your model you should be fine.

You could always use something like the class-transformer package to transform it to the model instance.

user_18080480

I had the same issue and made it work this way:

        sequelize.query(
            `SELECT q.id, q.title, a.id AS "answers.id"
               FROM questions q
               JOIN answers a ON a.question_id = q.id`,
                model: Question,
                mapToModel: true,
                nest: true,
                raw: true,
                type: sequelize.QueryTypes.SELECT }
        )
user_9119870
Related