Sequelize.js: Как переместить ORDER BY в подзапрос AFTER LIMIT без использования subQuery: false? [дубликат] ⇐ MySql

[Anonymous]

Как переместить ORDER BY в подзапрос СРАЗУ ПОСЛЕ LIMIT без использования подзапроса: false?
Это мой запрос на продолжение:

Код: Выделить всё

return await this._conversation.findAll({
// subQuery: false,
where: params.conversation_query,
separate: true,
include: [
{
required: true,
model: Message, // has Many
as: 'messages',
where: params.message_query,
},
{
required: true,
model: Contact, // belongs to
as: 'contacts',
where: params.contact_query,
include: [
{
as: 'country_code',
model: CountryCode, // belongs to
required: true,
},
{
as: 'labels',
model: Label, // through pivot table `contact_label`, many-to-many
required: false,
}
]
},
],
group: ['conversation.id'],
order: [
[ 'messages', 'created_at', 'DESC' ],
],
limit: 3 / 10,
})
.then(data =>  {

return data;

})

Вот сгенерированный необработанный запрос:

Код: Выделить всё

SELECT
`conversation`.*,
`messages`.`id` AS `messages.id`,
`messages`.`conversation_id` AS `messages.conversation_id`,
`messages`.`user_id` AS `messages.user_id`,
`messages`.`reply_message_id` AS `messages.reply_message_id`,
`messages`.`reaction_message_id` AS `messages.reaction_message_id`,
`messages`.`parameters` AS `messages.parameters`,
`messages`.`keywords` AS `messages.keywords`,
`messages`.`wa_message_id` AS `messages.wa_message_id`,
`messages`.`type` AS `messages.type`,
`messages`.`status` AS `messages.status`,
`messages`.`error_code` AS `messages.error_code`,
`messages`.`error_message` AS `messages.error_message`,
`messages`.`created_at` AS `messages.created_at`,
`messages`.`updated_at` AS `messages.updated_at`,
`contacts->labels`.`id` AS `contacts.labels.id`,
`contacts->labels`.`user_id` AS `contacts.labels.user_id`,
`contacts->labels`.`name` AS `contacts.labels.name`,
`contacts->labels`.`description` AS `contacts.labels.description`,
`contacts->labels`.`color` AS `contacts.labels.color`,
`contacts->labels`.`created_at` AS `contacts.labels.created_at`,
`contacts->labels`.`updated_at` AS `contacts.labels.updated_at`,
`contacts->labels->contact_label`.`id` AS `contacts.labels.contact_label.id`,
`contacts->labels->contact_label`.`contact_id` AS `contacts.labels.contact_label.contact_id`,
`contacts->labels->contact_label`.`label_id` AS `contacts.labels.contact_label.label_id`,
`contacts->labels->contact_label`.`created_at` AS `contacts.labels.contact_label.created_at`,
`contacts->labels->contact_label`.`updated_at` AS `contacts.labels.contact_label.updated_at`
FROM
(
SELECT
`conversation`.`id`,
`conversation`.`user_id`,
`conversation`.`phone_number_id`,
`conversation`.`contact_id`,
`conversation`.`status`,
`conversation`.`chatbot_status`,
`conversation`.`blocked`,
`conversation`.`last_active`,
`conversation`.`start_at`,
`conversation`.`end_at`,
`conversation`.`expiry_date`,
`conversation`.`created_at`,
`conversation`.`updated_at`,
`conversation`.`deleted_at`,
`contacts`.`id` AS `contacts.id`,
`contacts`.`uuid` AS `contacts.uuid`,
`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`.`username` AS `contacts.username`,
`contacts`.`email` AS `contacts.email`,
`contacts`.`phone_number` AS `contacts.phone_number`,
`contacts`.`full_phone_number` AS `contacts.full_phone_number`,
`contacts`.`notes` AS `contacts.notes`,
`contacts`.`block` AS `contacts.block`,
`contacts`.`subscribed` AS `contacts.subscribed`,
`contacts`.`last_active_at` AS `contacts.last_active_at`,
`contacts`.`created_at` AS `contacts.created_at`,
`contacts`.`updated_at` AS `contacts.updated_at`,
`contacts->country_code`.`id` AS `contacts.country_code.id`,
`contacts->country_code`.`name` AS `contacts.country_code.name`,
`contacts->country_code`.`value` AS `contacts.country_code.value`,
`contacts->country_code`.`iso` AS `contacts.country_code.iso`,
`contacts->country_code`.`created_at` AS `contacts.country_code.created_at`,
`contacts->country_code`.`updated_at` AS `contacts.country_code.updated_at`
FROM
`conversation` AS `conversation`
INNER JOIN `contacts` AS `contacts` ON `conversation`.`contact_id` = `contacts`.`id`
INNER JOIN `country_codes` AS `contacts->country_code` ON `contacts`.`country_code_id` = `contacts->country_code`.`id`
WHERE
(
`conversation`.`deleted_at` IS NULL
AND `conversation`.`status` = 'open'
)
AND (
SELECT
`conversation_id`
FROM
`messages` AS `messages`
WHERE
(
`messages`.`id` = (
SELECT
MAX(id)
FROM
`messages`
WHERE
`conversation_id` = `conversation`.`id`
)
AND `messages`.`conversation_id` = `conversation`.`id`
)
LIMIT
1
) IS NOT NULL
GROUP BY
`conversation`.`id`
LIMIT
10
) AS `conversation`
INNER JOIN `messages` AS `messages` ON `conversation`.`id` = `messages`.`conversation_id`
AND `messages`.`id` = (
SELECT
MAX(id)
FROM
`messages`
WHERE
`conversation_id` = `conversation`.`id`
)
LEFT OUTER JOIN (
`contact_label` AS `contacts->labels->contact_label`
INNER JOIN `labels` AS `contacts->labels` ON `contacts->labels`.`id` = `contacts->labels->contact_label`.`label_id`
) ON `contacts.id` = `contacts->labels->contact_label`.`contact_id`
ORDER BY
`messages`.`created_at` DESC;

как вы видите, ORDER BY применяется только к запросу OUTER, в результате чего в запросе LIMIT НЕТ ORDER (

Код: Выделить всё

bug

)
Можно ли применить ORDER BY в подзапрос сразу после ограничения? если нет, то как мне это сделать с помощью subQuery: true?

Подробнее здесь: https://stackoverflow.com/questions/793 ... using-subq