Aggregating Collections in Projections w. Quarkus/Hibernate

[ru4ert]

Hello everyone,

I'm working on a project using Quarkus with Hibernate Panache, and I've encountered an issue related to entity projection, particularly when dealing with @onetomany associations.

Here's a simplified version of my entity setup:

@Entity
class User {
  @OneToMany(mappedBy = "user")
  private List<ShoppingCartEntity> virtualShoppingCarts;
}

In this setup, a User entity is associated with multiple ShoppingCartEntity instances. For a specific user, let's say there are 3 associated virtualShoppingCarts.

To optimize my query performance, I'm using a projection to fetch user data:

@RegisterForReflection
public class UserProjection {
  public UserProjection(@ProjectedFieldName("virtualShoppingCarts") Object vsc) {}
}

And here's how I'm fetching the user:

var res = User.find("id.userId = :userId", Parameters.with("userId", userId))
                      .project(UserProjection.class)
                      .list();

Although I'm fetching a single user, the result (res) unexpectedly has a size of 3, presumably because of the 3 virtualShoppingCarts. However, my intention was to aggregate or collect these associated entities into a single projection instance.

Could someone guide me on how to correctly aggregate the @onetomany collection in the projection when using Quarkus/Hibernate/Panache? Is there a specific approach or pattern to achieve this?

Any advice or examples would be greatly appreciated. Thank you in advance for your help!

[quarkus-bot[bot]]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[ru4ert]

@FroMage 6 relations with x elements in.

[FroMage]

Ah, OK. Well, as far as I know, there's no way to do recursive projections in Hibernate ORM.

So you can make a User projection to leave some fields out, but you can't turn @OneToMany fields into their own projections in the same projection.

Is that correct, @gavinking @yrodiere @DavideD ?

[yrodiere]

Ah, OK. Well, as far as I know, there's no way to do recursive projections in Hibernate ORM.

So you can make a User projection to leave some fields out, but you can't turn @OneToMany fields into their own projections in the same projection.

Is that correct, @gavinking @yrodiere @DavideD ?

No idea.

@beikov?

[beikov]

Hibernate ORM only supports creating projections (through the use of the HQL constructor syntax) of flat results i.e. on projection object per result row. To map collection associations to into sub-projections, one will have to structure flat objects into hierarchical ones.

You can use Blaze-Persistence Entity-Views for this purpose though, which does all of that for you and also allows to configure fetch strategies for such collection associations to fine tune the performance even further.

[ru4ert]

Ok i will have a look at this. In native queries i can use the fns provided by a db.

[FroMage]

Thanks @beikov !