JPA: how can i map a property to a derived column that is based on a COUNT from other tables (Without incurring in n+1 select problems?)

Question

These are the entities defined in my project:

Post

@Entity
public class Post implements Serializable {
    public enum Type {TEXT, IMG}

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "section_id")
    protected Section section;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "author_id")
    protected User author;

    @Column(length = 255, nullable = false)
    protected String title;

    @Column(columnDefinition = "TEXT", nullable = false)
    protected String content;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    protected Type type;

    @CreationTimestamp
    @Column(nullable = false, updatable = false, insertable = false)
    protected Instant creationDate;

    @Column(insertable = false, updatable = false)
    protected Integer votes;  
    
    /*accessor methods*/
}

Comment

@Entity
public class Comment implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "post_id")
    protected Post post;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "author_id")
    protected User author;

    @ManyToOne(fetch = FetchType.LAZY, optional = true)
    @JoinColumn(name = "parent_comment_id")
    protected Comment parentComment;

    @Column(columnDefinition = "TEXT", nullable = false)
    protected String content;

    @CreationTimestamp
    @Column(nullable = false, updatable = false, insertable = false)
    protected Instant creationDate;

    @Column(insertable = false, updatable = false)
    protected Integer votes;

    @Column(insertable = false, updatable = false)
    protected String path;
    
    /*accessor methods*/
}  

As you can see, aggregations are only tracked from the child's point of view: there are no @ManyToOne relationships due to a design choice.

However, I'd like to add a commentCount field to the Post entity. This field should be loaded eagerly (since it is always read in my use cases and a COUNT statement isn't that costly... i guess)

These are the options I'm currently aware of:

• Use the @Formula or the @PostLoad annotations: there would be N+1select statements

• Retrieve the posts, then call another repository method that uses the IN operator with all the retrieved posts as argument:

    select post, count(comment) from Post post, Comment comment where comment.post in (:posts) group by comment.post
  

  (Given that my data access layer might load even 50 posts at once, would a 50 parameters long in be optimal?)

• Create a non-mapped entity attribute, then take care of filling it manually in all repository methods involving Post with a join: i'd like to avoid this.

• Store commentCount in the database and mantain it with triggers: could be an option, but it's not the focus of this question (I'm not allowed to touch the schema)

What can be a valid solution? I'm using Hibernate but i prefer implementation-agnostic solutions (Implementation-specific options won't be ignored, though)

Answer 1

Using @Formula would not cause any N+1 select problem (different to @PostLoad), since the specified expression is evaluated as part of the initial select query.

(Infact, you would need to enable Hibernates byte code enhancement to be able to lazy-load the calculated @Formula property: Controlling lazy/eager loading of @Formula columns dynamically )

So even though it is a Hibernate-specific feature which needs native SQL, @Formula is probably a good and simple way to realize this.

In case the performance drawback of the correlated subquery gets significant and there are cases when the calculated property is not needed, you might additionally create a DTO projection only containing needed properties.