Can somebody help to translate this MySql query to JPQL or HQL?

Don’t forget to check the image below

Objects:

1) Question

2) Tags

The relationship is ManyToMany

public class Question {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "question_has_tag",
        joinColumns = @JoinColumn(name = "question_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id"))
private List<Tag> tags;

public class Tag {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NotNull
@Column
private String name;
@ManyToMany(mappedBy = "tags", fetch = FetchType.LAZY)
@ContainedIn
private List<Question> questions;

The MySql query is following:

select t.id, t.name, count(question_has_tag.tag_id) as i from tag as t join question_has_tag 
on id = question_has_tag.tag_id group by id order by i desc;

Need to translate to JPQL or HQL

The main goal is to get list of the most frequency tags like:enter image description here

Answer

Try:

Query q = entityManager.createQuery("select t.id, t.name, count(t.id) from Tag t join t.questions group by t.id,t.name");
List<Object[]> res = q.getResultList();
for(Object[] row:res) {
    System.out.println(row[0]+" "+row[1]+" "+row[2]);
}

The given JPA query is translated to SQL (by Hibernate) as:

select tag0_.id as col_0_0_, tag0_.name as col_1_0_, count(tag0_.id) as col_2_0_ from Tag tag0_ inner join question_has_tag questions1_ on tag0_.id=questions1_.tag_id inner join Question question2_ on questions1_.question_id=question2_.id group by tag0_.id , tag0_.name