MIKRO-ORM: What is the best way to handle the count of votes on a post?

Question

I have 2 entities like the ones below. My question is what is the easiest / most efficient way to return the number of total votes that are linked to a given post?

A PostVote has an enum vote that can be either Up or Down.

I was thinking about using the serializer, but you would still return all of the PostVote rows just to get a count essentially.

Should I just do separate em.count() queries or is there a better way?

@Entity()
export class Post extends Node<Post> {
  @ManyToOne(() => User, { wrappedReference: true })
  user: IdentifiedReference<User>;

  @Property({ columnType: 'text' })
  title: string;

  @Property({ columnType: 'text', nullable: true })
  description?: string;

  @OneToMany(() => PostVotes, (pv) => pv.post, {
    serializer: (v: PostVotes[]) => {
      const upVotes = v.filter((v2) => v2.vote.Up).length;
      const downVotes = v.filter((v2) => v2.vote.Down).length;
      return upVotes - downVotes;
    },
  })
  votes = new Collection<PostVotes>(this);

@Entity()
export class PostVotes extends BaseEntity<
  PostVotesConstructorValues & PrivatePostVotesProperties,
  'post' | 'user'
> {
  @ManyToOne(() => Post, { wrappedReference: true, primary: true })
  post: IdentifiedReference<Post>;

  @ManyToOne(() => User, { wrappedReference: true, primary: true })
  user: IdentifiedReference<User>;

  [PrimaryKeyType]?: [IdentifiedReference<User>, IdentifiedReference<User>];

  @Enum({ items: () => PostVote })
  vote = PostVote;

Answer 1

Simple, yet more performant solution, would be to define a @Formula() property that loads the count via subquery. You could mark such property as lazy: true so it would not be selected automatically, only if you populate it.

Not tested, but something like this should work:

@Entity()
export class Post extends Node<Post> {
  // keep the 1:m as a way to modify the collection
  @OneToMany(() => PostVotes, (pv) => pv.post)
  votes = new Collection<PostVotes>(this);

  // add a subquery formula for easy reading of just the number
  @Formula(alias => `(select count(*) from vote_count vc where vc.post = ${alias}.id)`, { lazy: true })
  votesCount?: number;
}

And to read it:

const posts = await em.find(Post, {}, { populate: ['votesCount'] });

https://mikro-orm.io/docs/defining-entities#formulas

Similar can be achieved via virtual entities, depends on the use case:

https://mikro-orm.io/docs/virtual-entities

For performance critical paths it would make sense to have it as a real persistent property and maintain/sync its value when you create/remote votes.