NestJSのRelationsの方法（Entity同士の結合方法）〜 Many-to-one / one-to-many relations の場合 〜

# やりたいこと
* `dailyReview`テーブルと`weeklyReview`テーブルを紐付けたい。

# DailyReviewEntity と WeeklyReviewEntity の関係
![スクリーンショット 2021-10-10 18.33.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384836/8fbd0272-f305-ed7a-8fe2-4acfd9ec598b.png)

![スクリーンショット 2021-10-10 18.33.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384836/b15e3966-7aa8-c41d-9017-98788151a772.png)



# DailyReview
## Entity
```nest-test/src/entities/daily-review.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, ManyToOne } from 'typeorm'; // ← ManyToOne を追加
import { WeeklyReview } from './weekly-review.entity';

@Entity()
export class DailyReview {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({
        type: 'date', 
        default: () => 'current_date'
    })
    date: Date;

    @Column()
    sleepScore: number;

    @Column()
    walk: boolean;

    @Column()
    exercise: boolean;

    @Column('text')
    comment: string;

    // 以下の2行を追加 
    @ManyToOne(() => WeeklyReview, weeklyReview => weeklyReview.dailyReviews)
    // 一つの DailyReview に紐付いてる WeeklyReview を取り出すときに使うプロパティ名
    // そのプロパティ名+Id が自動で、外部キー名として DailyReview テーブルに追加される (ここでは weeklyReviewIdとなる)
    weeklyReview: WeeklyReview;
}
```

## Service
```nest-test/src/daily-review/daily-review.service.ts
import { Injectable } from '@nestjs/common';
import { DailyReview } from 'src/entities/daily-review.entity';
import { Repository } from 'typeorm';
import { InjectRepository } from '@nestjs/typeorm';

@Injectable()
export class DailyReviewService {
    constructor(
        @InjectRepository(DailyReview)
        private readonly dailyReviewRepository: Repository<DailyReview>
    ) { }

    getDailyReviewList() {
        // return this.dailyReviewRepository.find(); ← 紐付けない場合
        return this.dailyReviewRepository.find({ relations: ["weeklyReview"]}); // ← 紐付ける場合
    }

    addDailyReview(sleepScore: number, walk: boolean, exercise: boolean, comment: string) {
        const dailyReview = new DailyReview();
        dailyReview.sleepScore = sleepScore;
        dailyReview.walk = walk;
        dailyReview.exercise = exercise;
        dailyReview.comment = comment;
        return this.dailyReviewRepository.insert(dailyReview);
    }

    async updateDailyReview(
        id: number, sleepScore: number, walk: boolean, exercise: boolean, comment: string
    ) {
        const daily_review = await this.dailyReviewRepository.findOne({id: id});
        daily_review.sleepScore = sleepScore;
        daily_review.walk = walk;
        daily_review.exercise = exercise;
        daily_review.comment = comment;
        await this.dailyReviewRepository.save(daily_review);
    }

    deleteDailyReview(id: number) {
        return this.dailyReviewRepository.delete({id: id});
    }
}
```

## Controller
* 追加内容なし

```nest-test/src/daily-review/daily-review.controller.ts
import { Controller, Get, Post, Query } from '@nestjs/common';
import { DailyReviewService } from './daily-review.service';

@Controller('daily-review')
export class DailyReviewController {
    constructor(private readonly service: DailyReviewService) { }

    @Get()
    getDailyReviewList() {
        return this.service.getDailyReviewList();
    }

    @Post()
    addDailyReview(@Query() query: { 
        sleepScore: number, walk: string, exercise: string, comment: string
    }) {
        console.log({walk: query.walk});
        const walk: boolean = query.walk === 'true';
        const exercise: boolean = query.exercise === 'true';
        return this.service.addDailyReview(
            query.sleepScore, walk, exercise, query.comment
        );
    }

    @Post('update')
    updateDailyReview(@Query() query: {
        id:number, sleepScore: number, walk: string, exercise: string, comment: string
    }) {
        const walk: boolean = query.walk === 'true';
        const exercise: boolean = query.exercise === 'true';
        return this.service.updateDailyReview(
            query.id, query.sleepScore, walk, exercise, query.comment
        );
    }

    @Post('delete')
    deleteDailyReview(@Query() query: { id: number}) {
        return this.service.deleteDailyReview(query.id);
    }
}
```

## Module
```nest-test/src/daily-review/daily-review.module.ts
import { Module } from '@nestjs/common';
import { DailyReviewController } from './daily-review.controller';
import { DailyReviewService } from './daily-review.service';
import { DailyReview } from 'src/entities/daily-review.entity';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  controllers: [DailyReviewController],
  imports: [TypeOrmModule.forFeature([DailyReview])],
  providers: [DailyReviewService]
})
export class DailyReviewModule {}

```



# WeeklyReview

## Entity
```nest-test/src/entities/weekly-review.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm'; // ← OneToMany を追加 
import { DailyReview } from './daily-review.entity';

@Entity()
export class WeeklyReview {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({
        type: 'date',
        default: () => 'current_date'
    })
    date: Date;

    @Column()
    sleepScoreAvg: number;

    @Column()
    walkCount: number;

    @Column()
    exerciseCount: number;
    
    // 以下の2行を追加
    @OneToMany(() => DailyReview, dailyReview => dailyReview.weeklyReview)
    // 一つの WeeklyReview に紐付いてる DailyReview を取り出すときに使うプロパティ名
    // 一つの WeeklyReview に紐付いてる DailyReview は複数あるので複数形
    dailyReviews: DailyReview[]
}
```

## Service
```nest-test/src/weekly-review/weekly-review.service.ts
import { Injectable } from '@nestjs/common';
import { WeeklyReview } from 'src/entities/weekly-review.entity';
import { Repository } from 'typeorm';
import { InjectRepository } from '@nestjs/typeorm';

@Injectable()
export class WeeklyReviewService {
    constructor(
        @InjectRepository(WeeklyReview)
        private readonly weeklyRepository: Repository<WeeklyReview>
    ) { }

    getWeeklyReviewList() {
        return this.weeklyRepository.find( {
            relations: ['dailyReviews'],
            order: {
                date: "ASC",
                id: "DESC"
            }
        });
    }

    addWeeklyReview(date: Date, sleepScoreAvg: number, walkCount: number, exerciseCount: number) {
        const weeklyReview = new WeeklyReview();
        weeklyReview.date = date;
        weeklyReview.sleepScoreAvg = sleepScoreAvg;
        weeklyReview.walkCount = walkCount;
        weeklyReview.exerciseCount = exerciseCount;
        return this.weeklyRepository.insert(weeklyReview);
    }

    async updateWeeklyReview(
        id: number, date: Date, sleepScoreAvg: number, walkCount: number, exerciseCount: number
    ) {
        const weekly_review = await this.weeklyRepository.findOne({id: id});
        weekly_review.date = date;
        weekly_review.sleepScoreAvg = sleepScoreAvg;
        weekly_review.walkCount = walkCount;
        weekly_review.exerciseCount = exerciseCount;
        await this.weeklyRepository.save(weekly_review);
    }

    async calculateSleepScoreAvg(weeklyReviewId: number) {
        // weeklyRepository から、指定した weeklyReviewId の　weeklyReview データを取得。その時一緒に紐付いてる　dailyReview データも取ってくる
        const weeklyReview = await this.weeklyRepository.findOne(weeklyReviewId, {relations:['dailyReviews']});
        // weeklyReview に紐付いてる dailyReview の配列を変数に代入
        const dailyReviews = weeklyReview.dailyReviews;
        const sleepScores = dailyReviews.map(dr => dr.sleepScore);
        const sumSleepScore = sleepScores.reduce((accumulator, currentValue) => accumulator + currentValue, 0);
        const avgSleepScore = parseFloat((sumSleepScore / sleepScores.length).toFixed(2));
        // 集計結果をDBに保存
        weeklyReview.sleepScoreAvg = avgSleepScore;
        this.weeklyRepository.save(weeklyReview);
        return avgSleepScore;
    }

    async calculateWalkCount(weeklyReviewId: number) {
        const weeklyReview = await this.weeklyRepository.findOne(weeklyReviewId, {relations:['dailyReviews']});
        const dailyReviews = weeklyReview.dailyReviews;
        const count = dailyReviews.filter((dailyReview) => dailyReview.walk === true).length;
        weeklyReview.walkCount = count;
        this.weeklyRepository.save(weeklyReview);
        return count;
    }

    async calculateExerciseCount(weeklyReviewId: number) {
        const weeklyReview = await this.weeklyRepository.findOne(weeklyReviewId, {relations: ['dailyReviews']});
        const dailyReviews = weeklyReview.dailyReviews;
        const count = dailyReviews.filter((dailyReview) => dailyReview.exercise).length;
        weeklyReview.exerciseCount = count;
        this.weeklyRepository.save(weeklyReview);
        return count;
    }

    deleteWeeklyReview(id: number) {
        return this.weeklyRepository.delete({id: id});
    }
}
```

## Controller
* 追加内容なし

```nest-test/src/weekly-review/weekly-review.controller.ts
import { Controller, Get, Post, Query } from '@nestjs/common';
import { WeeklyReviewService } from './weekly-review.service';

@Controller('weekly-review')
export class WeeklyReviewController {
    constructor(private readonly service: WeeklyReviewService) { }

    @Get()
    getWeeklyReviewList() {
        return this.service.getWeeklyReviewList();
    }

    @Post()
    addWeeklyReview(@Query() query: {
        date: Date, sleepScoreAvg: number, walkCount: number, exerciseCount: number
    }) {
        return this.service.addWeeklyReview(
            query.date, query.sleepScoreAvg, query.walkCount, query.exerciseCount
        );
    }

    @Post('update')
    updateWeeklyReview(@Query() query: {
        id: number, date: Date, sleepScoreAvg: number, walkCount: number, exerciseCount: number
    }) {
        return this.service.updateWeeklyReview(
            query.id, query.date, query.sleepScoreAvg, query.walkCount, query.exerciseCount
        );
    }

    @Post('delete')
    deleteWeeklyReview(@Query() query: {id: number}) {
        return this.service.deleteWeeklyReview(query.id);
    }

    @Post('calculate/sleep-score-average')
    calculateSleepScoreAvg(@Query() query: {
        id: number
    }) {
        return this.service.calculateSleepScoreAvg(query.id);
    }

    @Post('calculate/walk-count')
    calculateWalkCount(@Query() query: {
        id: number
    }) {
        return this.service.calculateWalkCount(query.id);
    }

    @Post('calculate/exercise-count')
    calculateExerciseCount(@Query() query: {
        id: number
    }) {
        return this.service.calculateExerciseCount(query.id);
    }

    // 3つの機能を一度に処理する便利メソッド
    @Post('calculate')
    calculate(@Query() query: {id: number}) {
        this.service.calculateSleepScoreAvg(query.id);
        this.service.calculateWalkCount(query.id);
        this.service.calculateExerciseCount(query.id);
        return 'OK';
    }
}
```

## Module
```nest-test/src/weekly-review/weekly-review.module.ts
import { Module } from '@nestjs/common';
import { WeeklyReviewController } from './weekly-review.controller';
import { WeeklyReviewService } from './weekly-review.service';
import { WeeklyReview } from 'src/entities/weekly-review.entity';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  controllers: [WeeklyReviewController],
  imports: [TypeOrmModule.forFeature([WeeklyReview])],
  providers: [WeeklyReviewService]
})
export class WeeklyReviewModule {}

```


# サーバー起動
```
$ npm run start:dev
```

# 実行結果

## TablePlus （SQLiteを使用）

### dailyReviewテーブル

* `weeklyReviewId`の項目が新たに生成された。

![スクリーンショット 2021-10-11 10.19.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384836/7a37cc8b-8efa-0ca5-0750-d396f6d9c269.png)


### weeklyReviewテーブル

* 月曜始まりとしているので`date`は月曜日の日付。

![スクリーンショット 2021-10-11 10.27.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384836/ac80ace7-0cfa-84d1-1328-81ff53e436f4.png)



## `DailyReviewList`を確認してみる
```bash
$ curl "localhost:3000/daily-review" | jq
```

* `dailyReview`に紐付いてる`weeklyReview`も一緒に取得できた。

```json
[
  {
    "id": 1,
    "date": "2021-10-04",
    "sleepScore": 3,
    "walk": true,
    "exercise": true,
    "comment": "yeah",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 2,
    "date": "2021-10-05",
    "sleepScore": 5,
    "walk": true,
    "exercise": false,
    "comment": "yeah",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 3,
    "date": "2021-10-06",
    "sleepScore": 5,
    "walk": false,
    "exercise": false,
    "comment": "yeah",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 4,
    "date": "2021-10-07",
    "sleepScore": 3,
    "walk": true,
    "exercise": true,
    "comment": "aaaaa",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 5,
    "date": "2021-10-08",
    "sleepScore": 4,
    "walk": true,
    "exercise": false,
    "comment": "ttttt",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 6,
    "date": "2021-10-09",
    "sleepScore": 3,
    "walk": true,
    "exercise": true,
    "comment": "bbb",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 7,
    "date": "2021-10-10",
    "sleepScore": 5,
    "walk": false,
    "exercise": true,
    "comment": "ccc",
    "weeklyReview": {
      "id": 1,
      "date": "2021-10-04",
      "sleepScoreAvg": 4,
      "walkCount": 5,
      "exerciseCount": 4
    }
  },
  {
    "id": 8,
    "date": "2021-10-11",
    "sleepScore": 5,
    "walk": true,
    "exercise": true,
    "comment": "ddd",
    "weeklyReview": {
      "id": 2,
      "date": "2021-10-11",
      "sleepScoreAvg": 3.5,
      "walkCount": 2,
      "exerciseCount": 1
    }
  },
  {
    "id": 9,
    "date": "2021-10-12",
    "sleepScore": 2,
    "walk": true,
    "exercise": false,
    "comment": "eee",
    "weeklyReview": {
      "id": 2,
      "date": "2021-10-11",
      "sleepScoreAvg": 3.5,
      "walkCount": 2,
      "exerciseCount": 1
    }
  }
]
```

## `WeeklyReviewList`を確認してみる
```bash
curl "localhost:3000/weekly-review" | jq
```

* `weeklyReview`に紐付いてる`dailyReviews`も一緒に取得できた。

```json
[
  {
    "id": 1,
    "date": "2021-10-04",
    "sleepScoreAvg": 4,
    "walkCount": 5,
    "exerciseCount": 4,
    "dailyReviews": [
      {
        "id": 1,
        "date": "2021-10-04",
        "sleepScore": 3,
        "walk": true,
        "exercise": true,
        "comment": "yeah"
      },
      {
        "id": 2,
        "date": "2021-10-05",
        "sleepScore": 5,
        "walk": true,
        "exercise": false,
        "comment": "yeah"
      },
      {
        "id": 3,
        "date": "2021-10-06",
        "sleepScore": 5,
        "walk": false,
        "exercise": false,
        "comment": "yeah"
      },
      {
        "id": 4,
        "date": "2021-10-07",
        "sleepScore": 3,
        "walk": true,
        "exercise": true,
        "comment": "aaaaa"
      },
      {
        "id": 5,
        "date": "2021-10-08",
        "sleepScore": 4,
        "walk": true,
        "exercise": false,
        "comment": "ttttt"
      },
      {
        "id": 6,
        "date": "2021-10-09",
        "sleepScore": 3,
        "walk": true,
        "exercise": true,
        "comment": "bbb"
      },
      {
        "id": 7,
        "date": "2021-10-10",
        "sleepScore": 5,
        "walk": false,
        "exercise": true,
        "comment": "ccc"
      }
    ]
  },
  {
    "id": 2,
    "date": "2021-10-11",
    "sleepScoreAvg": 3.5,
    "walkCount": 2,
    "exerciseCount": 1,
    "dailyReviews": [
      {
        "id": 8,
        "date": "2021-10-11",
        "sleepScore": 5,
        "walk": true,
        "exercise": true,
        "comment": "ddd"
      },
      {
        "id": 9,
        "date": "2021-10-12",
        "sleepScore": 2,
        "walk": true,
        "exercise": false,
        "comment": "eee"
      }
    ]
  }
]
```

# 参考サイト
* [NestJS公式サイト](https://docs.nestjs.com/)
* [NestJSのススメ ～Expressを超えてゆけ～](https://qiita.com/elipmoc101/items/9b1e6b3efa62f3c2a166#%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB%E3%83%97%E3%83%AD%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E4%BD%9C%E6%88%90db%E6%93%8D%E4%BD%9C%E3%81%BE%E3%81%A7)
* [Many-to-one / one-to-many relations](https://orkhan.gitbook.io/typeorm/docs/many-to-one-one-to-many-relations)
* [TypeORM x NestJS についての備忘録](https://zenn.dev/okakyo/scraps/9503c939f45b40)