Is it possible to do a subquery with Sequelize.js?

Question

I have a query that looks like:

select es.EssayId, (esmax.WordCount - esmin.WordCount)
from (select es.EssayId, min(es.EssayDate) as mined, max(es.EssayDate) as maxed
      from EssayStats es
      group by es.EssayId
     ) es join
     EssayStats esmin
     on es.EssayId = esmin.EssayId and es.mined = esmin.EssayDate join
     EssayStats esmax
     on es.EssayId = esmax.EssayId and es.maxed = esmax.EssayDate;

Is it possible to write this with Sequelize.js ORM? I know I can just use a query directly, but I'm wondering if it's possible to construct.

Answer 1

I don't think a clean answer to your question is possible. See #1869:

Querying on the through model/join table is not possible currently unfortuneatly.

To answer the title question, Sequelize will automatically generate a subquery (eg, #1719), but you can't do a custom subquery. I don't have an authoritative reference for a negative.

---

It looks like your table is something like this:

EssayStats
    EssayId
    EssayDate
    WordCount

Then you could do something like this:

return EssayStat.findAll({
    attributes: [
        [sequelize.literal('((SELECT wordCount FROM "EssayStats" WHERE "EssayId" = "EssayStat"."EssayId" EssayStat BY "createdAt" DESC LIMIT 1) - (SELECT wordCount FROM "EssayStats" WHERE "EssayId" = "EssayStat"."EssayId" EssayStat BY "createdAt" ASC LIMIT 1))'), 'difference'],
        'EssayId'
    ],
    group: ['EssayId']
});

All that it is doing is running two SELECT queries, taking the MAX and MIN from those queries after ordering by your variable of interest, and then taking your difference. That will give you what you're interested in: the word count difference between the most recent version and the first version.

The trick here is to encapsulate a SELECT statement in an attribute field.

Of course, it's messy as heck and probably not all that much better than the canned sequelize.query. But it does answer the gist of your question.

A better solution might be to denormalize your data some, and store "wordCountDelta" in your Essay model directly. Then you could have an afterCreate hook to automatically update the field. That would most likely be the fastest solution as well.

I answered something similar here.

Answer 2

an example for subquery

ModelA.findAll({
    where: {
        $or: [
            {'$B.someColumn$' : someCondition},
            {'$C.someOtherColumn$' : someOtherCondition}
        ]
    },
    include: [{
        model: ModelB,
        required: false,  //true or false for required 
        where:{id:$id}

    }, {
        model: ModelC,
        required: false, //true or false for required 
        where:{id:$id}
    }]
}); 

i hope useful :D