是否可以使用Sequelize.js进行子查询? [英] Is it possible to do a subquery with Sequelize.js?

问题描述

我有一个查询，如下所示:

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;

是否可以用Sequelize.js ORM编写此代码?我知道我可以直接使用query，但是我想知道是否可以构造.

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.

推荐答案

我认为不可能为您的问题提供一个清晰的答案.参见#1869 :

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.

要回答标题问题，Sequelize将自动生成一个子查询(例如，#1719 )，但您无法执行自定义子查询.我没有权威性的否定参考.

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']
});

它所做的全部是运行两个SELECT查询，在按您感兴趣的变量进行排序之后，从那些查询中获取MAX和MIN，然后求和.这将给您带来您所感兴趣的:最新版本与第一个版本之间的字数差异.

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.

这里的技巧是将SELECT语句封装在属性字段中.

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

当然，它杂乱无章，可能并不比罐装sequelize.query好得多.但这确实回答了您的问题的要点.

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.

更好的解决方案可能是对您的数据进行非规范化，然后将"wordCountDelta"直接存储在Essay模型中.然后，您可以使用afterCreate 钩子自动更新该字段.那很有可能也是最快的解决方案.

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.

这篇关于是否可以使用Sequelize.js进行子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！