Nestjs typeorm many to many order by count

Question

I have two entities image and tag with many to many relationship

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

  @Column('text', { nullable: true })
  caption: string;

  @Column('text', { nullable: false })
  url: string;

  @Column('text', { nullable: false })
  thumbnailUrl: string;

  @ManyToOne(() => User, {
    eager: true,
  })
  uploader: User;

  @JoinTable()
  @ManyToMany(() => Tag, (tag: Tag) => tag.images, {
    cascade: true,
    eager: true,
  })
  tags?: Tag[];

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;

and Tag entity

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

  @Column()
  name: string;

  @ManyToMany(() => Image, (image) => image.tags)
  images: Image[];
}

Now I want to fetch popular tags. For that take 10 tags that have highest number of images created in last 7 days. For this i tried following in tag.repository.ts

getPopular() {
    return (
      this.createQueryBuilder('tag')
        .addSelect((query) => {
          return query
          .select('COUNT(I.id)', 'count')
          .from('image', 'I')
          .where('I.created_at > :date', {
            date: DateTime.now().minus({ weeks: 1 }).toISODate(),
          });
        }, 'imagecount')
        .orderBy('imagecount', 'DESC')
        .take(10)
        .getMany()
    );

But this will count all images that are created in last 7 days and will not consider if the image has that tag or not. So how should I load relation so that it count images with tags?

Answer 1

I would suggest you change your approach a little bit and try grouping after filtering by the last 7 days. Also, unless you have an explicit case where you will be adding images to a tag, I would recommend dropping the images property in your Tag entity since you already have a way to associate tags with an Image.

  this.repository
  .createQueryBuilder('image')
  .where('image.created_at > :created_at', {
    created_at: '2022-01-14 01:02:26', // make sure you set your own date here
  })
  .leftJoinAndSelect('image.tags', 'tag')
  .groupBy('tag.id') // here is where we grup by the tag so we can count
  .addGroupBy('tag.id')
  .select('tag.id, count(tag.id)') // here is where we count :)
  .orderBy('count(tag.id)', 'DESC')
  .limit(10) // here is the limit
  .execute();

These two sets of documentation could help you better in understanding how this query is built.

https://orkhan.gitbook.io/typeorm/docs/many-to-many-relations

https://github.com/typeorm/typeorm/blob/master/docs/select-query-builder.md#adding-group-by-expression