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.

I don't think so. Eager loading may be the closest thing. [Here's an example where Sequelize generates a sub-query via eager loading, because of an artificial `limit`](https://github.com/sequelize/sequelize/issues/1719). — bishop, Dec 08 '14 at 21:27
I don't know anything about `Sequelize.js`. Is it possible to compose something like `EssayStats t1 LEFT JOIN EssayStats t2 ON t1.EssayId = t2.EssayId AND t1.EssayDate < t2.EssayDate` using its ORM? If the answer is `YES` then you can write the query without subqueries and without `GROUP BY`. You need to join the table to itself three times though, once with `INNER JOIN` and two times with `LEFT JOIN`. — axiac, Dec 15 '14 at 16:09

score 6 · Accepted Answer · edited May 23 '17 at 12:08

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.

score 4 · Answer 2 · answered Jan 11 '18 at 08:17

4

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

answered Jan 11 '18 at 08:17

Derit Agustin

718
2
9
11

Hi @Derit, here I want some alias name for Model A, Model B join output and again join that output with model C. Is there any way to do it. Ex: select uid,col1,col2,col3 from( select A.id as uid,col1,col2,col3 from A inner join B in A.id = B.mid where somecondition ) as 't' inner join users as u on t.uid = u.id – DRK Mar 05 '18 at 12:42

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

2 Answers2

Linked