Typeorm Complex Query - get Average of a related table field and left join other tables

Mohamed Gad 0
Viewed 49

I need to perform a query using Typeorm as follows:

My Relations:

Template

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

  @OneToMany(() => Review, (review) => review.template)
  @JoinTable()
  reviews: Review[];

  @ManyToOne(() => Category, {
    eager: true,
  })
  @JoinTable()
  category: Category;
}

Reviews

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

  @Column()
  text: string;

  @Column()
  stars: number;

  @ManyToOne(() => Template, (template) => template.reviews)
  template: Template;
}

Category

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

  @OneToMany(() => Template, (template) => template.category)
  templates: Template;
}

What I want to accomplish is retrieving a template with the average stars (rating) and left join the category as a nested object!

I tried this query

this.templatesRepository
  .createQueryBuilder('temp')
  .where('temp.slug = :slug', { slug: slug })     
  .select('temp.*', '')
  .leftJoin('temp.reviews', 'reviews')
  .addSelect('AVG(reviews.stars)', 'stars')
  .leftJoin('temp.category', 'category')        
  .addSelect('category.*')
  .getRawOne();

The problem is I get the category back but it is not a nested object.

I want to get the category back as a nested object.

Does anyone know how can I do that?

The current Output

{
  id: 1,
  title: 'بلوجر', // this field belongs to the category and it did override the template's title
  thumbnail: '1662386512823-374.79221131561457-700.png',
  approved: 1,    
  slug: 'blogger', 
  previewLink: 'https://mobtakr.com/',   
  price: 50,
  slogan: 'قالب بلوجر احترافي',
  description: null,
  categoryId: 1,
  developerId: 1,
  createdAt: 2022-09-10T12:00:45.118Z,   
  updatedAt: 2022-09-09T13:04:33.201Z,   
  stars: '5.0000', // I get the stars
  purchases: 0,
  icon: 'FaBloggerB' // belongs to Category not Template
}

The query returns the result, but the category fields are added to the same JSON object.

So if the template and category have fields with the same name like 'title' it gets overridden.

I expect the result to be this way:

{
  id: 1,
  title: 'template', // this field belongs to the category and it did override the template's title
  thumbnail: '1662386512823-374.79221131561457-700.png',
  approved: 1,    
  slug: 'template', 
  previewLink: 'https://mobtakr.com/',   
  price: 50,
  slogan: 'قالب بلوجر احترافي',
  description: 'template',
  category: {
    id : 1,
    title :'Blogger',
    icon : 'FaBloggerB',
    slug: 'blogger', 
    description: 'category ',
  }
  createdAt: 2022-09-10T12:00:45.118Z,   
  updatedAt: 2022-09-09T13:04:33.201Z,   
  stars: '5.0000', // I get the stars
  purchases: 0,
}
typeorm sql node.js
1 Answers

You need to use getOne instead of getRawOne as typeorm will only be able to nest the objects according to definitions inside the entity classes. Since your query is aggregating some value on a related table, you'll have to add a field in the Template class like this:

@Column({ nullable: true, select: false })
stars: number;

Now your query would be:

this.templatesRepository
  .createQueryBuilder('temp')  
  .leftJoin('temp.reviews', 'reviews')
  .addSelect('AVG(reviews.stars)', 'temp_stars')      //please note the aliasing here
  .leftJoinAndSelect('temp.category', 'category')
  .where('temp.slug = :slug', { slug: slug })
  .getOne();

This will give you the Category object nested inside the Templates object.

user3uh80
Related