CODEWITHSUNDEEP
mysqldatabasenestjstypeorm
Andurit
Andurit

Reputation: 5762

TypeORM make COUNT query on table which maps two tables together

I have two entities Model and Video where Video basically store information about videos and Model store information about model. Because each video can have multiple models and each model can have multiple video entities looks like this:

How my entites and tables looks like:

// Video Entity
@Entity()
export class Video {
  @PrimaryGeneratedColumn()
  id?: number; 

  @Column({ charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci' })
  name: string;

  @Column({ type: 'text', charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci' })
  description: string;

  @ManyToMany((type) => Model)
  @JoinTable()
  models: Model[];
}


// Model Entity
@Entity()
export class Model {

  @PrimaryGeneratedColumn()
  id?: number; 

  @Column()
  name: string;

  @Column()
  code: string;
}

Because there is relation @ManyToMany between model and video TypeORM also created one extra table for connecting this two. Table name is video_models_model and it looks like this:

+-----------+---------+
| videoId   | modelId |
+===========+=========+
| 1         | 107     |
+-----------+---------+
| 2         | 142     |
+-----------+---------+
| 3         | 91      |
+-----------+---------+

What I need:

Based on modelId I need to find out COUNT() of videos. In regular query language it would be something like:

SELECT model.*, COUNT(model_videos.videoId) as totalVideos FROM model as model
LEFT JOIN `video_models_model` `model_videos` ON `model_videos`.`modelId`=`model`.`id` 
WHERE model.id = 1;

What I tried:

This is how regular query would looks like: 
this.modelRepository
  .createQueryBuilder('model')  
  .where('model.id = :id', { id: id })        
  .getOne();

so what I did was added to Model entity

  @ManyToMany(type => Video)
  @JoinTable()
  videos: Video[];

and after that I tried

this.modelRepository
  .createQueryBuilder('model')
  .leftJoin('model.videos', 'videos')
  .select('COUNT(videos)', 'totalVideos')  
  .where('model.id = :id', { id: id })        
  .getOne();

But it didn't work for me at all pluis it created one additional table named model_videos_video with modelId and videoId columns. So basically duplicate video_models_model table.

Is there any way how to make that easy query with TypeORM?

Upvotes: 3

Views: 24855

Answers (1)

Andurit
Andurit

Reputation: 5762

I found out that I have to change my entities to make them bi-directional so: Video Entity:

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

  @Column({ charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci' })
  name: string;

  @Column({ type: 'text', charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci' })
  description: string;

  @ManyToMany(type => Model, model => model.videos)
  @JoinTable()
  models: Model[];
}

Model Entity:

@Entity()
export class Model {

  @PrimaryGeneratedColumn()
  id?: number; 

  @Column()
  name: string;

  @Column()
  code: string;

  @ManyToMany(type => Video, video => video.models)
  videos: Video[];
}

And after that I make query like this:

this.modelRepository
  .createQueryBuilder('model')
  .loadRelationCountAndMap('model.videoCount', 'model.videos')
  .where('model.id = :id', { id: id })        
  .getOne();

Upvotes: 8

Related Questions