Simplify projection of a single field when using Panache with Hibernate

[DavideD]

With the current version of Panache with Hibernate, if we want to project a field from an entity, we need to do this:

import io.quarkus.runtime.annotations.RegisterForReflection;

@RegisterForReflection 
public class PersonName {
    public final String name; 

    public PersonName(String name){ 
        this.name = name;
    }
}

// only 'name' will be loaded from the database
PanacheQuery<PersonName> query = Person.find("status", Status.Alive).project(PersonName.class);

It seems a lot of work for returning a single field.

It would be nice ot have a way to specify a field:

PanacheQuery<String> query = Person.find("status", Status.Alive).project("name");

or, maybe,

PanacheQuery<String> query = Person.find("status", Status.Alive).project(Person.attribute("name", String.class));

For comparison, using HIbernate one can do:

session.createQuery("select name from Fruit ...")

This issue applies to both Panache with Hibernate ORM and Panache with Hibernate Reactive

Maybe we should also consider what happens when a user run a query like this:

List<String> distinctNames = Person.find("select distinct p.name from Person p").???.list();

One idea:

List<String> names = Person.distinct().find("status", Status.Alive).project("name", String.class).list()

WORKAROUND:
I haven't tested but it seems that this will work:

List<String> names = Person
    .find("select distinct name from Person where ...")
    .project(String.class)
    .list()

[quarkus-bot]

/cc @FroMage, @loicmathieu

[FroMage]

Can't you already write:

PanacheQuery<String> query = Person.find("select name from Person where status = ?1", Status.Alive);

[DavideD]

No, the signature of .find is

<T extends PanacheEntityBase> PanacheQuery<T> find(String query,  ... )

So it needs to be an entity class

[mkouba]

I once tried to introduce a more flexible PanacheQuery#project() method but was told that it's basically useless. The truth is that for your particular sample the usage would look like query.project("name", row -> row[0].toString()) which is not very ellegant 🤷

[DavideD]

No, I'm suggesting something different.
At the moment, when we use .project(...) underneath we are creating an HQL the looks like (using the person example):

select new PersonName(name) from Person

This seems unnecessary and force us to have a new class just for a field.
We should have something that allows the generation of the following HQL instead:

select name from Person

Personally, I don't want to create a new class every time I need a couple of fields from the entity.

The truth is that for your particular sample the usage would look like query.project("name", row -> row[0].toString()) which is not very ellegant

This doesn't work well with Person.findAll().project("name", row -> row[0].toString())). I can't tell where the different fields of Person will be in a row.

I think this would make more sense for SQL queries or more generic queries. But with Panache we query over an entity , so we know that row is actually on object of the type of the entity (Person in the example or PersonName when we use a projection).

[mkouba]

This seems unnecessary and force us to have a new class just for a field.

Yes, I know and I agree.

This doesn't work well with Person.findAll().project("name", row -> row[0].toString())). I can't tell where the different fields of Person will be in a row.

Well, in this API the row was actually not a "full row of an entity" but the row of the selected fields, i.e. the resulting query is something like select name from Person and the the row is an array of length 1 - therefore row[0] maps to the name.

[DavideD]

To be fair Hibernate already handle all these scenarios.

If one select a single field, it will return a list of the type of the field. Otherwise, it will return a List<Object[]>.

I think we should do the same:

List<String> names = Person.findAll().project("name").list();
List<Object[]> custom = Person.findAll().project("name", "status").list();

People can then handle the list the way they prefer.

[mkouba]

List names = Person.findAll().project("name").list();

But how do you derive the type of name?

[DavideD]

I'm not sure yet

[DavideD]

I mean, we can pass a parameter somewhere

[FroMage]

No, the signature of .find is

<T extends PanacheEntityBase> PanacheQuery<T> find(String query,  ... )

So it needs to be an entity class

That signature was a mistake, I've wanted to drop that constraint for a long time. We should really drop it.

[Serkan80]

It would have been nice if the the all args constructor requirement was not necessary for projections because this way we could directly use the models generated from OpenApi.