自己結合のSQLをTypeORMで書いた

バージョン

mysql: 5.7.19
@nestjs/common: 6.7.2
@nestjs/typeorm: 7.1.0
typeorm: 0.2.25
typescript: 3.6.3

やりたいこと

・自分をいいねしたユーザのユーザIDのリストを取得する
　※自分がブロックしたユーザは取得しない
　※自分をいいねした後に自分をブロックしたユーザは取得しない
・①自分にいいねがついた日時順と、②いいねをもらっている数が多いユーザの順で、ソートの切り分けができるようにする。

Entity

◆LikeEntity　いいねテーブル

物理	論理
id	like id
user_id	いいねしたユーザのID
target_user_id	いいねされたユーザのID
created_date	いいねした日時

※user_idとtarget_user_idはUNIQUEインデックス

◆BlockEntity　ブロックテーブル

物理	論理
id	block id
user_id	ブロックしたユーザのID
target_user_id	ブロックされたユーザのID
created_date	ブロックした日時

※user_idとtarget_user_idはUNIQUEインデックス

SQL

①自分にいいねがついた日時順

方針

単純にlikeテーブルからいいね対象が自分であるレコードを抽出して、
created_dateでORDER BY

-- ?は自分のユーザIDが入る
SELECT
    `likeT`.`user_id` AS `likeT_user_id` -- いいねしたユーザのユーザID
FROM
    `like` `likeT`
WHERE
    `likeT`.`target_user_id` = ? -- いいね対象が自分であるレコード
-- ブロック・被ブロックは除外
AND NOT EXISTS(
        SELECT
            id
        FROM
            `block` `blockT`
        WHERE
            (
                `blockT`.`target_user_id` = `likeT`.`user_id`
            AND `blockT`.`user_id` = ?
            )
        OR  (
                `blockT`.`user_id` = `likeT`.`user_id`
            AND `blockT`.`target_user_id` = ?
            )
    )
ORDER BY
    `likeT`.`created_date` DESC

②いいねをもらっている数が多いユーザの順

方針

登録されているレコードを下記とする場合

id	user_id	target_user_id
1	a	me
2	b	me
3	c	me
4	d	me
5	1a	a
6	1b	a
7	1c	b
8	1d	b
9	1e	b
10	1f	c

↓

①自分にいいねしてるユーザはaさん、bさん、cさん、dさん

id	user_id	target_user_id
1	a	me
2	b	me
3	c	me
4	d	me

②aさん、bさん、cさん、dさんがいいね対象になっているレコード(dさん無し)

id	user_id	target_user_id
5	1a	a
6	1b	a
7	1c	b
8	1d	b
9	1e	b
10	1f	c

↓

②aさん、bさん、cさん、dさんがいいね対象になっているレコードを集約してカウントする

target_user_id	liked_cnt	補足
a	2	← aさんがもらってるいいね数
b	3	← bさんがもらってるいいね数
c	1	← cさんがもらってるいいね数
d	0	← dさんがもらってるいいね数

↓

①自分がいいね対象のレコードに、
②自分にいいねしたユーザの被いいね数のカラムを結合する
(key： ①.user_id = ②.target_user_id)

user_id	target_user_id	liked_cnt
a	me	2
b	me	3
c	me	1
d	me	0

↓

liked_cntでORDER BY

-- ?は自分のユーザIDが入る
SELECT
    `likeT`.`user_id` AS `likeT_user_id` -- いいねしたユーザのユーザID
FROM
    `like` `likeT` -- ①
    -- ソート対象であるいいね数をカウントした集合②を作成し、自己結合する
    LEFT JOIN
        (
            -- いいねされたユーザID毎に、いいねされた件数をカウントする
            SELECT
                `likeT2`.`target_user_id` AS `targetUserId`,
                COUNT(`likeT2`.`target_user_id`) AS `likedCnt`
            FROM
                `like` `likeT2`
            GROUP BY
                `likeT2`.`target_user_id`
        ) `likedCntT`
    ON  `likeT`.`user_id` = likedCntT.targetUserId
WHERE
    `likeT`.`target_user_id` = ? -- いいね対象が自分であるレコード
-- ブロック・被ブロックは除外
AND NOT EXISTS(
        SELECT
            id
        FROM
            `block` `blockT`
        WHERE
            (
                `blockT`.`target_user_id` = `likeT`.`user_id`
            AND `blockT`.`user_id` = ?
            )
        OR  (
                `blockT`.`user_id` = `likeT`.`user_id`
            AND `blockT`.`target_user_id` = ?
            )
    )
ORDER BY
    IFNULL(`likedCntT`.`likedCnt`, 0) DESC,
    `likeT`.`created_date` DESC -- 第二ソート

TypeORM

import { InjectRepository } from "@nestjs/typeorm";
import { Repository } from "typeorm";
import { Like } from "src/entities/Like";

enum SortType {
    Good, // いいね数
    RecievedDate // いいね受け取った日時
}

constructor(
    @InjectRepository(Like)
    private readonly likeRepository: Repository<Like>,
) {}

async searchMyLikers(
    myUserId: string,
    sortType: SortType = null
): Promise<Like[]> {
    const queryBuilder = this.likeRepository
        .createQueryBuilder("likeT")
        .select("likeT.userId");

    // いいね順の場合のみ自己結合
    if (sortType === LikerSortType.Good) {
        queryBuilder.leftJoin(
            subQuery => {
                return subQuery
                    .select("likeT2.targetUserId", "targetUserId")
                    .addSelect("COUNT(likeT2.targetUserId)", "likedCnt")
                    .from(Like, "likeT2")
                    .groupBy("likeT2.targetUserId");
            },
            "likedCntT",
            "likeT.userId = likedCntT.targetUserId"
        );
    }

    queryBuilder
        .where("likeT.targetUserId = :myUserId", { myUserId })
        .andWhere(
            qb =>
                "NOT EXISTS " +
                qb
                    .subQuery()
                    .select("id")
                    .from(Block, "blockT")
                    .where(
                        new Brackets(qb => {
                            qb.where(
                                "blockT.targetUserId = likeT.userId"
                            ).andWhere("blockT.userId = :myUserId", {
                                myUserId
                            });
                        })
                    )
                    .orWhere(
                        new Brackets(qb => {
                            qb.where(
                                "blockT.userId = likeT.userId"
                            ).andWhere("blockT.targetUserId = :myUserId", {
                                myUserId
                            });
                        })
                    )
                    .getQuery()
        );

    if (sortType === LikerSortType.RecievedDate) {
        queryBuilder.orderBy("likeT.createdDate", "DESC");
    } else if (sortType === LikerSortType.Good) {
        queryBuilder
            .orderBy("IFNULL(`likedCntT`.`likedCnt`, 0)", "DESC")
            .addOrderBy("likeT.createdDate", "DESC");
    }

    console.log("sql:", queryBuilder.getSql());

    return await queryBuilder.getMany();
}