Skip to content

Commit 0943339

Browse files
floydspaceVictor KorzuninKeimenosdepold
authored
fix(select): do not force set subQuery to false (#13490)
* fix(select): do not force set subQuery to false this occurred on validating included elements in spite of user set it to true in include options * test: make include (subQuery alias) tests dry Co-authored-by: Victor Korzunin <victor.korzunin@blanco.services> Co-authored-by: Constantin Metz <58604248+Keimeno@users.noreply.github.com> Co-authored-by: Sascha Depold <sdepold@users.noreply.github.com>
1 parent 31d0fbc commit 0943339

2 files changed

Lines changed: 96 additions & 19 deletions

File tree

‎lib/model.js‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -528,7 +528,7 @@ class Model {
528528

529529
if (include.subQuery !== false && options.hasDuplicating && options.topLimit) {
530530
if (include.duplicating) {
531-
include.subQuery = false;
531+
include.subQuery = include.subQuery || false;
532532
include.subQueryFilter = include.hasRequired;
533533
} else {
534534
include.subQuery = include.hasRequired;
@@ -538,7 +538,6 @@ class Model {
538538
include.subQuery = include.subQuery || false;
539539
if (include.duplicating) {
540540
include.subQueryFilter = include.subQuery;
541-
include.subQuery = false;
542541
} else {
543542
include.subQueryFilter = false;
544543
include.subQuery = include.subQuery || include.hasParentRequired && include.hasRequired && !include.separate;

‎test/unit/sql/select.test.js‎

Lines changed: 95 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -449,7 +449,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
449449
});
450450
});
451451

452-
it('include (subQuery alias)', () => {
452+
describe('include (subQuery alias)', () => {
453453
const User = Support.sequelize.define('User', {
454454
name: DataTypes.STRING,
455455
age: DataTypes.INTEGER
@@ -466,29 +466,107 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
466466

467467
User.Posts = User.hasMany(Post, { foreignKey: 'user_id', as: 'postaliasname' });
468468

469-
expectsql(sql.selectQuery('User', {
470-
table: User.getTableName(),
471-
model: User,
472-
attributes: ['name', 'age'],
469+
it('w/o filters', () => {
470+
expectsql(sql.selectQuery('User', {
471+
table: User.getTableName(),
472+
model: User,
473+
attributes: ['name', 'age'],
474+
include: Model._validateIncludedElements({
475+
include: [{
476+
attributes: ['title'],
477+
association: User.Posts,
478+
subQuery: true,
479+
required: true
480+
}],
481+
as: 'User'
482+
}).include,
483+
subQuery: true
484+
}, User), {
485+
default: 'SELECT [User].* FROM ' +
486+
'(SELECT [User].[name], [User].[age], [User].[id] AS [id], [postaliasname].[id] AS [postaliasname.id], [postaliasname].[title] AS [postaliasname.title] FROM [User] AS [User] ' +
487+
'INNER JOIN [Post] AS [postaliasname] ON [User].[id] = [postaliasname].[user_id] ' +
488+
`WHERE ( SELECT [user_id] FROM [Post] AS [postaliasname] WHERE ([postaliasname].[user_id] = [User].[id])${sql.addLimitAndOffset({ limit: 1, tableAs: 'postaliasname' }, User)} ) IS NOT NULL) AS [User];`
489+
});
490+
});
491+
492+
it('w/ nested column filter', () => {
493+
expectsql(sql.selectQuery('User', {
494+
table: User.getTableName(),
495+
model: User,
496+
attributes: ['name', 'age'],
497+
where: { '$postaliasname.title$': 'test' },
498+
include: Model._validateIncludedElements({
499+
include: [{
500+
attributes: ['title'],
501+
association: User.Posts,
502+
subQuery: true,
503+
required: true
504+
}],
505+
as: 'User'
506+
}).include,
507+
subQuery: true
508+
}, User), {
509+
default: 'SELECT [User].* FROM ' +
510+
'(SELECT [User].[name], [User].[age], [User].[id] AS [id], [postaliasname].[id] AS [postaliasname.id], [postaliasname].[title] AS [postaliasname.title] FROM [User] AS [User] ' +
511+
'INNER JOIN [Post] AS [postaliasname] ON [User].[id] = [postaliasname].[user_id] ' +
512+
`WHERE [postaliasname].[title] = ${sql.escape('test')} AND ( SELECT [user_id] FROM [Post] AS [postaliasname] WHERE ([postaliasname].[user_id] = [User].[id])${sql.addLimitAndOffset({ limit: 1, tableAs: 'postaliasname' }, User)} ) IS NOT NULL) AS [User];`
513+
});
514+
});
515+
});
516+
517+
it('include w/ subQuery + nested filter + paging', () => {
518+
const User = Support.sequelize.define('User', {
519+
scopeId: DataTypes.INTEGER
520+
});
521+
522+
const Company = Support.sequelize.define('Company', {
523+
name: DataTypes.STRING,
524+
public: DataTypes.BOOLEAN,
525+
scopeId: DataTypes.INTEGER
526+
});
527+
528+
const Profession = Support.sequelize.define('Profession', {
529+
name: DataTypes.STRING,
530+
scopeId: DataTypes.INTEGER
531+
});
532+
533+
User.Company = User.belongsTo(Company, { foreignKey: 'companyId' });
534+
User.Profession = User.belongsTo(Profession, { foreignKey: 'professionId' });
535+
Company.Users = Company.hasMany(User, { as: 'Users', foreignKey: 'companyId' });
536+
Profession.Users = Profession.hasMany(User, { as: 'Users', foreignKey: 'professionId' });
537+
538+
expectsql(sql.selectQuery('Company', {
539+
table: Company.getTableName(),
540+
model: Company,
541+
attributes: ['name', 'public'],
542+
where: { '$Users.Profession.name$': 'test', [Op.and]: { scopeId: [42] } },
473543
include: Model._validateIncludedElements({
474544
include: [{
475-
attributes: ['title'],
476-
association: User.Posts,
545+
association: Company.Users,
546+
attributes: [],
547+
include: [{
548+
association: User.Profession,
549+
attributes: [],
550+
required: true
551+
}],
477552
subQuery: true,
478553
required: true
479554
}],
480-
as: 'User'
555+
model: Company
481556
}).include,
557+
limit: 5,
558+
offset: 0,
482559
subQuery: true
483-
}, User), {
484-
default: 'SELECT [User].*, [postaliasname].[id] AS [postaliasname.id], [postaliasname].[title] AS [postaliasname.title] FROM ' +
485-
'(SELECT [User].[name], [User].[age], [User].[id] AS [id] FROM [User] AS [User] ' +
486-
'WHERE ( SELECT [user_id] FROM [Post] AS [postaliasname] WHERE ([postaliasname].[user_id] = [User].[id]) LIMIT 1 ) IS NOT NULL) AS [User] ' +
487-
'INNER JOIN [Post] AS [postaliasname] ON [User].[id] = [postaliasname].[user_id];',
488-
mssql: 'SELECT [User].*, [postaliasname].[id] AS [postaliasname.id], [postaliasname].[title] AS [postaliasname.title] FROM ' +
489-
'(SELECT [User].[name], [User].[age], [User].[id] AS [id] FROM [User] AS [User] ' +
490-
'WHERE ( SELECT [user_id] FROM [Post] AS [postaliasname] WHERE ([postaliasname].[user_id] = [User].[id]) ORDER BY [postaliasname].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY ) IS NOT NULL) AS [User] ' +
491-
'INNER JOIN [Post] AS [postaliasname] ON [User].[id] = [postaliasname].[user_id];'
560+
}, Company), {
561+
default: 'SELECT [Company].* FROM (' +
562+
'SELECT [Company].[name], [Company].[public], [Company].[id] AS [id] FROM [Company] AS [Company] ' +
563+
'INNER JOIN [Users] AS [Users] ON [Company].[id] = [Users].[companyId] ' +
564+
'INNER JOIN [Professions] AS [Users->Profession] ON [Users].[professionId] = [Users->Profession].[id] ' +
565+
`WHERE ([Company].[scopeId] IN (42)) AND [Users->Profession].[name] = ${sql.escape('test')} AND ( ` +
566+
'SELECT [Users].[companyId] FROM [Users] AS [Users] ' +
567+
'INNER JOIN [Professions] AS [Profession] ON [Users].[professionId] = [Profession].[id] ' +
568+
`WHERE ([Users].[companyId] = [Company].[id])${sql.addLimitAndOffset({ limit: 1, tableAs: 'Users' }, User)} ` +
569+
`) IS NOT NULL${sql.addLimitAndOffset({ limit: 5, offset: 0, tableAs: 'Company' }, Company)}) AS [Company];`
492570
});
493571
});
494572

0 commit comments

Comments
 (0)