Limit selection of nested properties

[scordio]

I'm not sure if I should report this in spring-data-jpa or spring-data-commons. I apologize in advance if this isn't the right place.

I experienced the following issue on Spring Data JPA with Hibernate, initially with Spring Boot 2.7.14 and Db2 for z/OS, but also reproduced with the latest Spring Boot (both GA and snapshot) and H2.

In the Interface-based Projections section of both the Spring Data JPA and Spring Data Commons reference guides, there is an example on how to use such projections recursively:

interface PersonSummary {

  String getFirstname();
  String getLastname();
  AddressSummary getAddress();

  interface AddressSummary {
    String getCity();
  }
}

If I use this interface in a repository, like:

interface PersonRepository extends Repository<Person, UUID> {

  Collection<PersonSummary> findPersonSummaryByLastname(String lastname);
}

The following query is executed:

select p1_0.firstname,p1_0.lastname,p1_0.city,p1_0.street,p1_0.zip_code from person p1_0 where p1_0.lastname=?

where street and zip_code are also selected although they are not required.

This seems to contradict what is mentioned in the Closed Projections section:

If you use a closed projection, Spring Data can optimize the query execution, because we know about all the attributes that are needed to back the projection proxy.

As a workaround, if I remove the nested interface in PersonSummary and concatenate the property names:

interface PersonSummaryNoNested {

  String getFirstname();

  String getLastname();

  String getAddressCity();
}

the following query is executed:

select p1_0.firstname,p1_0.lastname,p1_0.city from person p1_0 where p1_0.lastname=?

Reproducer: PersonRepositoryTest

---

Is this a bug or is it by design?

In case the example with the nested interface is not meant to be considered as a closed projection, would it make sense to highlight this detail in the documentation?

[mp911de]

Currently, this behavior is by design as we use top-level properties for the selection. We could introspect the projection for nested projections to limit the selection.

For the time being, using inlined, concatenated properties makes sense. We can revisit this enhancement somewhere in the future.

[scordio]

Thank you for the feedback, @mp911de! Would you accept a small addition to the docs about it? I'd be happy to contribute.

Talking with colleagues, I realized this wasn't clear to many of them when looking at the PersonSummary example.

[christian-schlueter]

Would love to see this revisited in the future - if the nested entity has relationships itself that are not part of the projection, they will also get queried in separate queries, so this can result in a lot of wasted DB resources.

[mp911de]

@scordio sure, feel free.

[mp911de]

FWIW, I updated docs with #2757 to explain property limitations.

[scordio]

FWIW, I updated docs with #2757 to explain property limitations.

spring-data-jpa/src/main/antora/modules/ROOT/pages/repositories/projections.adoc

Lines 26 to 27 in 7f13a04

	Projections limit the selection to top-level properties of the target entity.
	Any nested properties resolving to joins select the entire nested property causing the full join to materialize.

Looks perfect to me. Thanks a lot!

[mp911de]

I explored nested element projections. The good news is, that it works for interface projections quite well. There are a few caveats:

• We cannot select a tuple in a multi-select as hibernate forbids that. So we need another carrier for nested object data.
• So far, I've been successful with using the entity itself as the carrier and populate properties using nested aliases. That works also nicely across multiple hierarchy levels.
• We have to resort to constructor expressions while the instantiated type is populated via properties. I think that's not intended usage, yet it works.
• Partially populating entities is a dangerous path, we've seen with Kotlin that we quickly hit limits as Kotlin types are properly designed when they are immutable and limit nullability. I do not have an answer to that issue.
• DTO's might be the final boss. We do not control DTO constructors and JPA doesn't have model metadata about a DTO.

That being said, it seems that there might be a way for nested properties decomposition using various roundtrips. I'm just not sure that things will work in all sorts of arrangements.

Given the complexity and uncertainty, I need to consult the team how we want to proceed.

[mp911de]

We've explored this area. Using CriteriaQuery, we can apply projections. However, with Spring Data JPA 4.0 we've switched to use JPQL, also for derived queries. Hibernate generates the following HQL from its CriteriaQuery:

select alias_1689020768.firstname, alias_1689020768.lastname, 
new org.springframework.data.jpa.domain.sample.User(alias_2019149967.firstname as firstname, alias_2019149967.lastname as lastname, 
      new org.springframework.data.jpa.domain.sample.User(alias_15395037.firstname as firstname, alias_15395037.lastname as lastname) as manager) 
from org.springframework.data.jpa.domain.sample.User alias_1689020768 
left join alias_1689020768.manager alias_2019149967 
left join alias_2019149967.manager alias_15395037 
where alias_1689020768.firstname like :firstname escape \

assuming we're querying User projected as NameAndManagerNameOnly:

interface NameAndManagerNameOnly {

	String getFirstname();

	String getLastname();

	NameAndManagerNameOnly getManager();
}

@Table(name = "SD_User")
class User {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO) private Integer id;
	private String firstname;
	private String lastname;


	@ManyToOne(fetch = FetchType.LAZY) private User manager;
	
	// …
}

Using that HQL for query creation fails in the first parse phase with duplicate aliases (although aliases are not duplicate within their scope). Removing aliases fails because constructors do not match; CriteriaBuilder Queries tend to use a different mechanism than constructors, so I expected that to work as well.

All in all, Hibernate is capable of returning object graphs from such a query. It would be neat if that kind of arrangement (also, using jakarta.persistence.Tuple instead of domain objects) would work. Maybe someone can reach out to Hibernate folks and convince them that would be a neat idea.

That being said, we decided not to follow up on that topic as we have other features that we want to ship and don't really have the bandwidth (and energy) to convince the Hibernate team that such an endeavor requires.

We can reopen that ticket once there is broader support across JPA providers.