LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@maiamea(まいあ めあ)

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

Last updated at Posted at 2021-10-11

やりたいこと

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

DailyReviewEntity と WeeklyReviewEntity の関係

スクリーンショット 2021-10-10 18.33.02.png

スクリーンショット 2021-10-10 18.33.21.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

weeklyReviewテーブル

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

スクリーンショット 2021-10-11 10.27.11.png

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

参考サイト

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?