联接表上的sequelize条件不适用于限制条件 [英] Sequelize condition on joined table doesn't work with limit condition
本文介绍了联接表上的sequelize条件不适用于限制条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个供应商模型和关联的日历模型。
我想获取供应商
- 具有已设置为可用日历的日历
- 没有日历
我可以使用以下方法进行操作：
Supplier.findAll（{
包括：[
{
模型：日历，
如： calendars，
必需：false，
其中：{
start_time：{[Op.lte]：date}，
end_time：{[Op.gte]：date}，
} ，
}，
]，
其中：{
'$ calendars.state $'：{
[Op.or]：[
{[ Op.in]：['available']}，
{[Op.eq]：null}，
]，
}，
}，
}） ;
这将生成以下SQL（删除了不相关的列）：
SELECT
供应商。 uuid
，日历。 uuid AS calendars.uuid
calendars。 state AS calendars.state
来自 suppliers AS suppliers
LEFT OUTER JOIN suppliers_calendars AS calendars ON
suppliers。 uuid = calendars。 supplier_id
AND calendars。 start_time< ='2019-05-27 23：00：00.000 +00：00'
AND calendars。 end_time > ='2019-05-27 23：00：00.000 +00：00'
WHERE（
（ calendars。请注明 IN（'available'）
OR calendars。 state为NULL
）
）
ORDER BY供应商。 uuid
;
很酷。现在，如果我添加限额会发生什么？即
Supplier.findAll（{
包括：[
{
模型：日历，
as：'calendars'，必需的
：false，
其中：{
start_time：{[Op.lte]：date}，
end_time：{[Op .gte]：date}，
}，
}，
]，
其中：{
'$ calendars.state $'：{
[ Op.or]：[
{[Op.in]：['available']}，
{[Op.eq]：null}，
]，
}，
}，
上限：10，
}）；
这会产生以下结果：
SELECT
供应商。*
， calendars。 uuid AS calendars.uuid
， calendars。状态 AS calendars.state
FROM（
选择供应商。 uuid
FROM供应商作为供应商
在哪里（
（日历。状态 IN（可用）
或日历。状态为NULL）
）
由供应商订购。 uuid
限制10
）AS供应商
左外联接 suppliers_calendars AS $ $日历上的 suppliers_calendars。 uuid =日历。 supplier_id
和 calendars。 start_time< ='2019-05-27 23：00：00.000 +00：00'
AND calendars。 end_time> ='2019-05-27 23：00：00.000 +00： 00'
ORDER BY供应商。 uuid
这是一个完全不同的查询，主要部分放在子查询中，联接放在外面。但是在联接发生之前，联接表上的 where 条件放在子查询中，因此失败。
这里正确的方法是什么？
解决方案
经过大约一周的地狱之后，我的情况才可以接受。相信它会有所帮助，因为在github上发现了许多未答复的主题/问题。
TL; DR;实际的解决方案是在最后，只是最后一段代码。
主要思想是Sequelize构建了正确的SQL查询，但是当您离开时加入后，我们会产生笛卡尔乘积，因此查询结果将有很多行。
示例：A和B表。多对多关系。如果我们想让所有的A与B相连，我们将收到A * B行，因此A的每条记录都会有很多行，而B的值却不同。
<如果不存在，则创建表（
id整数主键不为空，
标题VARCHAR
）
如果不存在则创建表b（
id整数主键不为空，
age INTEGER
）
如果不存在则创建表ab（
id整数主键不为空，
辅助整数，
投标整数
）
选择*
从
左联接（ab JOIN b ON b.id = ab.bid）ON a.id = ab.aid
用后续语法：
A类扩展模型{}
A.init（{
id：{
类型：Sequelize.INTEGER，
自动递增：true，
primaryKey：true，
}，
标题：{
type：Sequelize.STRING，
}，
}）；
B类扩展模型{}
B.init（{
id：{
type：Sequelize.INTEGER，
autoIncrement：true，
primaryKey：true，
}，
年龄：{
type：Sequelize.INTEGER，
}，
}）；
A.belongsToMany（B，{foreignKey：'aid'，otherKey：'bid'，as：'ab'}）;
B.belongsToMany（A，{foreignKey：'bid'，otherKey：'aid'，as：'ab'}）;
A.findAll（{
different：true，
include：[{association：'ab'}]，
}）
一切正常。
所以，想象一下我想接收10条记录$ a $ b从A映射到B的记录。
在此查询上加上LIMIT 10时，Sequelize构建正确的查询，但LIMIT应用于整个查询，结果我们只收到10行，而所有行可能都是例如：
A.findAll（{
different：true，
include ：[{association：'ab'}]，
限制：10，
}）
这将被转换为：
SELECT *
从
左联接（ab联接b在b.id = ab.bid上）ON a.id = ab.aid
限制10
id |标题| id |援助|出价| id |年龄
--- | -------- | ----- | ----- | ----- | ----- | -----
1 |第一| 1 | 1 | 1 | 1 | 1
1 |第一| 2 | 1 | 2 | 2 | 2
1 |第一| 3 | 1 | 3 | 3 | 3
1 |第一| 4 | 1 | 4 | 4 | 4
1 |第一| 5 | 1 | 5 | 5 | 5
2 |第二| 6 | 2 | 5 | 5 | 5
2 |第二| 7 | 2 | 4 | 4 | 4
2 |第二| 8 | 2 | 3 | 3 | 3
2 |第二| 9 | 2 | 2 | 2 | 2
2 |第二| 10 | 2 | 1 | 1 | 1
接收到输出后，Seruqlize作为ORM将进行数据映射，并且代码中的过度查询结果将为：
[
{
id：1，
标题：'first'，
ab：[
{id：1，age：1}，
{id：2，age：2}，
{id：3，age：3}，
{id：4，age：4}，
{id：5，age：5}，
]，
}，
{
id：2 ，
标题：'second'，
ab：[
{id：5，age：5}，
{id：4，age：4}，
{id：3，age：3}，
{id：2，age：2}，
{id：1，age：1}，
]，
}
]
显然不是我们想要的。我想收到10条关于A的记录，但只收到2条记录，而我知道数据库中还有更多记录。
所以我们有正确的SQL查询，但仍然收到不正确的结果。
好吧，我有一些想法，但最简单，最合乎逻辑的是：
1.进行第一个请求连接，并按源表对结果分组（表我们要查询的内容以及要加入的内容）的 id属性。似乎很容易.....
要做到这一点，我们需要提供 group属性以对查询选项进行排序。在这里，我们有一些问题。首先-Sequelize在生成SQL查询时为每个表创建别名。其次-Sequelize将JOINED表中的所有列放入其查询的SELECT语句中，并且传递__′attributes′= [] __不会有帮助。在这两种情况下，我们都会收到SQL错误。
首先要解决这个问题，我们需要将Model.tableName转换为该单词的单数形式（此逻辑基于Sequelize）。只需使用[pluralize.singular（）]（https://www.npmjs.com/package/pluralize#usage）。然后将正确的属性组合为GROUP BY：
```ts
const tableAlias = duplexize.singular（'Industries'）//行业
{
... ，
组：[`$ {tableAlias} .id`]
}
```
解决第二个问题（这是最困难也是最困难的.. （未记录）。我们需要使用未记录的属性‘includeIgnoreAttributes’= false。除非我们手动指定，否则这将从SELECT语句中删除所有列。我们应该在根查询中手动指定属性= ['id']。
- 现在我们将仅在必要时正确接收输出资源ID。然后，我们需要构成没有限制和偏移量的seconf查询，而是指定其他'where'子句：
{
...，
其中：{
...，
id：Sequelize.Op.in：[ID数组]，
}
}
- 查询有关我们可以
解决方案
方法接收模型和原始查询作为参数并返回正确的查询+数据库中用于分页的记录总数。它还可以正确解析查询顺序，以提供按联接表中的字段排序的功能：
/ **
*解决方法用于在使用LEFT JOINS查询并具有LIMIT / OFFSET
*
*时不合理的行为进行归类，这里我们按主（源）模型的'id'属性分组，使用未记录的'includeIgnoreAttributes'
*序列化道具（在其静态count（）方法中使用）以获取正确的SQL请求
*包括'includeIgnoreAttributes'的用法SELECT语句
中有很多额外的无效列*
*不带'includeIgnoreAttributes'的错误示例。在这里，我们将获得正确的SQL查询
*但根据业务逻辑无用：
*
* SELECT Media。 id， Solutions-> MediaSolutions。 mediaId， Industries-> MediaIndustries。 mediaId，...，
*来自 Medias，如 Media
*左加入...
*位置...
* GROUP BY Media。 id
* ORDER BY ...
* LIMIT ...
* OFFSET ...
*
*正确的示例包括'includeIgnoreAttributes'：
*
*选择 Media。 id
* FROM Medias AS Media
*左联接...
*位置...
*按媒体。 id
组*按...排序
*限制...
*抵消...
*
* @param模型-源模型（获取GROUP BY选项的tableName所必需）
* @param查询-解析并准备使用查询对象
* /
私人异步fixSequeliseQueryWithLeftJoins< C扩展Model>（
模型：ModelCtor< C> ;，查询：FindAnd CountOptions，
）：IMsgPromise< {查询：FindAndCountOptions;总计？：数字}> {
const fixedQuery：FindAndCountOptions = {... query};
//如果只有租户数据加入->如果（query.include&& query.include.length === 1&&（query.include [0] as IncludeOptions）.model === Tenant），则返回原始查询
{
返回msg.ok（{查询：fixedQuery}）;
}
//这里需要将其设为单数形式，
//因为Sequelize对于SQL查询中的模型AS别名获得单数形式
const modelAlias =单数（model.tableName）;
const firstQuery = {
... fixedQuery，
组：[`$ {modelAlias} .id`]，
属性：['id']，
raw：true，
includeIgnoreAttributes：false，
logging：true，
}；
//通过联接表列进行排序-通过联接数据进行排序时，需要将其添加到组
中，如果（Array.isArray（firstQuery.order））{
firstQuery。 order.forEach（（item）=> {
if（（item as GenericObject）.length === 2）{
firstQuery.group.push（`$ {modelAlias}。$ {（item as GenericObject）[0]}`）;
} else if（（item as GenericObject）.length === 3）{
firstQuery.group.push（`$ {（item as GenericObject）[ 0]}。$ {（item as GenericObject）[1]}`）;
}
}）;
}
返回model.findAndCountAll< C>（firstQuery）
.then（（ids）=> {
if（ids&& ids。行&& ids.rows.length）{
fixedQuery.where = {
... fixedQuery.where，
id：{
[Op.in]：ids .rows.map（（item：GenericObject）=> item.id），
}，
};
删除fixedQuery.limit;
删除fixedQuery.offset;
}
/ * eslint-disable-next-line * /
const total =（ids.count为任意值）.length || ids.count;
return msg.ok（{query：fixedQuery，total}）;
}）
.catch（（err）=> this.createCustomError（err））;
}
I have a Supplier model with associated Calendar models.
I want to fetch suppliers who either
- have a calendar which is set to available
- don't have a calendar
I can do this using the following:
Supplier.findAll({
include: [
{
model: Calendar,
as: 'calendars',
required: false,
where: {
start_time: { [Op.lte]: date },
end_time: { [Op.gte]: date },
},
},
],
where: {
'$calendars.state$': {
[Op.or]: [
{ [Op.in]: ['available'] },
{ [Op.eq]: null },
],
},
},
});
This generates the following SQL (irrelevant columns removed):
SELECT
"suppliers"."uuid"
,"calendars"."uuid" AS "calendars.uuid"
,"calendars"."state" AS "calendars.state"
FROM "suppliers" AS "suppliers"
LEFT OUTER JOIN "suppliers_calendars" AS "calendars" ON
"suppliers"."uuid" = "calendars"."supplier_id"
AND "calendars"."start_time" <= '2019-05-27 23:00:00.000 +00:00'
AND "calendars"."end_time" >= '2019-05-27 23:00:00.000 +00:00'
WHERE (
("calendars"."state" IN ('available')
OR "calendars"."state" IS NULL
)
)
ORDER BY "suppliers"."uuid"
;
Cool, as expected. Now what happens if I add a limit? I.e.
Supplier.findAll({
include: [
{
model: Calendar,
as: 'calendars',
required: false,
where: {
start_time: { [Op.lte]: date },
end_time: { [Op.gte]: date },
},
},
],
where: {
'$calendars.state$': {
[Op.or]: [
{ [Op.in]: ['available'] },
{ [Op.eq]: null },
],
},
},
limit: 10,
});
This produces the following:
SELECT
"suppliers".*
,"calendars"."uuid" AS "calendars.uuid"
,"calendars"."state" AS "calendars.state"
FROM (
SELECT "suppliers"."uuid"
FROM "suppliers" AS "suppliers"
WHERE (
("calendars"."state" IN ('available')
OR "calendars"."state" IS NULL)
)
ORDER BY "suppliers"."uuid"
LIMIT 10
) AS "suppliers"
LEFT OUTER JOIN "suppliers_calendars" AS "calendars" ON
"suppliers"."uuid" = "calendars"."supplier_id"
AND "calendars"."start_time" <= '2019-05-27 23:00:00.000 +00:00'
AND "calendars"."end_time" >= '2019-05-27 23:00:00.000 +00:00'
ORDER BY "suppliers"."uuid"
This is a completely different query, with the main part put into a subquery and the join placed outside. But the where condition on the joined table is put inside the subquery, before the join has occurred, and so fails.
What is the correct approach here?
解决方案
After about a week of hell found acceptable workaround for my case. Believe it would be helpful as found a lot of unanswered topics/issues on github.
TL;DR; actual solution is at the end of post, just the last piece of code.
The main idea is that Sequelize builds correct SQL query, but when having left joins we produce carthesian product, so there will be a lot of rows as query result.
Example: A and B tables. Many to many relation. If we want to get all A joined with B we will receive A * B rows, so there will be a lot of rows for each record from A with different values from B.
CREATE TABLE IF NOT EXISTS a (
id INTEGER PRIMARY KEY NOT NULL,
title VARCHAR
)
CREATE TABLE IF NOT EXISTS b (
id INTEGER PRIMARY KEY NOT NULL,
age INTEGER
)
CREATE TABLE IF NOT EXISTS ab (
id INTEGER PRIMARY KEY NOT NULL,
aid INTEGER,
bid INTEGER
)
SELECT *
FROM a
LEFT JOIN (ab JOIN b ON b.id = ab.bid) ON a.id = ab.aid
In sequelize syntax:
class A extends Model {}
A.init({
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true,
},
title: {
type: Sequelize.STRING,
},
});
class B extends Model {}
B.init({
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true,
},
age: {
type: Sequelize.INTEGER,
},
});
A.belongsToMany(B, { foreignKey: ‘aid’, otherKey: ‘bid’, as: ‘ab’ });
B.belongsToMany(A, { foreignKey: ‘bid’, otherKey: ‘aid’, as: ‘ab’ });
A.findAll({
distinct: true,
include: [{ association: ‘ab’ }],
})
Everything works ok.
So, imagine i want to receive 10 records from A with mapped to them records from B. When we put LIMIT 10 on this query, Sequelize build correct query but LIMIT is applied to whole query and as result we receive only 10 rows , where all of them could be for only one record from A. Example:
A.findAll({
distinct: true,
include: [{ association: ‘ab’ }],
limit: 10,
})
Which will be converted into:
SELECT *
FROM a
LEFT JOIN (ab JOIN b ON b.id = ab.bid) ON a.id = ab.aid
LIMIT 10
id | title | id | aid | bid | id | age
--- | -------- | ----- | ----- | ----- | ----- | -----
1 | first | 1 | 1 | 1 | 1 | 1
1 | first | 2 | 1 | 2 | 2 | 2
1 | first | 3 | 1 | 3 | 3 | 3
1 | first | 4 | 1 | 4 | 4 | 4
1 | first | 5 | 1 | 5 | 5 | 5
2 | second | 6 | 2 | 5 | 5 | 5
2 | second | 7 | 2 | 4 | 4 | 4
2 | second | 8 | 2 | 3 | 3 | 3
2 | second | 9 | 2 | 2 | 2 | 2
2 | second | 10 | 2 | 1 | 1 | 1
After output is received, Seruqlize as ORM will make data mapping and over query result in code will be:
[
{
id: 1,
title: 'first',
ab: [
{ id: 1, age:1 },
{ id: 2, age:2 },
{ id: 3, age:3 },
{ id: 4, age:4 },
{ id: 5, age:5 },
],
},
{
id: 2,
title: 'second',
ab: [
{ id: 5, age:5 },
{ id: 4, age:4 },
{ id: 3, age:3 },
{ id: 2, age:2 },
{ id: 1, age:1 },
],
}
]
Obviously NOT what we wanted. I wanted to receive 10 records for A, but received just 2, while i know that there are more in database.
So we have correct SQL query but still received incorrect result.
Ok, i had some ideas but the easiest and most logical is: 1. Make first request with joins, and group results by source table (table on which we are making query and to which making joins) 'id' property. Seems easy.....
To make so we need to provide 'group' property to Sequelize query options. Here we have some problems. First - Sequelize makes aliases for each table while generating SQL query. Second - Sequelize puts all columns from JOINED table into SELECT statement of its query and passing __'attributes' = []__ won't help. In both cases we'll receive SQL error.
To solve first we need to convert Model.tableName to singluar form of this word (this logic is based on Sequelize). Just use [pluralize.singular()](https://www.npmjs.com/package/pluralize#usage). Then compose correct property to GROUP BY:
```ts
const tableAlias = pluralize.singular('Industries') // Industry
{
...,
group: [`${tableAlias}.id`]
}
```
To solve second (it was the hardest and the most ... undocumented). We need to use undocumented property 'includeIgnoreAttributes' = false. This will remove all columns from SELECT statement unless we specify some manually. We should manually specify attributes = ['id'] on root query.
- Now we will receive correctly output with only necessary resources ids. Then we need to compose seconf query WITHOUT limit and offset, but specify additional 'where' clause:
{
...,
where: {
...,
id: Sequelize.Op.in: [array of ids],
}
}
- With query about we can produce correct query with LEFT JOINS.
Solution Method receives model and original query as arguments and returns correct query + additionally total amount of records in DB for pagination. It also correctly parse query order to provide ability to order by fields from joined tables:
/**
* Workaround for Sequelize illogical behavior when querying with LEFT JOINS and having LIMIT / OFFSET
*
* Here we group by 'id' prop of main (source) model, abd using undocumented 'includeIgnoreAttributes'
* Sequelize prop (it is used in its static count() method) in order to get correct SQL request
* Witout usage of 'includeIgnoreAttributes' there are a lot of extra invalid columns in SELECT statement
*
* Incorrect example without 'includeIgnoreAttributes'. Here we will get correct SQL query
* BUT useless according to business logic:
*
* SELECT "Media"."id", "Solutions->MediaSolutions"."mediaId", "Industries->MediaIndustries"."mediaId",...,
* FROM "Medias" AS "Media"
* LEFT JOIN ...
* WHERE ...
* GROUP BY "Media"."id"
* ORDER BY ...
* LIMIT ...
* OFFSET ...
*
* Correct example with 'includeIgnoreAttributes':
*
* SELECT "Media"."id"
* FROM "Medias" AS "Media"
* LEFT JOIN ...
* WHERE ...
* GROUP BY "Media"."id"
* ORDER BY ...
* LIMIT ...
* OFFSET ...
*
* @param model - Source model (necessary for getting its tableName for GROUP BY option)
* @param query - Parsed and ready to use query object
*/
private async fixSequeliseQueryWithLeftJoins<C extends Model>(
model: ModelCtor<C>, query: FindAndCountOptions,
): IMsgPromise<{ query: FindAndCountOptions; total?: number }> {
const fixedQuery: FindAndCountOptions = { ...query };
// If there is only Tenant data joined -> return original query
if (query.include && query.include.length === 1 && (query.include[0] as IncludeOptions).model === Tenant) {
return msg.ok({ query: fixedQuery });
}
// Here we need to put it to singular form,
// because Sequelize gets singular form for models AS aliases in SQL query
const modelAlias = singular(model.tableName);
const firstQuery = {
...fixedQuery,
group: [`${modelAlias}.id`],
attributes: ['id'],
raw: true,
includeIgnoreAttributes: false,
logging: true,
};
// Ordering by joined table column - when ordering by joined data need to add it into the group
if (Array.isArray(firstQuery.order)) {
firstQuery.order.forEach((item) => {
if ((item as GenericObject).length === 2) {
firstQuery.group.push(`${modelAlias}.${(item as GenericObject)[0]}`);
} else if ((item as GenericObject).length === 3) {
firstQuery.group.push(`${(item as GenericObject)[0]}.${(item as GenericObject)[1]}`);
}
});
}
return model.findAndCountAll<C>(firstQuery)
.then((ids) => {
if (ids && ids.rows && ids.rows.length) {
fixedQuery.where = {
...fixedQuery.where,
id: {
[Op.in]: ids.rows.map((item: GenericObject) => item.id),
},
};
delete fixedQuery.limit;
delete fixedQuery.offset;
}
/* eslint-disable-next-line */
const total = (ids.count as any).length || ids.count;
return msg.ok({ query: fixedQuery, total });
})
.catch((err) => this.createCustomError(err));
}
这篇关于联接表上的sequelize条件不适用于限制条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
