自己結合の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

```sql
-- ?は自分のユーザ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

```sql
-- ?は自分のユーザ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
```ts
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();
}
```