是否可以使用 Sequelize.js 进行子查询?

2022-01-19 00:00:00 mysql sequelize.js

我的查询看起来像:

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

然后你可以这样做:

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 hook自动更新字段.这很可能也是最快的解决方案.

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.

相关文章