Count and group with Hibernate Panache

Question

I have two tables and they are in One to Many relationship to each other. They are defined as the following:

@Entity
@Table(name = "accounts")
data class Account(
    @field:Id
    var id: UUID? = null,

    @ManyToOne
    var gender: Gender? = null,

    var birthday: Long = 0) {

  @ManyToMany(fetch = FetchType.EAGER, cascade = [CascadeType.MERGE])
  @JoinTable(
      name = "account_interests",
      joinColumns = [JoinColumn(name = "account_id")],
      inverseJoinColumns = [JoinColumn(name = "interest_id")]
  )
  var interests: Interests = emptyList()
}

@Entity
@Table(name = "genders")
data class Gender(
    @field:Id
    var abbr: Char? = null,
    var description: String = ""
)

I would like to select the data from databases and I did it first with SQL:

SELECT g.description, a.gender_abbr, count(a.gender_abbr)
from accounts a
         inner join genders g on g.abbr = a.gender_abbr
group by a.gender_abbr, g.description

the question is, how to translate the above SQL to hibernate panache HQL?

Answer 1

I suppose something like this could do it?

select g.description, g.abbr, count(p)
    from Account a
    inner join a.gender as g
    group by g.abbr, g.description