やりたいこと

dailyReviewテーブルとweeklyReviewテーブルを紐付けたい。

DailyReviewEntity と WeeklyReviewEntity の関係

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の項目が新たに生成された。

weeklyReviewテーブル

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

`DailyReviewList`を確認してみる

$ curl "localhost:3000/daily-review" | jq

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

[
  {
    "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`を確認してみる

curl "localhost:3000/weekly-review" | jq

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

[
  {
    "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"
      }
    ]
  }
]

参考サイト