Skip to content

Commit 2949a63

Browse files
ckebosssushantdhiman
ckeboss
authored and
sushantdhiman
committed
feat(model): add options.include[].right option (#11537)
1 parent 7d251bd commit 2949a63

10 files changed

Lines changed: 249 additions & 5 deletions

File tree

‎docs/manual/models-usage.md‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -759,3 +759,49 @@ Include all also supports nested loading:
759759
```js
760760
User.findAll({ include: [{ all: true, nested: true }]});
761761
```
762+
763+
### Use right join for association
764+
765+
By default, associations are loaded using a left join, that is to say it only includes records from the parent table. You can change this behavior to a right join by passing the `right` property, if the dialect you are using supports it. Currenly, `sqlite` *does not* support [right joins](https://www.sqlite.org/omitted.html).
766+
767+
*Note:* `right` is only respected if `required` is false.
768+
769+
```js
770+
User.findAll({
771+
include: [{
772+
model: Tool // will create a left join
773+
}]
774+
});
775+
776+
User.findAll({
777+
include: [{
778+
model: Tool,
779+
right: true // will create a right join
780+
}]
781+
});
782+
783+
User.findAll({
784+
include: [{
785+
model: Tool,
786+
required: true,
787+
right: true // has no effect, will create an inner join
788+
}]
789+
});
790+
791+
User.findAll({
792+
include: [{
793+
model: Tool,
794+
where: { name: { [Op.like]: '%ooth%' } },
795+
right: true // has no effect, will create an inner join
796+
}]
797+
});
798+
799+
User.findAll({
800+
include: [{
801+
model: Tool,
802+
where: { name: { [Op.like]: '%ooth%' } },
803+
required: false
804+
right: true // because we set `required` to false, this will create a right join
805+
}]
806+
});
807+
```

‎lib/dialects/abstract/index.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@ AbstractDialect.prototype.supports = {
1111
'ORDER NULLS': false,
1212
'UNION': true,
1313
'UNION ALL': true,
14+
'RIGHT JOIN': true,
1415

1516
/* does the dialect support returning values for inserted/updated fields */
1617
returnValues: false,

‎lib/dialects/abstract/query-generator.js‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1716,7 +1716,7 @@ class QueryGenerator {
17161716
}
17171717

17181718
return {
1719-
join: include.required ? 'INNER JOIN' : 'LEFT OUTER JOIN',
1719+
join: include.required ? 'INNER JOIN' : include.right && this._dialect.supports['RIGHT JOIN'] ? 'RIGHT OUTER JOIN' : 'LEFT OUTER JOIN',
17201720
body: this.quoteTable(tableRight, asRight),
17211721
condition: joinOn,
17221722
attributes: {
@@ -1750,7 +1750,7 @@ class QueryGenerator {
17501750
const identTarget = association.foreignIdentifierField;
17511751
const attrTarget = association.targetKeyField;
17521752

1753-
const joinType = include.required ? 'INNER JOIN' : 'LEFT OUTER JOIN';
1753+
const joinType = include.required ? 'INNER JOIN' : include.right && this._dialect.supports['RIGHT JOIN'] ? 'RIGHT OUTER JOIN' : 'LEFT OUTER JOIN';
17541754
let joinBody;
17551755
let joinCondition;
17561756
const attributes = {

‎lib/dialects/sqlite/index.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ SqliteDialect.prototype.supports = _.merge(_.cloneDeep(AbstractDialect.prototype
2323
'DEFAULT': false,
2424
'DEFAULT VALUES': true,
2525
'UNION ALL': false,
26+
'RIGHT JOIN': false,
2627
inserts: {
2728
ignoreDuplicates: ' OR IGNORE',
2829
updateOnDuplicate: ' ON CONFLICT DO UPDATE SET'

‎lib/model.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1653,6 +1653,7 @@ class Model {
16531653
* @param {Object} [options.include[].on] Supply your own ON condition for the join.
16541654
* @param {Array<string>} [options.include[].attributes] A list of attributes to select from the child model
16551655
* @param {boolean} [options.include[].required] If true, converts to an inner join, which means that the parent model will only be loaded if it has any matching children. True if `include.where` is set, false otherwise.
1656+
* @param {boolean} [options.include[].right] If true, converts to a right join if dialect support it. Ignored if `include.required` is true.
16561657
* @param {boolean} [options.include[].separate] If true, runs a separate query to fetch the associated instances, only supported for hasMany associations
16571658
* @param {number} [options.include[].limit] Limit the joined rows, only supported with include.separate=true
16581659
* @param {Object} [options.include[].through.where] Filter on the join model for belongsToMany relations

‎test/integration/include.test.js‎

Lines changed: 100 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,8 @@ const chai = require('chai'),
77
Support = require('./support'),
88
DataTypes = require('../../lib/data-types'),
99
_ = require('lodash'),
10-
dialect = Support.getTestDialect();
10+
dialect = Support.getTestDialect(),
11+
current = Support.sequelize;
1112

1213
const sortById = function(a, b) {
1314
return a.id < b.id ? -1 : 1;
@@ -911,6 +912,104 @@ describe(Support.getTestDialectTeaser('Include'), () => {
911912
});
912913
});
913914

915+
describe('right join', () => {
916+
it('should support getting an include with a right join', function() {
917+
const User = this.sequelize.define('user', {
918+
name: DataTypes.STRING
919+
}),
920+
Group = this.sequelize.define('group', {
921+
name: DataTypes.STRING
922+
});
923+
924+
User.hasMany(Group);
925+
Group.belongsTo(User);
926+
927+
return this.sequelize.sync({ force: true }).then(() => {
928+
return Promise.all([
929+
User.create({ name: 'User 1' }),
930+
User.create({ name: 'User 2' }),
931+
User.create({ name: 'User 3' }),
932+
Group.create({ name: 'A Group' })
933+
]);
934+
}).then(() => {
935+
return Group.findAll({
936+
include: [{
937+
model: User,
938+
right: true
939+
}]
940+
});
941+
}).then(groups => {
942+
if (current.dialect.supports['RIGHT JOIN']) {
943+
expect(groups.length).to.equal(3);
944+
} else {
945+
expect(groups.length).to.equal(1);
946+
}
947+
});
948+
});
949+
950+
it('should support getting an include through with a right join', function() {
951+
const User = this.sequelize.define('user', {
952+
name: DataTypes.STRING
953+
}),
954+
Group = this.sequelize.define('group', {
955+
name: DataTypes.STRING
956+
}),
957+
UserGroup = this.sequelize.define('user_group', {
958+
vip: DataTypes.INTEGER
959+
});
960+
961+
User.hasMany(Group);
962+
Group.belongsTo(User);
963+
User.belongsToMany(Group, {
964+
through: UserGroup,
965+
as: 'Clubs',
966+
constraints: false
967+
});
968+
Group.belongsToMany(User, {
969+
through: UserGroup,
970+
as: 'Members',
971+
constraints: false
972+
});
973+
974+
const ctx = {};
975+
return this.sequelize.sync({ force: true }).then(() => {
976+
return Promise.all([
977+
User.create({ name: 'Member 1' }),
978+
User.create({ name: 'Member 2' }),
979+
Group.create({ name: 'Group 1' }),
980+
Group.create({ name: 'Group 2' })
981+
]);
982+
}).then(([member1, member2, group1, group2]) => {
983+
ctx.member1 = member1;
984+
ctx.member2 = member2;
985+
ctx.group1 = group1;
986+
ctx.group2 = group2;
987+
}).then(() => {
988+
return Promise.all([
989+
ctx.group1.addMember(ctx.member1),
990+
ctx.group1.addMember(ctx.member2),
991+
ctx.group2.addMember(ctx.member1)
992+
]);
993+
}).then(() => {
994+
return ctx.group2.destroy();
995+
}).then(() => {
996+
return Group.findAll({
997+
include: [{
998+
model: User,
999+
as: 'Members',
1000+
right: true
1001+
}]
1002+
});
1003+
}).then(groups => {
1004+
if (current.dialect.supports['RIGHT JOIN']) {
1005+
expect(groups.length).to.equal(2);
1006+
} else {
1007+
expect(groups.length).to.equal(1);
1008+
}
1009+
});
1010+
});
1011+
});
1012+
9141013
describe('nested includes', () => {
9151014
beforeEach(function() {
9161015
const Employee = this.sequelize.define('Employee', { 'name': DataTypes.STRING });

‎test/integration/model/count.test.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,7 +53,7 @@ describe(Support.getTestDialectTeaser('Model'), () => {
5353
{ username: 'bar' },
5454
{
5555
username: 'valak',
56-
createdAt: (new Date()).setFullYear(2015)
56+
createdAt: new Date().setFullYear(2015)
5757
}
5858
]).then(() => this.User.count({
5959
attributes: ['createdAt'],

‎test/unit/sql/generateJoin.test.js‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,22 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
172172
}
173173
);
174174

175+
testsql(
176+
'include[0]',
177+
{
178+
model: User,
179+
subQuery: true,
180+
include: [
181+
{
182+
association: User.Company, right: true
183+
}
184+
]
185+
},
186+
{
187+
default: `${current.dialect.supports['RIGHT JOIN'] ? 'RIGHT' : 'LEFT'} OUTER JOIN [company] AS [Company] ON [User].[companyId] = [Company].[id]`
188+
}
189+
);
190+
175191
testsql(
176192
'include[0].include[0]',
177193
{

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

Lines changed: 76 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -159,7 +159,6 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
159159
}) AS [user] ORDER BY [subquery_order_0] ASC;`
160160
});
161161

162-
163162
testsql({
164163
table: User.getTableName(),
165164
model: User,
@@ -374,6 +373,82 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
374373
});
375374
});
376375

376+
it('include (right outer join)', () => {
377+
const User = Support.sequelize.define('User', {
378+
name: DataTypes.STRING,
379+
age: DataTypes.INTEGER
380+
},
381+
{
382+
freezeTableName: true
383+
});
384+
const Post = Support.sequelize.define('Post', {
385+
title: DataTypes.STRING
386+
},
387+
{
388+
freezeTableName: true
389+
});
390+
391+
User.Posts = User.hasMany(Post, { foreignKey: 'user_id' });
392+
393+
expectsql(sql.selectQuery('User', {
394+
attributes: ['name', 'age'],
395+
include: Model._validateIncludedElements({
396+
include: [{
397+
attributes: ['title'],
398+
association: User.Posts,
399+
right: true
400+
}],
401+
model: User
402+
}).include,
403+
model: User
404+
}, User), {
405+
default: `SELECT [User].[name], [User].[age], [Posts].[id] AS [Posts.id], [Posts].[title] AS [Posts.title] FROM [User] AS [User] ${current.dialect.supports['RIGHT JOIN'] ? 'RIGHT' : 'LEFT'} OUTER JOIN [Post] AS [Posts] ON [User].[id] = [Posts].[user_id];`
406+
});
407+
});
408+
409+
it('include through (right outer join)', () => {
410+
const User = Support.sequelize.define('user', {
411+
id: {
412+
type: DataTypes.INTEGER,
413+
primaryKey: true,
414+
autoIncrement: true,
415+
field: 'id_user'
416+
}
417+
});
418+
const Project = Support.sequelize.define('project', {
419+
title: DataTypes.STRING
420+
});
421+
422+
const ProjectUser = Support.sequelize.define('project_user', {
423+
userId: {
424+
type: DataTypes.INTEGER,
425+
field: 'user_id'
426+
},
427+
projectId: {
428+
type: DataTypes.INTEGER,
429+
field: 'project_id'
430+
}
431+
}, { timestamps: false });
432+
433+
User.Projects = User.belongsToMany(Project, { through: ProjectUser });
434+
Project.belongsToMany(User, { through: ProjectUser });
435+
436+
expectsql(sql.selectQuery('User', {
437+
attributes: ['id_user', 'id'],
438+
include: Model._validateIncludedElements({
439+
include: [{
440+
model: Project,
441+
right: true
442+
}],
443+
model: User
444+
}).include,
445+
model: User
446+
}, User), {
447+
default: `SELECT [user].[id_user], [user].[id], [projects].[id] AS [projects.id], [projects].[title] AS [projects.title], [projects].[createdAt] AS [projects.createdAt], [projects].[updatedAt] AS [projects.updatedAt], [projects->project_user].[user_id] AS [projects.project_user.userId], [projects->project_user].[project_id] AS [projects.project_user.projectId] FROM [User] AS [user] ${current.dialect.supports['RIGHT JOIN'] ? 'RIGHT' : 'LEFT'} OUTER JOIN ( [project_users] AS [projects->project_user] INNER JOIN [projects] AS [projects] ON [projects].[id] = [projects->project_user].[project_id]) ON [user].[id_user] = [projects->project_user].[user_id];`,
448+
sqlite: `SELECT \`user\`.\`id_user\`, \`user\`.\`id\`, \`projects\`.\`id\` AS \`projects.id\`, \`projects\`.\`title\` AS \`projects.title\`, \`projects\`.\`createdAt\` AS \`projects.createdAt\`, \`projects\`.\`updatedAt\` AS \`projects.updatedAt\`, \`projects->project_user\`.\`user_id\` AS \`projects.project_user.userId\`, \`projects->project_user\`.\`project_id\` AS \`projects.project_user.projectId\` FROM \`User\` AS \`user\` ${current.dialect.supports['RIGHT JOIN'] ? 'RIGHT' : 'LEFT'} OUTER JOIN \`project_users\` AS \`projects->project_user\` ON \`user\`.\`id_user\` = \`projects->project_user\`.\`user_id\` LEFT OUTER JOIN \`projects\` AS \`projects\` ON \`projects\`.\`id\` = \`projects->project_user\`.\`project_id\`;`
449+
});
450+
});
451+
377452
it('include (subQuery alias)', () => {
378453
const User = Support.sequelize.define('User', {
379454
name: DataTypes.STRING,

‎types/lib/model.d.ts‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -420,6 +420,11 @@ export interface IncludeOptions extends Filterable, Projectable, Paranoid {
420420
*/
421421
required?: boolean;
422422

423+
/**
424+
* If true, converts to a right join if dialect support it. Ignored if `include.required` is true.
425+
*/
426+
right?: boolean;
427+
423428
/**
424429
* Limit include. Only available when setting `separate` to true.
425430
*/

0 commit comments

Comments
 (0)