Panache Query with distinct returns PanacheQuery<Entity> and not ArrayList<String>

Question

I'm trying to get a distinct result of one Column from my Database with Panache+Hibernate. Normally in Hibernate you would get an ArrayList<String> back from the query.

List<String> list = repo
            .find("select DISTINCT(a.country) from TMdBrandAll a order by a.country")
            .page(Page.ofSize(1000)).list();

But if I try this Approach with Panache I get the ErrorMessage Comiler Error Message

If I change the variable "list" to the returnType List<TMdBrandAll> the compile error is gone.

List<TMdBrandAll> list = brandAllRepo
            .find("select DISTINCT(a.country) from TMdBrandAll a order by a.country")
            .page(Page.ofSize(1000)).list();

When I now inspect the executed code in the debugger I get. Debugger output

How can I tell Panache that the outcome of the query will be an ArrayList<Strings> and not an ArrayList<PanacheEntity>?

Thanks for your answers

EDIT: Code of Repo:

@RequestScoped
@Transactional
public class BrandAllRepo implements PanacheRepositoryBase<TMdBrandAll, Long> {


    public void updateBrand(String brandName, String soundexCode, String countryCode, Long active, Long isNew, String user, Long brandAllId) {

        update("set brandName = ?1, soundexCode = soundex(pkg_util.f_escape_special_char1(?2))," +
                " countryCode = ?3, active = ?4, isNew = ?5, modifiedAt = sysdate, modified_by = ?6 where brandAllId = ?7",
            brandName, soundexCode, countryCode, active, isNew, user, brandAllId);

    }

}

Working Code from Repo:

@Inject
EntityManager em;

public List<String> findCountries() {
    List<String> qres = em
        .createQuery("select DISTINCT(a.countryCode) from TMdBrandAll a order by a.countryCode", String.class)
        .getResultList();

    return new ArrayList<>(qres);
}

With injected EntityManager and standard hibernate query it works.

Answer 1

This is a limitation of Panache.

Have a look at the code https://github.com/quarkusio/quarkus/blob/master/extensions/panache/hibernate-orm-panache/runtime/src/main/java/io/quarkus/hibernate/orm/panache/PanacheRepositoryBase.java

It always returns a List of the entity.

Either create a finder method in BrandAllRepo that returns a List of Strings or use an untyped list:

List list = brandAllRepo
        .find("select DISTINCT(a.country) from TMdBrandAll a order by a.country")
        .page(Page.ofSize(1000)).list();

You know that there will be Strings in the list.

The second option is not so nice. I would use the first option.

Answer 2

I was running in the same problem, but I figured that it works if I use .project(String.class) and GROUP BY instead of DISTINCT:

List<String> list = repo
            .find("select a.country from TMdBrandAll a " 
                   + "group by a.country "
                   + "order by a.country")
            .project(String.class) 
            .page(Page.ofSize(1000)).list();

It seems that Panache gets confused by the DISTINCT clause.