使用 NodeJS 进行续集无法连接有限制的表 [英] Sequelize with NodeJS can't join tables with limit

问题描述

我正在尝试实现一个应该如下所示的简单查询:

I'm trying to implement a simple query that should look like this:

select * from property join entity_area on property.id=entity_area.entity_id and entity_area.area_id=1 where property.price>300000 limit 12

非常简单:我想获得连接结果，然后限制为 12 个.

Pretty straightforward: I want to get the joined result and then to limit to 12.

在 Sequelize 中，我使用以下函数:

In Sequelize i'm using the following function:

return models.property.findAll(
{
    where: ["price>=?", 300000],
    include: [
    {
        model:models.entity_area,
        where: { area_id:1 }
    }
    ],
    limit:12
})

但是这段代码会生成如下的sql:

But this code generates the following sql:

select property.*, entity_area.* from (select * from property where property.price>300000 limit 12) join entity_area on property.id=entity_area.entity_id and entity_area.area_id=1

这与我正在尝试做的逻辑完全不同，因为在生成的 sql 中它首先得到任何 12 个结果，然后尝试与 entity_area 连接，当然随机的 12 个结果不一定与 entity_area 匹配，所以我没有得到任何结果.

Which has totally different logic from what i'm trying to do because in the generated sql it first gets any 12 results and then tries to join with entity_area, and of course the random 12 results don't necessarily match the entity_area, so i'm getting no results back.

请给我一个正确的方法.属性表非常庞大，我必须使用限制"而不是获取所有结果并在 javascript 中对它们进行切片.我也不想开始使用原始查询.

Please suggest me a proper way of doing it. The property table is very massive, and i have to use the "limit" rather than getting all the results and slicing them in javascript. Also i wouldn't like to start using raw queries.

推荐答案

其实我自己也找到了解决方案.我认为这是sequelize框架中的一个错误.
在 node_modules/sequelize/lib/dialect/abstract/query_generator.js 中有一个selectQuery"函数，它具有以下行:

Actually I found a solution myself. I think this is a bug in sequelize framework.
In the node_modules/sequelize/lib/dialect/abstract/query_generator.js there is a "selectQuery" function which has the following line:

subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation) && options.subQuery !== false

首先有一个选项 subQuery 可以作为 false 传递以删除子查询生成.Sequelize 文档对此只字未提.但是此外，如果您在 findAll 对象中传递 subQuery:false ，它将无法工作，因为由于某种原因，它对 selectQuery 函数的定义不够完善.
我试过类似的东西:

First of all there is an option subQuery that could be passed as false to remove the subquery generation. Sequelize documentation does not have a word about it. But moreover if you pass subQuery:false in the findAll object it's not going to work because for some reason it's getting as underfined to the selectQuery function.
I tried something like:

return models.property.findAll(
{
    where: ["price>=?", 300000],
    include: [
    {
        model:models.entity_area,
        where: { area_id:1 }
    }
    ],
    limit:12,
    subQuery:false
})

仍然得到 options.subQuery=undefined.

and still got options.subQuery=undefined.

所以我不得不将 query_generator.js 中的函数更改为:

So i had to change the function in query_generator.js to be something like:

subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation) && options.subQuery !== false && options.doSubQuery===true

所以现在默认情况下它不会执行这个丑陋的子查询，除非我明确指定 doSubQuery:true.最后我得到了正确的查询，没有带限制的子查询.

So now by default it's not doing this ugly subquery unless i specify explicitely doSubQuery:true. And finally i got the proper query without subquery with limit.

这篇关于使用 NodeJS 进行续集无法连接有限制的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！