Q: NamedQuery for most used tags Let's say I have the following 2 classes:
@Entity
class Article {

    @Id
    int id;

    @ManyToMany(mappedBy="articles")
    List<Tag> tags;
}

@Entity
@NamedQueries({
    @NamedQuery(name = "Tag.trends", query = "SELECT t FROM Tag t") // Some magic stuff here..
})
class Tag {

    @Id
    String name;

    @ManyToMany
    List<Articles> articles;
}

Now I want to create a named query to select the most used tags.
So this will require a join, count and an order by. But I can't seem to figure this out.
Question:
How can I select the most used tags?
For example, if the following articles have the following tags:
Article: Tags
0: A, B
1: A
2: C
3: A, C

Output
A // 3 times
C // 2 times
B // 1 time


A: You can achieve what you want using something like
SELECT t FROM Tag t ORDER BY SIZE(t.articles) ASC

That would bring you the list of tags ordered by number of articles.

A: Work your way backwards on these kinds of problems. Start with the SQL query:
select 
  count(t.name), t.name 
from article a 
  join article_tags ats 
  join tag t 
where 
  a.id=ats.article_id 
  and ats.tag_id = t.id 
group by 
  t.name 
order by 
  count(t.name) 
desc;

Then the JPQL should more or less follow from that:
List<Tuple> t = em.createQuery("select t.name as name, count(t.name) as count from Article a join a.tags t group by (t.name) order by count(t.name) desc", Tuple.class).getResultList();
t.stream().forEach((tp)-> {
    System.out.println("Count of " + tp.get("name") + " = " + tp.get("count"));
}); 

This gives me the following output:
Hibernate: select tag2_.name as col_0_0_, count(tag2_.name) as col_1_0_ from Article article0_ inner join Tag_Article tags1_ on article0_.id=tags1_.articles_id inner join Tag tag2_ on tags1_.tags_id=tag2_.id group by tag2_.name order by count(tag2_.name) desc
Count of A = 2
Count of B = 1
Count of C = 1

UPDATE: If you want a list of tags, just change the first element of the tuple to be a tag. And of course you can ignore the count if you want:
List<Tag> t = em.createQuery("select t from Article a join a.tags t group by (t.name) order by count(t.name) desc", Tag.class).getResultList();
t.stream().forEach((tag)-> {
    System.out.println("Tag = " + tag.getName());
}); 

And get the following output:
Hibernate: select tag2_.id as id1_1_, tag2_.name as name2_1_ from Article article0_ inner join Tag_Article tags1_ on article0_.id=tags1_.articles_id inner join Tag tag2_ on tags1_.tags_id=tag2_.id group by tag2_.name order by count(tag2_.name) desc
Tag = A
Tag = B
Tag = C

