How to insert a Distinct and Order in include?

18
May 15, 2019, at 11:50 PM

i'm doing a project in nodejs (express and angular) and im finding difficulties with this sequielize. I cannot add a distinct (at the beginning of the query, i receive too much copies) and i cannot add an order by for a "include field"

I went through Sequielize documentation ( http://docs.sequelizejs.com/manual/querying.html#operators-aliases ) but without success. I found some answers in here but still no success. What am I doing wrong?

    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);
    });

This is the sql generated:

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

And this is the error
Invalid usage of the option NEXT in the FETCH statement.

I can see that the sequielize generated 2 different order by, but I actually don't know why it generates the "ID order by", considering i specified that the order by should be on InspectionGroup.Date...and the "distinct:true" is totally ignored.

Edit (generated SQL with attributes: [[Sequelize.fn('distinct', Sequelize.col('ID')), 'inspectionId']]) And error: "Invalid usage of the option NEXT in the FETCH statement."

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

If i am not wrong, you should put at least one attribute on the route model to be able to apply distinct on that column. Otherwise, there is no column to apply it.

Let’s try and see if it works

distinct: true,
attributes: [´id’]

Hope it will help you

[EDIT]

With the following update it should be fine

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);
});
READ ALSO
multiple sockets on one client using angular

multiple sockets on one client using angular

I'm not even sure if this is possibleIf not, please point me in a different direction

26
Meaning in nodejs/postgreSQL : query.query2+='DELETE FROM public.category_qb_map WHERE qb_id=${id};';

Meaning in nodejs/postgreSQL : query.query2+='DELETE FROM public.category_qb_map WHERE qb_id=${id};';

I am writing an api in nodejs, in which some data are taken from the body of the request and insert, update and delete operations are performedI am having difficulty in understanding some portions of the code

28
Get a variable from a file to another file in node.js( problem not solve )

Get a variable from a file to another file in node.js( problem not solve )

I tried too many time but I couldn't a get solution to my problemmy file structure is like following:

13
Why is my express code not rendering css and javascipt files?

Why is my express code not rendering css and javascipt files?

I am trying to write a basic express server codeHowever, the HTML file that gets served on a particular route does not render the static files (css, js and image files)

30