Get row from entity on hibernate with count and join

Question

I'm trying to get a field name from one table that has a join using hibernate. Does someone know how to get it? I'm using spring boot with EntityManager. The query is:

"SELECT COUNT(post_id), t.name" +
" FROM TagsPosts tp" +
" JOIN Tags t" +
" WHERE t.id = tp.tags_id" +
" GROUP BY tags_id" +
" ORDER BY 1 DESC"

I tried this code

        Query query = entityManager.createQuery(
                "SELECT COUNT(post_id), t.name" +
                " FROM TagsPosts tp" +
                " JOIN Tags t" +
                " WHERE t.id = tp.tags_id" +
                " GROUP BY tags_id" +
                " ORDER BY 1 DESC");
        query.setMaxResults(10);
        for (Object queryResult: query.getResultList()) {
        ...

But it is not working.

Answer 1

It depends on how the java object that you mapped to. In JPQL , you have to refer to the java object field name rather than the actual database column name. Assuming you have the following mapping :

@Entity
@Table(name= "Tag")
public class Tag {

    @Column(name="name")
    private String name;

    @OneToMany / @ManyToMany
    private List<Post> posts;
}

@Entity
@Table(name= "Post")
public class Post {


}

The JPQL is :

select tag.name , count(post)
from Tag tag
join tag.posts post
group by tag
order by count(post) 

The returned object is an Object[] which the 0th index is the tag name while 1st index is the number of post that is tagged with this tag name.

TypedQuery<Object[]> query = entityManager.createQuery(queryStr, Object[].class)    
query.setMaxResults(10);
for (Object[] queryResult: query.getResultList()) {
        //queryResult[0] is tag.name 
        //queryResult[1] is total count of the post for this tag
}