How does group by works in sequelize?
issue: https://github.com/sequelize/sequelize/issues/348
User.findAll({
group: ['field']
})
i use sequelize@2.0.0-dev9
Group By with Sequelize
I solved it as follows:
async index(req, res) {
const { page = 1 } = req.query;
const champions = await Champion.findAll({
limit: 5,
offset: (page - 1) * 5,
order: [[fn('count', col('cupselection.selection.id')), 'DESC']],
attributes: [[fn('count', col('cupselection.selection.id')), 'vezes']],
group: ['cupselection.selection.id', 'cupselection.selection.logo.id'],
raw: true,
include: [
{
model: CupSelection,
as: 'cupselection',
attributes: [],
include: [
{
model: Selection,
as: 'selection',
attributes: ['id', 'country'],
include: [
{
model: File,
as: 'logo',
attributes: ['id', 'path', 'url'],
},
],
},
],
},
],
});
return res.json(champions);
}
Using group by and joins in sequelize
This issue has been fixed on Sequelize 3.0.1, the primary key of the included models must be excluded with
attributes: []
and the aggregation must be done on the main model (infos in this github issue).
Thus for my use case, the code is the following
models.contracts.findAll({
attributes: ['id', [models.sequelize.fn('sum', models.sequelize.col('payments.payment_amount')), 'total_cost']],
include: [
{
model: models.payments,
attributes: []
}
],
group: ['contracts.id']
})
Sequelize group by with association includes id
So this is not possible. The ORM needs a relation towards the database and this is done through the id; making it impossible to get this without the id.
What you can do, however, is do a raw query with sequelize.
i.e.
return models.sequelize.query(
`SELECT
[WorkingCalendar].[id],
[WorkingCalendar].[PeriodId],
[WorkingCalendar].[date],
[Period].[name]
FROM [WorkingCalendars] AS [WorkingCalendar]
LEFT OUTER JOIN [Periods] AS [Period] ON [WorkingCalendar].[PeriodId] = [Period].[id]
WHERE [WorkingCalendar].[GetSudoId] = :getsudoId AND [WorkingCalendar].[UnitPlantId] = N'1'
GROUP BY [WorkingCalendar].[PeriodId], [WorkingCalendar].[date], [Period].[name]`,
{
replacements: { getsudoId: getsudoId },
type: models.Sequelize.QueryTypes.SELECT
}
)
Hope this helps anyone struggling too.
Related Topics
Get the Value of Datepicker on Change in Jquery
Angular:How to Add/Remove Files in the Angular
How to Put a Variable in an Src Attribute
Calculate String Value in Javascript, Not Using Eval
Getting Selected Values from Dropdown Inside Table
Select Drop Down Text-Align Center in Safari
How to Access a Model Attribute With JavaScript Variable
How to Add Disable Option or Default Option in Select Box
How to Prevent Browser to Re-Set Inputs on Page Refresh
Is There Any Workaround (Or Hack) to Make the Audio Autoplay on Chrome, Firefox and Safari
How to Wrap Each Word of an Element in a Span Tag
Automatically Refreshing Page Every 60 Seconds Using JavaScript
Get Index on Click of a Mapped Array in React Js
I Want to Change the Page Background Color, But It Doesn't Work as I Expect in Chrome
Form Builder - Cannot Read Property 'Get' of Undefined, Issue With Validators