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",
],
]
}
});
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",
],
]
}
});