连接表上的 Sequelize 条件不适用于限制条件 [英] Sequelize condition on joined table doesn't work with limit condition
问题描述
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屋！
