How to parse model and associations from raw query?

[alekbarszczewski]

From time to time I have to write very complex query that cannot be handled by Sequelize default methods. So I write raw query by hand and I execute it using sequelize instance and passing also sequelize model:

Post.hasMany(PostTranslation);

q = '
SELECT 
   "Posts".*, 
   "PostTranslations"."id" AS "PostTranslations.id", 
   "PostTranslations"."language" AS "PostTranslations.language",
   "PostTranslations"."PostId" AS "PostTranslations.PostId"
FROM "Posts" 
INNER JOIN "PostTranslations" AS "PostTranslations"
ON ("Posts"."id" = "PostTranslations"."PostId")"
';

sequelize.query(q,Post).done(function(err,posts){
    console.error(posts[0])
})

That partialy works as I get array of post models however Sequelize does not detect that there are any associated models included... Is there any way to "inform" Sequelize how to parse selected rows to build models with their associations?

For now posts[0].dataValues looks like this:

{      id: 1,
       type: 'video',
       isPublished: true,
       publishedAt: Wed Jan 01 2014 01:00:00 GMT+0100 (CET),
       thumbnailUrl: 'http://placehold.it/230x150&text=post1',
       isFeatured: false,
       isDailyContent: false,
       lovesCount: 0,
       commentsCount: 0,
       CategoryId: 1,
       SeriesId: 1,
       UserId: 4,
       AuthorId: null,
       createdAt: Wed May 28 2014 18:07:28 GMT+0200 (CEST),
       updatedAt: Wed May 28 2014 18:07:28 GMT+0200 (CEST),
       'PostTranslations.id': 2,
       'PostTranslations.language': 'zh-cn',
       'PostTranslations.PostId': 6 }

As you can see all columns are treated as Post model properties instead of PostTranslation properties. Am I doing something wrong? Using Sequelize 1.7.5

[mickhansen]

You'll have to provide include options and make sure they are valided aswell.

Something like this might work:

var options = {
  include: etc
};
Sequelize.Model.validateIncludedElements(options);
sequelize.query(q,Post, options).done(function(err,posts){
    console.error(posts[0])
})

[mickhansen]

Not sure this will work though, you'll have to fiddle around with it.

[alekbarszczewski]

Hmm I can't figure out how to access validateIncludedElements. Sequelize.Model is function that does not have this method... I couldnt find this method in any of available objects: Post, sequelize (instance), Sequelize, Sequelize.Model - also tried $validateIncludedElements (I found it in src) but I couldnt find it either...

[mickhansen]

Are you using master? I just realized it's only exposed as Sequelize.Model.$validateIncludedElements on the current master.

[alekbarszczewski]

I am using sequelize 1.7.5. Is it possible to make it work on this version?

[mickhansen]

Most likely not, not without modifying your local copy of sequelize.

[alekbarszczewski]

Damn so I stucked :(. I guess its high time I switched to master...

[mickhansen]

Yeah unfortuneatly so, unless you want to fork 1.7.6 and provide the small change for your local development untill your ready to switch to master.

[alekbarszczewski]

Hmm I have problem with installing latest (unstable) version of sequelize... I tried npm install --save sequelize@unstable - it installed sequelize with correct package.json (with version 2.0.0-dev11) however with invalid (old) source code - ie. there is lib/dao.js instead of lib/model.js -> this is weird...

[mickhansen]

dev11 is not master :) To install master you should npm install --save sequelize/sequelize

[alekbarszczewski]

Thanks, I managed to install master. Hovever I still can't make it work according to my issue:

Post.hasMany(PostTranslation);

q = '
SELECT 
   "Posts".*, 
   "PostTranslations"."id" AS "PostTranslations.id", 
   "PostTranslations"."language" AS "PostTranslations.language",
   "PostTranslations"."PostId" AS "PostTranslations.PostId"
FROM "Posts" 
INNER JOIN "PostTranslations" AS "PostTranslations"
ON ("Posts"."id" = "PostTranslations"."PostId")"
';

options = { include: [{ model: PostTranslation }] }

Sequelize.Model.$validateIncludedElements.call(Post,options);

sequelize.query(q,Post,options).done(function(err,posts){
    console.error(posts[0])
})

Result is same as I wrote before - PostTranslations is not parsed from the query result and still:

{      id: 1,
       type: 'video',
       isPublished: true,
       publishedAt: Wed Jan 01 2014 01:00:00 GMT+0100 (CET),
       thumbnailUrl: 'http://placehold.it/230x150&text=post1',
       isFeatured: false,
       isDailyContent: false,
       lovesCount: 0,
       commentsCount: 0,
       CategoryId: 1,
       SeriesId: 1,
       UserId: 4,
       AuthorId: null,
       createdAt: Wed May 28 2014 18:07:28 GMT+0200 (CEST),
       updatedAt: Wed May 28 2014 18:07:28 GMT+0200 (CEST),
       'PostTranslations.id': 2,
       'PostTranslations.language': 'zh-cn',
       'PostTranslations.PostId': 6 }

[mickhansen]

Yeah it appears it's a bit more advanced than that, take a look at: https://github.com/sequelize/sequelize/blob/master/lib/query-interface.js#L538

You might need to validate options and then pass them to query like it's done here. Although looking at the params it should work with what you have right now.

[alekbarszczewski]

Hmm, for now I cant make it work :(. I looked up queryOptions that are passed when normal Post.findAll is executed and it's pretty much the same as queryOptions after $validate...

[alekbarszczewski]

I am trying still trying to make it work. For now I get strange error which appears only on 2.0.0-dev11 and master and it works fine on 1.7.5 - For the following code:

Post.hasMany(PostTranslation)
PostTranslation.belongsTo(Post)
...
Post.findAll({ include: [{ model: PostTranslation }] }).done (err, posts) ->
   throw err if err
   res.json { items: posts }

Code above generates following error - any ideas what might be wrong? I guess this is because sequelize 2.0.0 + is not backward compatibile but I can't figure out whats wrong...

Possibly unhandled TypeError: Cannot set property postTranslations of [object Object] which has only a getter
  at [object Object].module.exports.Instance._setInclude (/Users/alek/Desktop/wl/node_modules/sequelize/lib/instance.js:394:24)
  at [object Object].module.exports.Instance.set (/Users/alek/Desktop/wl/node_modules/sequelize/lib/instance.js:279:16)
  at [object Object].module.exports.Instance.set (/Users/alek/Desktop/wl/node_modules/sequelize/lib/instance.js:251:20)
  at [object Object].module.exports.initValues (/Users/alek/Desktop/wl/node_modules/sequelize/lib/instance.js:896:10)
  at [object Object].module.exports.Instance (/Users/alek/Desktop/wl/node_modules/sequelize/lib/instance.js:52:16)
  at new Instance.DAO (/Users/alek/Desktop/wl/node_modules/sequelize/lib/model.js:235:16)
  at [object Object].module.exports.Model.build (/Users/alek/Desktop/wl/node_modules/sequelize/lib/model.js:954:12)
  at [object Object].<anonymous> (/Users/alek/Desktop/wl/node_modules/sequelize/lib/model.js:972:19)
  at Array.map (native)
  at [object Object].module.exports.Model.bulkBuild (/Users/alek/Desktop/wl/node_modules/sequelize/lib/model.js:971:22)
  at [object Object].module.exports.handleSelectQuery (/Users/alek/Desktop/wl/node_modules/sequelize/lib/dialects/abstract/query.js:244:28)
  at [object Object].module.exports.AbstractQuery.send (/Users/alek/Desktop/wl/node_modules/sequelize/lib/dialects/abstract/query.js:117:26)
  at /Users/alek/Desktop/wl/node_modules/sequelize/lib/dialects/postgres/query.js:140:23
  at tryCatchApply (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/util.js:83:19)
  at Promise$_callSpread [as _callSpread] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/promise.js:709:12)
  at Promise$_callHandler [as _callHandler] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/promise.js:717:18)
  at Promise$_settlePromiseFromHandler [as _settlePromiseFromHandler] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/promise.js:737:18)
  at Promise$_settlePromiseAt [as _settlePromiseAt] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/promise.js:899:14)
  at Promise$_settlePromises [as _settlePromises] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/promise.js:1037:14)
  at Promise.b (domain.js:183:18)
  at Async$_consumeFunctionBuffer [as _consumeFunctionBuffer] (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/async.js:74:12)
  at Async$consumeFunctionBuffer (/Users/alek/Desktop/wl/node_modules/sequelize/node_modules/sequelize-bluebird/js/main/async.js:37:14)

[alekbarszczewski]

I solved the unhandledException error - it was caused because I manually set up getter called postTranslations...

Anyway I gave up trying to force Sequelize to parse results correctly - it seems that many "things" happen in Model.findAll including calls to functions that are not exported by the module (so it's not possible to reach them outside sequelize source)...

I thought about parsing results myself and tried sequelize.query(q,null,{ raw: true }).done(..) - unfortunately sequelize returns very strange result - first row is parsed correctly and others not... whats going on?

{
  "items": [
    {
      "Posts": {
        "id": 1
      },
      "PostTranslations": {
        "id": 2,
        "language": "zh-cn",
        "PostId": 1
      }
    },
    {
      "Posts.id": 1,
      "PostTranslations.id": 1,
      "PostTranslations.language": "en-us",
      "PostTranslations.PostId": 1
    },
    {
      "Posts.id": 2,
      "PostTranslations.id": 3,
      "PostTranslations.language": "en-us",
      "PostTranslations.PostId": 2
    },
    {
      "Posts.id": 3,
      "PostTranslations.id": 4,
      "PostTranslations.language": "zh-cn",
      "PostTranslations.PostId": 3
    },
    {
      "Posts.id": 4,
      "PostTranslations.id": 5,
      "PostTranslations.language": "en-us",
      "PostTranslations.PostId": 4
    },
    {
      "Posts.id": 5,
      "PostTranslations.id": 6,
      "PostTranslations.language": "en-us",
      "PostTranslations.PostId": 5
    },
    {
      "Posts.id": 6,
      "PostTranslations.id": 7,
      "PostTranslations.language": "en-us",
      "PostTranslations.PostId": 6
    }
  ]
}

[mickhansen]

Looks like dottie.transform is being applied to the first row (although it should probably be applied to all rows or none at all). We don't really have tests or official support for the type of thing you want, raw queries are just that, raw.

[olive75]

This latter point (regarding dotties) was reported in sequelize/sequelize#2061 and appears to be solved by commit 1e577d3.

I have the same problem, anything new on this?

[mickhansen]

@olive75 is it not working with that commit?

[mickhansen]

The commit is only in 2.0.0-dev13 and up, so still an issue on 1.7

[juliocanares]

what is the final solution for this ? ,

var options = { include: etc }; Sequelize.Model.validateIncludedElements(options);

Not work for me , I'm using -> sequelize ^2.0.2

[mickhansen]

@juliocanares There is no official one, it's on the roadmap.

[juliocanares]

@mickhansen then I need format manually the data ?

[we4sz]

How is this going?

Im fetching movies from my database and join in genres and actors which gives like 100 rows per movie, how can i create one object with a list of genres and actors instead

[xb1itz]

+1 here Do anybody figured out a solution for this?

[abelbarbosa]

Any news about this topic?

[rbudiharso]

+1 Any update on this?

[ratheeshart]

@mickhansen is there any classMethods to parse that "dotties"?

[jharting]

Here's what I am using to get Sequelize 3 to build model objects from my raw query:

    const query = "SELECT ....";
    const options = {
        model: sql.topic,
        include: [{
            model: sql.rule,
            include: [{
                model: sql.plugin
            }]
        }]
    };

    sql.Sequelize.Model.$validateIncludedElements(options);
    options.hasJoin = true;

    sql.sequelize.query(query, options);

HTH

[felixfbecker]

@jharting Please note that $validateIncludedElements is private API (and has been renamed in v4).

[jharting]

@felixfbecker I see. Is making it public on the roadmap?