DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
    `id` INTEGER,
    `id2` INTEGER,
    `article_id` INTEGER,
    `article_id2` INTEGER,
    `author_id` INTEGER,
    `votes` INTEGER,
    `body` TEXT,
    `published` BOOLEAN,
    PRIMARY KEY (`id`),
    KEY `comments_article_id` (`article_id`, `article_id2`),
    KEY `comments_top_comments_sort` (`votes`, `id`)
);

DROP TABLE IF EXISTS `comments_i18n`;
CREATE TABLE `comments_i18n` (
    `id` INTEGER AUTO_INCREMENT,
    `locale` VARCHAR(255),
    `model` VARCHAR(255),
    `foreign_key` INTEGER,
    `field` VARCHAR(255),
    `content` TEXT,
    PRIMARY KEY (`id`),
    UNIQUE KEY `comments_I18N_LOCALE_FIELD` (`locale`, `model`, `foreign_key`, `field`),
    KEY `comments_I18N_FIELD` (`model`, `foreign_key`, `field`)
);

INSERT INTO `comments` (`id`, `id2`, `article_id`, `article_id2`, `author_id`, `votes`, `body`, `published`) VALUES
    (1, 2, 1, 2, 1, 1, 'Lorem ipsum dolor sit amet', 1),
    (2, 3, 1, 2, 1, 2, 'Lorem ipsum dolor sit amet', 1),
    (3, 4, 1, 2, 2, 3, 'Lorem ipsum dolor sit amet', 1),
    (4, 5, 1, 2, 2, 4, 'Lorem ipsum dolor sit amet', 0),
    (5, 6, 2, 3, 1, 10, 'Lorem ipsum dolor sit amet', 1),
    (6, 7, 2, 3, 1, 9, 'Lorem ipsum dolor sit amet', 1),
    (7, 8, 2, 3, 2, 8, 'Lorem ipsum dolor sit amet', 1),
    (8, 9, 2, 3, 2, 7, 'Lorem ipsum dolor sit amet', 0);

INSERT INTO `comments_i18n` (`locale`, `model`, `foreign_key`, `field`, `content`) VALUES
    ('de_DE', 'Comments', 1, 'body', 'Franz jagt im komplett verwahrlosten Taxi quer durch Bayern'),
    ('de_DE', 'Comments', 2, 'body', 'Der schnelle braune Fuchs springt über den faulen Hund'),
    ('de_DE', 'Comments', 5, 'body', 'Fix Schwyz! quäkt Jürgen blöd vom Paß'),
    ('de_DE', 'Comments', 6, 'body', 'Quod erat demonstrandum.');

SELECT
    TopComments.id AS TopComments__id,
    TopComments.article_id AS TopComments__article_id,
    TopComments.body AS TopComments__body,
    TopComments.votes AS TopComments__votes,
    TopComments_body_translation.id AS TopComments_body_translation__id,
    TopComments_body_translation.content AS TopComments_body_translation__content
FROM
    comments TopComments
INNER JOIN
    comments_i18n TopComments_body_translation ON (
        TopComments_body_translation.model = 'Comments'
        AND TopComments_body_translation.field = 'body'
        AND TopComments_body_translation.locale = 'de_DE'
        AND TopComments.id = TopComments_body_translation.foreign_key
    )
WHERE (
    TopComments.article_id in (1, 2)
    AND TopComments.id in (
        SELECT
            __ranked__TopComments.id AS id
        FROM (
            SELECT
                TopComments.id AS TopComments__id,
                TopComments.article_id AS TopComments__article_id,
                TopComments.body AS TopComments__body,
                TopComments.votes AS TopComments__votes,
                TopComments.id AS id,
                (
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            TopComments.article_id
                        ORDER BY
                            TopComments.votes DESC,
                            TopComments.id ASC
                    )
                ) AS __row_number,
                TopComments_body_translation.id AS TopComments_body_translation__id,
                TopComments_body_translation.content AS TopComments_body_translation__content
            FROM
                comments TopComments
            INNER JOIN
                comments_i18n TopComments_body_translation ON (
                    TopComments_body_translation.model = 'Comments'
                    AND TopComments_body_translation.field = 'body'
                    AND TopComments_body_translation.locale = 'de_DE'
                    AND TopComments.id = TopComments_body_translation.foreign_key
                )
            WHERE
                TopComments.article_id in (1, 2)
        )
        __ranked__TopComments
        WHERE
            __ranked__TopComments.__row_number <= 3
    )
)
ORDER BY
    TopComments.votes DESC,
    TopComments.id ASC;

DROP TABLE `comments`;
DROP TABLE `comments_i18n`;
