Sequelizeを使用して、結合テーブルに対してCOUNT関数とDISTINCTを組み合わせて値を取得する #MySQL

概要

Sequelizeを使用して、結合テーブルに対してCOUNT関数とDISTINCTを組み合わせて結合テーブルの数を取得する。
実装方法を検索した際に、COUNT関数を使用する方法は多く見かけたが、COUNT関数にDISTINCTを付与する方法が見つからず苦戦したため投稿する。
SequelizeのメソッドはfindAllメソッドを使用する。

環境

■ MySQLバージョン
5.7.35
■ Sequelizeバージョン
3.30.4

テーブル設計

投稿とその投稿に対する閲覧数を保存しているテーブルがある。
目標は、全ての投稿とその閲覧数を取得することである。

Post（投稿テーブル）
- id
Browsing（閲覧数テーブル）
- id
- post_id

解決薬

結論として、以下の記述で対応可能である。

Post.findAll({
    include: [{model: Browsing, attributes:['id']}],
    group: [`Post.id`],
    subQuery: false,
    attributes: [
        "id",
        [sequelize.literal("COUNT(DISTINCT `Browsing`.`id`)"), "history_count"]
    ]
}).then((results)=>{
    const results_string = JSON.stringify(results);
    const last_results = JSON.parse(results_string);
})

発行されるSQL

SELECT 
    `Post`.`id`,
    `Browsing`.`id` AS `Browsing.id`,
    COUNT(DISTINCT `Browsing`.`id`) AS `history_count`
FROM
    `Post` AS `Post`
        LEFT OUTER JOIN
    `Browsing` AS `Browsing` ON `Post`.`id` = `Browsing`.`post_id`
GROUP BY `Post`.`id`
ORDER BY id DESC;

以下で、1つ１つの記述が必要な理由を記載する。

include

子テーブルを外部結合させている。
外部結合にしている理由は、閲覧数がない投稿も取得するためである。
必要であればattributesにカラムを追加する必要があるが、今回はCOUNT関数で指定するカラムのみ取得している。
テーブル結合しないと、COUNT関数実行時にBrowsingテーブルが存在しないのでエラーになる。

group

GROUP BY句を発行している。
GROUP BY句がないと、COUNT関数が意図した数値を指定できないので記載している。

subQuery: false

サブクエリを無効化している。
この記述がないと以下のようなSQLが発行される。

SELECT
    `Post`.*,
    `Browsing`.`id` AS `Browsing.id`
FROM
    (
        SELECT
            `Post`.`id`,
            COUNT(DISTINCT `Browsing`.`id`) AS `history_count`
        FROM
            `Post` AS `Post`
        GROUP BY
            `Post`.`id`
        ORDER BY
            id DESC
        LIMIT
            0, 25
    ) AS `Post`
    LEFT OUTER JOIN `Browsing` AS `Browsing` ON `Post`.`id` = `Browsing`.`post_id`
ORDER BY
    id DESC;

上記だと、サブクエリ内でCOUNT関数が発行されて閲覧数テーブルの数を取得できない。
そのためサブクエリを無効化している。

attributes

attributesの1つ目の要素は、投稿テーブルの取得するカラムを指定している。
sequelize.literalメソッド内の記載が、SELECT文として発行される。
第２引数にhistory_countとあるが、DBが返却するカラム名を指定しているので任意の名称で良い。

取得した結果をJSONオブジェクトに変換

SQL発行後に、Sequelizeから返却される値のCOUNT関数を使用して取得したプロパティ（results[0].history_count）を指定するとundefinedになる現状が発生した。
返却される値をJSONオブジェクトに変換すると指定できるようになった。

const results_string = JSON.stringify(results);
const last_results = JSON.parse(results_string);

取得できる値

例

id	Browsing.id	history_count
1	NULL	0
2	1	3
3	4	15