Includes in raw querys

[Eric Thompson]

Is there a way to tell Sequelize that I would like to get the nested behavior of "belongsTo" in the output of a raw query result?

In my example I have a simple findAll query that includes the Attribute table: Item.findAll({include: models.Attribute}).  Giving this result:

[

   {

      "Id": 1,

      "AttributeId": 1,

      "Value": "(IA) FIRST VALUE",

      "createdAt": "2015-10-30T22:40:00.603Z",

      "updatedAt": "2015-10-30T22:40:00.603Z",

      "Attribute": {

         "Id": 1,

         "Definition": "{\"A\": true, \"B\": false}",

         "backward": 4,

         "forward": null,

         "createdAt": "2015-10-30 22:39:59.883 +00:00",

         "updatedAt": "2015-10-30 22:40:00.179 +00:00"

      }

   }

]

I have a raw query that will return Items and I would like their Attribute model filled.  Is there a way to coxe Seqelize into giving the same nested behavior in a raw query given this example?

models.sequelize.query("....", {type: models.sequelize.QueryTypes.SELECT, model: models.Item, include: models.Attribute})

[

   {

      "Id": 1,

      "AttributeId": 1,

      "Value": "(IA) FIRST VALUE",

      "createdAt": "2015-10-30T22:40:00.603Z",

      "updatedAt": "2015-10-30T22:40:00.603Z"

      --- MISSING ATTRIBUTE MODEL ---

   }

]

I suspect its a matter of joining in the correct information and naming it correctly?

[Mick Hansen]

It should be doable, although slightly convuluted.
You'll need a few things: formatting the includes to what is expected, triggering the right code path and joins that map to the correct namings.

https://github.com/sequelize/sequelize/blob/master/lib/model.js#L432
https://github.com/sequelize/sequelize/blob/master/lib/dialects/abstract/query.js#L583

--

Mick Hansen

@mhansendev
mhansen.io

[Eric Thompson]

Interesting.  So if I am reading this correctly, I should just be able to write the same JOIN instruction that would have be generated by Sequelize and if "include: models.Attribute" in in the options sent to the raw query then Seqelize will sort it out for me and produce the nested object?

[Mick Hansen]

You'll definitely need to call Model.validateIncludeOptions on the options set.
I'm not sure exactly what you'll need to do, probably have to play with it a little :)

[Eric Thompson]

OK.  On a related note, is there a way to get the JOIN string that Sequelize would have generated or do I need to piece that together by hand for my raw query?

[Eric Thompson]

I was able to get the desired behavior by writing the same JOIN statement Sequelize would have and massaging the options object to include "hasJoin: true" and then having validateIncludeElements act on it before passing it to the raw query call.

var options = {type: sequelize.QueryTypes.SELECT, model: Item, include: [ {model: Attribute} ], hasJoin: true };

options = sequelize.Model.$validateIncludedElements(options, {InstantiatedAttribute: true});

sequelize.query(q, options).then(...

Thanks for your help on this, Mick.

On more question before closing this out... is there a way to get the JOIN string that Sequelize would have generated or do I need to piece that together by hand for my raw query?

[Mick Hansen]

For 1:1 and 1:M you can use: https://github.com/sequelize/sequelize/blob/master/lib/dialects/abstract/query-generator.js#L1565
N:M has not yet been converted to use that method for joins though.

[Eric Thompson]

Is that exposed though the sequelize object anywhere?

[Mick Hansen]

`sequelize.dialect.QueryGenerator` should have it, maybe `sequelize.dialect.QueryInterface.QueryGenerator`, not exactly public API :)

[Eric Thompson]

sequelize.dialect.QueryGenerator.joinIncludeQuery does the trick.  How do I mark this as answered? :)

[Mick Hansen]

Google groups is not necessarily Q/A, so not possible :)

[Eric Thompson]

Naw, I found it.  "Mark this as best answer".  It shows up in the list with an "Answered" tag now.

[Mick Hansen]

Oh okay :) I answer these messages from my gmail client heh.