CODEWITHSUNDEEP
mysqlsequelize.js
robin14dev
robin14dev

Reputation: 41

how to grouping and sorting by period in mysql

I want to group and sort data in mysql.

itemName startDate(Int) endDate(Int)
item1 20221101 20221110
item2 20221101 20221120
item3 20221101 20221231
item4 20221201 20221231
item5 20221220 20231231
item6 20230101 20231231

I want to sort data like 'in progress => planned => overdue' by today

for example,

if today is 2022-11-11, I expect the sort would be item2 => item3 => item4 => item5 => item6 => item1(overdue)

If today is 2022-12-01, I expect the sort would be item3 => item4 => item5 => item6 => item1(overdue) => item2(overdue)

If today is 2023-01-01, I expect the sort would be item6(in progress) => item1 => item2 => item3 => item4 => item5

Do I have to use Group by? I don't know how to sorting these data.

please let me know how to groupby and sorting in mysql or sequelize.js

I just use item in progress below. but I want to add endItem, PlannedItem

  let items = await Items.findAll({
        where: {
          endDate: { [Op.gte]: date },
          startDate: { [Op.lte]: date },
        },

       })

I thought that in progress item is startDate <= today <= endDate, planned item is today < startDate, overdue item is endDate < today

and after making 3 groups, sorting... but I don't know how to solve it..

Upvotes: 0

Views: 58

Answers (1)

ysth
ysth

Reputation: 98398

It sounds like you want:

order by
    case
        when itemName like 'progress%' then 1
        when itemName like 'planned%' then 2
        when itemName like 'end%' then 3
    end,
    0+regexp_substr(itemName, '\\d+$')

I think in sequelize you'd need to put all that in an order: sequelize.literal(...)

group by something you only use when combining multiple rows (or combinations of joined rows) into fewer result rows, which doesn't sound like the case here.

Upvotes: 2

Related Questions