[JPA] counting rows in @ManyToMany relations

Three tables are given

SQL
CREATE TABLE posts (
  seq INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(256) NOT NULL,
  PRIMARY KEY (`seq`),
  ...
);

CREATE TABLE tags (
  seq INT(11) NOT NULL AUTO_INCREMENT,
  tag VARCHAR(64) NOT NULL,
  PRIMARY KEY (`seq`),
  ...
)
;

CREATE TABLE `tag_per_post` (
  post INT(11) NOT NULL,
  tag INT(11) NOT NULL,
  CONSTRAINT fk_post FOREIGN KEY (post) REFERENCES posts (seq),
  CONSTRAINT fk_tag FOREIGN KEY (tag) REFERENCES tags (seq)
)
;

• posts : tags = M : N
• tag_per_post(association table)

And two entities, Post and Tag are

JAVA
@Entity
@Table(name="posts")
public class Post {
    @ManyToMany
    @JoinTable(
        name="tag_per_post",
        joinColumns = @JoinColumn(name="post"),      //means tag_per_post.post
        inverseJoinColumns =@JoinColumn(name="tag")) //means tag_per_post.tag
    Set<Tag> tags;
}


@Entity
@Table(name="tags")
public class Tag {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer seq;
    
    @Column(name="tag")
    String text;
    
    @ManyToMany(mappedBy = "tags")
    Set<Post> posts;
}

• Post.tags - defines @ManyToMany relation
• Tag.posts - references mapping from Post.tags(mappedBy="tags")

JPQL to count the number of posts per tag

ITagDao.java will count the number of posts for all tags

ITagDao.java
@Repository
public interface ITagDao extends JpaRepository<Tag, Integer>{
    @Query("SELECT "
            + " new blog.domain.tag.Tag(t.seq, t.text, COUNT(p)) "
            + " FROM Post p "
            + " JOIN p.tags t"
            + " WHERE p.authorSeq = ?1"
            + " GROUP BY t.seq"
    )
    Set<Tag> countByPosts(Integer authorSeq);
}

• Tag(t.seq, t.text, COUNT(p)) - call constructor with seq, tagname(t.text) and the number of posts

JPQL @Query("SELECT ... GROUP BY t.seq") generates native query like this

SQL
    select
        tag2_.`seq` as col_0_0_,
        tag2_.`tag` as col_1_0_,
        count(post0_.`seq`) as col_2_0_ 
    from
        `posts` post0_ 
    inner join
        `tag_per_post` tags1_ 
            on post0_.`seq`=tags1_.`post` 
    inner join
        `tags` tag2_ 
            on tags1_.`tag`=tag2_.`seq` 
    where
        post0_.`author`=? 
    group by
        tag2_.`seq`

• generated by hibernate

For this to work, class Tag should have an constructor(Integer, String, Long)

Tag.java
public class Tag {

    // (1) the number of posts, no corresponding column in table tags
    @Transient
    Long count;

    // (2) default constructor
    public Tag() {
    }

    // (3) another constructor for rows per tag
    public Tag(Integer seq, String text, Long count) {
        this.seq = seq;
        this.text = text;
        this.count = count;
    }
}

• property Tag.count is not a column in table tags. It need to be @Transient not to be mapped by hibernate.
• default constructor(2) is required because Tag(Integer, String, Long) is defined explicitly.

Code

Tag.java
package blog.domain.tag;

import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.Transient;

import blog.domain.post.dto.Post;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Entity
@Table(name="tags")
@Getter
@Setter
@ToString
public class Tag {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer seq;
    
    @Column(name="tag")
    String text;
    
    @ManyToMany(mappedBy = "tags")
    Set<Post> posts;
    
    
    
    @Transient
    Long count;

    public Tag() {
    }

    public Tag(Integer seq, String text, Long count) {
        this.seq = seq;
        this.text = text;
        this.count = count;
    }
    
    public static Tag withText(String tagText) {
        Tag tag = new Tag();
        tag.setText(tagText);
        return tag;
    }
}

Post.java
package blog.domain.post.dto;

import java.time.Instant;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.Lob;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.commonmark.node.Node;
import org.commonmark.parser.Parser;
import org.commonmark.renderer.html.HtmlRenderer;
import org.hibernate.annotations.DynamicUpdate;
import org.hibernate.annotations.Type;

import blog.BlogException;
import blog.ErrorCode;
import blog.domain.tag.Tag;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name="posts")
@Getter
@Setter
@DynamicUpdate
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer seq;
    
    ...skip properties....
    
    @ManyToMany
    @JoinTable(
        name="tag_per_post",
        joinColumns = @JoinColumn(name="post"),
        inverseJoinColumns =@JoinColumn(name="tag"))
    Set<Tag> tags;
    
    ...
}

ITagDao.java
package blog.domain.tag;

import java.util.Set;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface ITagDao extends JpaRepository<Tag, Integer>{

    @Query("SELECT "
            + " new github.yeori.blog.domain.tag.Tag(t.seq, t.text, COUNT(p)) "
            + " FROM Post p "
            + " JOIN p.tags t"
            + " WHERE p.authorSeq = ?1"
            + " GROUP BY t.seq"
    )
    Set<Tag> countByPosts(Integer authorSeq);
}