sql - 如何在 include 中插入 Distinct 和 Order？

Question

我正在 nodejs (express 和 angular) 中做一个项目，我发现这个 sequeelize 有困难。我无法添加不同的(在查询开始时，我收到太多副本)并且我无法为“包含字段”添加订单

我浏览了 Sequielize 文档( http://docs.sequelizejs.com/manual/querying.html#operators-aliases )但没有成功。我在这里找到了一些答案，但仍然没有成功。我究竟做错了什么？

    return models.Inspection.findAndCountAll({
        distinct: true,
        attributes: [],
        include: [{model: models.InspectionGroup, attributes:['ID', 'InspectionTypeModel_ID', 'Notes', 'Date'], 
                            required: true, include: [{
                                model: models.InspectionTypeModel, attributes: ['Model'], required: true
                            }]},
                    {model: models.UnitElement, attributes: [], required: true, include: [{
                        model: models.Span, attributes: [], required: true, include: [{
                            model: models.StructuralUnit, attributes: [], required: true, include: [{
                                model: models.Building, attributes: [], required: true, include: [{
                                    model: models.Direction, attributes: [], required: true, include: [{
                                        model: models.Road, attributes: [], where: {Dealer_ID: dealerList}, required: true
                                    }]
                                }]
                            }]
                        }]
                    }]
                }],
    order: [[ { model: models.InspectionGroup }, 'Date', 'DESC']],
    limit: limit,
    offset: offset
    })
    .then(function (results) {
        res.json(results);
    })
    .catch(function (err) {
        commons.showerror(err, res);
    });

这是生成的sql:

SELECT [Inspection].[ID], [InspectionGroup].[ID] AS [InspectionGroup.ID], [InspectionGroup].[InspectionTypeModel_ID] AS [InspectionGroup.InspectionTypeModel_ID], 
    [InspectionGroup].[Notes] AS [InspectionGroup.Notes], [InspectionGroup].[Date] AS [InspectionGroup.Date], [InspectionGroup.InspectionTypeModel].[ID] AS [InspectionGroup.InspectionTypeModel.ID], 
    [InspectionGroup.InspectionTypeModel].[Model] AS [InspectionGroup.InspectionTypeModel.Model] 
FROM [Inspection] AS [Inspection] 
INNER JOIN [InspectionGroup] AS [InspectionGroup] ON [Inspection].[InspectionGroup_ID] = [InspectionGroup].[ID] 
INNER JOIN [InspectionTypeModel] AS [InspectionGroup.InspectionTypeModel] ON [InspectionGroup].[InspectionTypeModel_ID] = [InspectionGroup.InspectionTypeModel].[ID] 
INNER JOIN [UnitElement] AS [UnitElement] ON [Inspection].[UnitElement_ID] = [UnitElement].[ID] INNER JOIN [Span] AS [UnitElement.Span] ON [UnitElement].[Span_ID] = [UnitElement.Span].[ID] 
INNER JOIN [StructuralUnit] AS [UnitElement.Span.StructuralUnit] ON [UnitElement.Span].[StructuralUnit_ID] = [UnitElement.Span.StructuralUnit].[ID] INNER JOIN [Building] AS [UnitElement.Span.StructuralUnit.Building] ON [UnitElement.Span.StructuralUnit].[Building_ID] = [UnitElement.Span.StructuralUnit.Building].[ID] 
INNER JOIN [Direction] AS [UnitElement.Span.StructuralUnit.Building.Direction] ON [UnitElement.Span.StructuralUnit.Building].[Direction_ID] = [UnitElement.Span.StructuralUnit.Building.Direction].[ID] INNER JOIN [Road] AS [UnitElement.Span.StructuralUnit.Building.Direction.Road] ON [UnitElement.Span.StructuralUnit.Building.Direction].[Road_ID] = [UnitElement.Span.StructuralUnit.Building.Direction.Road].[ID] AND [UnitElement.Span.StructuralUnit.Building.Direction.Road].[Dealer_ID] IN (4, 12) 
ORDER BY [InspectionGroup].[Date] DESC ORDER BY [ID] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

这是错误
FETCH 语句中选项 NEXT 的使用无效。

我可以看到 sequielize 生成了 2 个不同的 order by，但我实际上不知道为什么它会生成“ID order by”，考虑到我指定 order by 应该在 InspectionGroup.Date 上......和“distinct: true”被完全忽略。

编辑(生成带有属性的 SQL:[[Sequelize.fn('distinct', Sequelize.col('ID')), 'inspectionId']])
和错误:“FETCH 语句中选项 NEXT 的使用无效。”

SELECT [Inspection].[ID], distinct([ID]) AS [inspectionId], [InspectionGroup].[ID] AS [InspectionGroup.ID], [InspectionGroup].[InspectionTypeModel_ID] AS [InspectionGroup.InspectionTypeModel_ID], 
    [InspectionGroup].[Notes] AS [InspectionGroup.Notes], [InspectionGroup].[Date] AS [InspectionGroup.Date], [InspectionGroup.InspectionTypeModel].[ID] AS [InspectionGroup.InspectionTypeModel.ID], 
    [InspectionGroup.InspectionTypeModel].[Model] AS [InspectionGroup.InspectionTypeModel.Model]
    FROM [Inspection] AS [Inspection] 
    INNER JOIN [InspectionGroup] AS [InspectionGroup] ON [Inspection].        [InspectionGroup_ID] = [InspectionGroup].[ID] 
    INNER JOIN [InspectionTypeModel] AS [InspectionGroup.InspectionTypeModel] ON [InspectionGroup].[InspectionTypeModel_ID] = [InspectionGroup.InspectionTypeModel].[ID] 
    INNER JOIN [UnitElement] AS [UnitElement] ON [Inspection].[UnitElement_ID] = [UnitElement].[ID] INNER JOIN [Span] AS [UnitElement.Span] ON [UnitElement].[Span_ID] = [UnitElement.Span].[ID] 
    INNER JOIN [StructuralUnit] AS [UnitElement.Span.StructuralUnit] ON [UnitElement.Span].[StructuralUnit_ID] = [UnitElement.Span.StructuralUnit].[ID] INNER JOIN [Building] AS [UnitElement.Span.StructuralUnit.Building] ON [UnitElement.Span.StructuralUnit].[Building_ID] = [UnitElement.Span.StructuralUnit.Building].[ID] 
    INNER JOIN [Direction] AS [UnitElement.Span.StructuralUnit.Building.Direction] ON [UnitElement.Span.StructuralUnit.Building].[Direction_ID] = [UnitElement.Span.StructuralUnit.Building.Direction].[ID] 
    INNER JOIN [Road] AS [UnitElement.Span.StructuralUnit.Building.Direction.Road] ON [UnitElement.Span.StructuralUnit.Building.Direction].[Road_ID] = [UnitElement.Span.StructuralUnit.Building.Direction.Road].[ID] AND [UnitElement.Span.StructuralUnit.Building.Direction.Road].[Dealer_ID] IN (4, 12) 
    ORDER BY [Inspection].[Date] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Answer 1

如果我没有错，您应该在路由模型上至少放置一个属性，以便能够在该列上应用不同的属性。否则，没有列可以应用它。

让我们试试看它是否有效

distinct: true,
attributes: [´id’]

希望它会帮助你

[编辑]

通过以下更新应该没问题

return models.Inspection.findAndCountAll({
    attributes: [[ Sequelize.fn('DISTINCT', Sequelize.col('Inspection.ID')), 'Inspection.ID']],
    include: [{model: models.InspectionGroup, attributes:['ID', 'InspectionTypeModel_ID', 'Notes', 'Date'], 
                        required: true, include: [{
                            model: models.InspectionTypeModel, attributes: ['Model'], required: true
                        }]},
                {model: models.UnitElement, attributes: [], required: true, include: [{
                    model: models.Span, attributes: [], required: true, include: [{
                        model: models.StructuralUnit, attributes: [], required: true, include: [{
                            model: models.Building, attributes: [], required: true, include: [{
                                model: models.Direction, attributes: [], required: true, include: [{
                                    model: models.Road, attributes: [], where: {Dealer_ID: dealerList}, required: true
                                }]
                            }]
                        }]
                    }]
                }]
            }],
order: [[ { model: models.InspectionGroup }, 'Date', 'DESC']],
limit: limit,
offset: offset
})
.then(function (results) {
    res.json(results);
})
.catch(function (err) {
    commons.showerror(err, res);
});