how do I ORDER BY timestamp before GROUP BY in sequelize ORM?
Sample sequelize Query:
await this._message.findAll({
attributes: {
include: [
[
sequelize.literal(`(
SELECT *
FROM messages AS messages
ORDER BY
messages.created_at DESC
)`),
'messages'
],
]
},
include: [
{
required: true,
model: App,
as: 'apps',
where: {
id: app.id,
user_id: user_id
}
},
{
required: true,
model: Contact,
as: 'contacts',
},
],
order: [
['created_at', 'DESC'],
],
group: ['contact_id'],
})
.then(data => {
....
})
.catch(err => {
....
});
}
sample MySql Query:
SELECT
`messages`.`id`,
`messages`.`app_id`,
`messages`.`contact_id`,
`messages`.`parameters`,
`messages`.`wa_message_id`,
`messages`.`type`,
`messages`.`status`,
`messages`.`created_at`,
`messages`.`updated_at`,
(
SELECT
*
FROM
messages AS messages
ORDER BY
messages.created_at DESC
) AS `messages`,
`apps`.`id` AS `apps.id`,
`apps`.`user_id` AS `apps.user_id`,
`apps`.`name` AS `apps.name`,
`apps`.`application_id` AS `apps.application_id`,
`apps`.`wb_account_id` AS `apps.wb_account_id`,
`apps`.`access_token` AS `apps.access_token`,
`apps`.`created_at` AS `apps.created_at`,
`apps`.`updated_at` AS `apps.updated_at`,
`contacts`.`id` AS `contacts.id`,
`contacts`.`user_id` AS `contacts.user_id`,
`contacts`.`country_code_id` AS `contacts.country_code_id`,
`contacts`.`first_name` AS `contacts.first_name`,
`contacts`.`last_name` AS `contacts.last_name`,
`contacts`.`name` AS `contacts.name`,
`contacts`.`email` AS `contacts.email`,
`contacts`.`phone_number` AS `contacts.phone_number`,
`contacts`.`convo_started` AS `contacts.convo_started`,
`contacts`.`notes` AS `contacts.notes`,
`contacts`.`last_active` AS `contacts.last_active`,
`contacts`.`created_at` AS `contacts.created_at`,
`contacts`.`updated_at` AS `contacts.updated_at`
FROM
`messages` AS `messages`
INNER JOIN `apps` AS `apps` ON `messages`.`app_id` = `apps`.`id`
AND `apps`.`id` = 3
AND `apps`.`user_id` = 1
INNER JOIN `contacts` AS `contacts` ON `messages`.`contact_id` = `contacts`.`id`
GROUP BY
`contact_id`
ORDER BY
`messages`.`created_at` DESC;