What does include[].duplicating: true does in sequelize?

Question

I'm looking at Sequelize documentation for findAll, and I'm having difficulty understanding what setting duplicating: true does. The documentation says:

Mark the include as duplicating, will prevent a subquery from being used.

I am setting it on one of the includes I have on my model and see no difference in the results, but since I don't understand what this option does, I'm not sure what I'm supposed to be looking for.

My query looks like this:

Sticker.findAndCountAll({
    where: { accountId: query.accountId },
    attributes: ['id', 'title', 'updatedAt'],
    distinct: true,
    include: [
        {
            model: Folder,
            attributes: ['id', 'name'],
            duplicating: true,
            include: [
                { model: Sticker, attributes: ['id'] },
                { model: User, attributes: ['id', 'name', 'email'] }
            ]
        },
        { model: User, attributes: ['id', 'name', 'email'] }
    ],
    offset,
    order,
    limit
})

I'm only setting it on the Folder include.

Say I have 10 Stickers, 5 don't have a Folder, 2 belong to one Folder, and 3 to another Folder.

I am trying to get Stickers that may or may not be part of a Folder, but if they do belong to a Folder, I only want one "representative" from that Folder, so for the 10 Stickers in my DB, I want only 7 results, 5 for the free Stickers, and 1 for each Folder. By the way, I am including the Stickers again in the Folder include to count them later.

I thought that duplicating: true could help me achieve this, but I can't understand what it does.

Can someone explain to me how this option translates into the queries? Is this to avoid doing extra queries on my associations? If so, should I set it on all associations? Is it possible to achieve the results I want?

user_1335287 · Answer

I could not find out how duplicating: true works. However, I did find how to list elements the way I wanted. I just needed to add another model to associate them:

AccountContent.belongsTo('Sticker')
AccountContent.belongsTo('Folder')

That way I can include both Stickers and Folders as associations of AccountContent in the same query.

user_10205754 · Answer

duplicating: false will not add subQuery of the include in the WHERE CLAUSE. EXAMPLE with not setting duplicating:

SELECT [correspondence].*, [histories].[ID] AS [histories.id], [histories].[CorrespondenceID] AS [histories.correspondenceId], [histories].[UserID] AS [histories.userId], [histories].[Text] AS [histories.text], [histories].[CreatedDTS] AS [histories.createdDate], [histories].[TaskID] AS [histories.taskId], [histories->user].[ID] AS [histories.user.id], [histories->user].[DisplayName] AS [histories.user.displayName], [histories->user->structureEntity].[ID] AS [histories.user.structureEntity.id], [histories->user->structureEntity].[Name] AS [histories.user.structureEntity.name], [comments].[ID] AS [comments.id], [comments].[Body] AS [comments.body], [contacts->organization].[ID] AS [contacts.organization.id], [contacts->organization].[Name] AS [contacts.organization.name], [contacts->employee].[ID] AS [contacts.employee.id], [contacts->employee].[Name] AS [contacts.employee.name], [propertyValues].[Id] AS [propertyValues.id], [propertyValues].[Value] AS [propertyValues.value], [propertyValues].[CorrespondencePropertyId] AS [propertyValues.correspondencePropertyId], [propertyValues->property].[CorrespondenceTypeId] AS [propertyValues.property.correspondenceTypeId], [propertyValues->property].[Id] AS [propertyValues.property.id], [propertyValues->property].[Name] AS [propertyValues.property.name], [tags].[ID] AS [tags.id], [tags].[Name] AS [tags.name], [tags->correspondenceTag].[CorrespondenceID] AS [tags.correspondenceTag.correspondenceId], [tags->correspondenceTag].[TagID] AS [tags.correspondenceTag.tagId], [tags->correspondenceTag].[Notes] AS [tags.correspondenceTag.notes], [participators].[Id] AS [participators.id], [participators].[CorrespondenceId] AS [participators.correspondenceId], [participators].[UserId] AS [participators.userId], [participators].[EntityId] AS [participators.entityId], [participators->entity].[ID] AS [participators.entity.id], [participators->entity].[Name] AS [participators.entity.name], [participators->user].[ID] AS [participators.user.id], [participators->user].[DisplayName] AS [participators.user.displayName] FROM (SELECT [correspondence].[ID] AS [id], [correspondence].[SubjectName] AS [subjectName], [correspondence].[Subject] AS [subject], [correspondence].[StatusID] AS [statusId], [correspondence].[CreatedDTS] AS [createdDate], [correspondence].[ReferenceNumber] AS [referenceNumber], [correspondence].[TypeID] AS [typeId], [correspondence].[PriorityID] AS [priorityId], [contacts].[ID] AS [contacts.id], [contacts].[CorrespondenceId] AS [contacts.correspondenceId], [contacts].[OrganizationId] AS [contacts.organizationId], [contacts].[ContactEmployeeId] AS [contacts.contactEmployeeId], [contacts].[CreatedDTS] AS [contacts.createdDate] FROM [Correspondences] AS [correspondence] INNER JOIN [CorrespondenceContact] AS [contacts] ON [correspondence].[ID] = [contacts].[CorrespondenceId] AND [contacts].[OrganizationId] = N'2144' WHERE ([correspondence].[IsDeleted] = 0 AND [correspondence].[IsArchived] = 0 AND [correspondence].[Published] = 1 AND [correspondence].[TypeID] = 1) AND ( SELECT [CorrespondenceId] FROM [CorrespondencePropertyValue] AS [propertyValues] WHERE ([propertyValues].[Value] = N'SOMR VALUE' AND [propertyValues].[CorrespondenceId] = [correspondence].[ID]) ORDER BY [propertyValues].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY ) IS NOT NULL ORDER BY [correspondence].[CreatedDTS] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY) AS [correspondence] LEFT OUTER JOIN [CorrespondenceHistory] AS [histories] ON [correspondence].[id] = [histories].[CorrespondenceID] LEFT OUTER JOIN [Users] AS [histories->user] ON [histories].[UserID] = [histories->user].[ID] LEFT OUTER JOIN [LKEntityStucture] AS [histories->user->structureEntity] ON [histories->user].[EntityID] = [histories->user->structureEntity].[ID] LEFT OUTER JOIN [CorrespondenceComments] AS [comments] ON [correspondence].[id] = [comments].[CorrespondenceId] AND [comments].[Type] = N'initialComment' LEFT OUTER JOIN [ContactOrganizations] AS [contacts->organization] ON [contacts.organizationId] = [contacts->organization].[ID] LEFT OUTER JOIN [ContactEmployees] AS [contacts->employee] ON [contacts.contactEmployeeId] = [contacts->employee].[ID] INNER JOIN [CorrespondencePropertyValue] AS [propertyValues] ON [correspondence].[id] = [propertyValues].[CorrespondenceId] AND [propertyValues].[Value] = N'SOMR VALUE' LEFT OUTER JOIN [CorrespondenceProperty] AS [propertyValues->property] ON [propertyValues].[CorrespondencePropertyId] = [propertyValues->property].[Id] LEFT OUTER JOIN ( [CorrespondenceTagLookup] AS [tags->correspondenceTag] INNER JOIN [Tags] AS [tags] ON [tags].[ID] = [tags->correspondenceTag].[TagID]) ON [correspondence].[id] = [tags->correspondenceTag].[CorrespondenceID] LEFT OUTER JOIN [CorrespondenceParticipator] AS [participators] ON [correspondence].[id] = [participators].[CorrespondenceId] LEFT OUTER JOIN [LKEntityStucture] AS [participators->entity] ON [participators].[EntityId] = [participators->entity].[ID] LEFT OUTER JOIN [Users] AS [participators->user] ON [participators].[UserId] = [participators->user].[ID] ORDER BY [createdDate] DESC`

see this line AND (SELECT [CorrespondenceId] FROM [CorrespondencePropertyValue] AS [propertyValues] WHERE ( [propertyValues].[Value] = N'SOME VALUE' AND [propertyValues].[CorrespondenceId] = [correspondence].[ID]) ORDER BY [propertyValues].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) IS NOT NULL Will be deleted if you added duplicating: false to the include options. to be like this:

SELECT [correspondence].*, [histories].[ID] AS [histories.id], [histories].[CorrespondenceID] AS [histories.correspondenceId], [histories].[UserID] AS [histories.userId], [histories].[Text] AS [histories.text], [histories].[CreatedDTS] AS [histories.createdDate], [histories].[TaskID] AS [histories.taskId], [histories->user].[ID] AS [histories.user.id], [histories->user].[DisplayName] AS [histories.user.displayName], [histories->user->structureEntity].[ID] AS [histories.user.structureEntity.id], [histories->user->structureEntity].[Name] AS [histories.user.structureEntity.name], [comments].[ID] AS [comments.id], [comments].[Body] AS [comments.body], [contacts->organization].[ID] AS [contacts.organization.id], [contacts->organization].[Name] AS [contacts.organization.name], [contacts->employee].[ID] AS [contacts.employee.id], [contacts->employee].[Name] AS [contacts.employee.name], [propertyValues->property].[CorrespondenceTypeId] AS [propertyValues.property.correspondenceTypeId], [propertyValues->property].[Id] AS [propertyValues.property.id], [propertyValues->property].[Name] AS [propertyValues.property.name], [tags].[ID] AS [tags.id], [tags].[Name] AS [tags.name], [tags->correspondenceTag].[CorrespondenceID] AS [tags.correspondenceTag.correspondenceId], [tags->correspondenceTag].[TagID] AS [tags.correspondenceTag.tagId], [tags->correspondenceTag].[Notes] AS [tags.correspondenceTag.notes], [participators].[Id] AS [participators.id], [participators].[CorrespondenceId] AS [participators.correspondenceId], [participators].[UserId] AS [participators.userId], [participators].[EntityId] AS [participators.entityId], [participators->entity].[ID] AS [participators.entity.id], [participators->entity].[Name] AS [participators.entity.name], [participators->user].[ID] AS [participators.user.id], [participators->user].[DisplayName] AS [participators.user.displayName] FROM (SELECT [correspondence].[ID] AS [id], [correspondence].[SubjectName] AS [subjectName], [correspondence].[Subject] AS [subject], [correspondence].[StatusID] AS [statusId], [correspondence].[CreatedDTS] AS [createdDate], [correspondence].[ReferenceNumber] AS [referenceNumber], [correspondence].[TypeID] AS [typeId], [correspondence].[PriorityID] AS [priorityId], [contacts].[ID] AS [contacts.id], [contacts].[CorrespondenceId] AS [contacts.correspondenceId], [contacts].[OrganizationId] AS [contacts.organizationId], [contacts].[ContactEmployeeId] AS [contacts.contactEmployeeId], [contacts].[CreatedDTS] AS [contacts.createdDate], [propertyValues].[Id] AS [propertyValues.id], [propertyValues].[Value] AS [propertyValues.value], [propertyValues].[CorrespondencePropertyId] AS [propertyValues.correspondencePropertyId] FROM [Correspondences] AS [correspondence] INNER JOIN [CorrespondenceContact] AS [contacts] ON [correspondence].[ID] = [contacts].[CorrespondenceId] AND [contacts].[OrganizationId] = 2144 INNER JOIN [CorrespondencePropertyValue] AS [propertyValues] ON [correspondence].[ID] = [propertyValues].[CorrespondenceId] AND [propertyValues].[Value] = N'سري للغاية' WHERE ([correspondence].[IsDeleted] = 0 AND [correspondence].[IsArchived] = 0 AND [correspondence].[Published] = 1 AND [correspondence].[TypeID] = 1) ORDER BY [correspondence].[CreatedDTS] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY) AS [correspondence] LEFT OUTER JOIN [CorrespondenceHistory] AS [histories] ON [correspondence].[id] = [histories].[CorrespondenceID] LEFT OUTER JOIN [Users] AS [histories->user] ON [histories].[UserID] = [histories->user].[ID] LEFT OUTER JOIN [LKEntityStucture] AS [histories->user->structureEntity] ON [histories->user].[EntityID] = [histories->user->structureEntity].[ID] LEFT OUTER JOIN [CorrespondenceComments] AS [comments] ON [correspondence].[id] = [comments].[CorrespondenceId] AND [comments].[Type] = N'initialComment' LEFT OUTER JOIN [ContactOrganizations] AS [contacts->organization] ON [contacts.organizationId] = [contacts->organization].[ID] LEFT OUTER JOIN [ContactEmployees] AS [contacts->employee] ON [contacts.contactEmployeeId] = [contacts->employee].[ID] LEFT OUTER JOIN [CorrespondenceProperty] AS [propertyValues->property] ON [propertyValues.correspondencePropertyId] = [propertyValues->property].[Id] LEFT OUTER JOIN ( [CorrespondenceTagLookup] AS [tags->correspondenceTag] INNER JOIN [Tags] AS [tags] ON [tags].[ID] = [tags->correspondenceTag].[TagID]) ON [correspondence].[id] = [tags->correspondenceTag].[CorrespondenceID] LEFT OUTER JOIN [CorrespondenceParticipator] AS [participators] ON [correspondence].[id] = [participators].[CorrespondenceId] LEFT OUTER JOIN [LKEntityStucture] AS [participators->entity] ON [participators].[EntityId] = [participators->entity].[ID] LEFT OUTER JOIN [Users] AS [participators->user] ON [participators].[UserId] = [participators->user].[ID] ORDER BY [createdDate] DESC;

What does include[].duplicating: true does in sequelize?

2 Answers