Converting a SQL query to SEQUELIZE-CodePudding

I am trying to wrap my head around creating this sequelize query.

SELECT 
    dayofweek(datum) AS DOW, 
    concat(DAY(datum), '-', MONTH(datum)) AS datum, 
    ochtend, 
    middag, 
    avond 
FROM dbt.menus
    ORDER BY datum ASC LIMIT 5 OFFSET (variable in sequelize code);

I have been able to create 2 separate queries where the 'order by filter' works and 'aliases' work, but not combined.

This is what i currenty have in sequelize

const amount = await Menu.count();
const listOfMenu = await Menu.findAll({ 
    order: [['datum', 'ASC']],
    offset: amount - 5,
    limit: 5,
    attributes: {
        include: [
            [
              Menu.sequelize.fn
              (
                "DATE_FORMAT", 
                Menu.sequelize.col("datum"), 
                "%d-%m "
              ),
              "datum",
            ],
          ]
     }
});

CodePudding user response：

You just need to use fn twice: in order and in attributes options:

const datumExpression = Menu.sequelize.fn
              (
                "DATE_FORMAT", 
                Menu.sequelize.col("datum"), 
                "%d-%m "
              )
const listOfMenu = await Menu.findAll({ 
    order: [[datumExpression, 'ASC']],
    offset: amount - 5,
    limit: 5,
    attributes: {
        include: [
            [
              datumExpression,
              "datum",
            ],
          ]
     }
});

Also you can combine findAll and count queries into one findAndCountAll and you will get the result with two props: rows and count:

const { rows: listOfMenu, count: amount } = await Menu.findAndCountAll({ 
    order: [[datumExpression, 'ASC']],
    offset: amount - 5,
    limit: 5,
    attributes: {
        include: [
            [
              datumExpression,
              "datum",
            ],
          ]
     }

});