Projections on collection properties are not distinct [DATAJPA-1786]

[spring-projects-issues]

Sebastian Staudt opened DATAJPA-1786 and commented

Given the following simple domain models:

public class Host {

    List<Alias> aliases;

    long id;

    String name;

    public List<Alias> getAliases() {
        return aliases;
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

public class Alias {

    String name;

    public String getName() {
        return name;
    }
}

I'd expect the following projection to do "the right thing":

public interface HostProjection {

    List<String> getAliasesName();

    String getName();
}

Instead it loads a HostProjection proxy for each Alias. Using a distinct query method does not work here as the rows fetched are distinct. I'd expect some magic to happen before the data is transformed into the projection proxies. It seems I was wrong.

I also tried adding a @Id annotated getId() method to the projection, but this doesn't change the behavior.

Using an open projection with the following @Value annotation and a distinct query method works, though:

@Value("#{target.aliases.![name]}")
List<String> getAliases();

I understand that this is operating on a higher level, so this won't help much. It's just a workaround with the overhead of open projections

---

Issue Links:

• DATAJPA-1785 Collections cannot be used in dto projections

1 votes, 6 watchers

[spring-projects-issues]

Oliver Drotbohm commented

What is "the right thing" here? Who is it? What does the repository query look like? I am also not getting "proxy for each alias". Isn't HostProjection supposed to project on Host instances?

[spring-projects-issues]

Sebastian Staudt commented

Sorry for not being very explicit about my problem, here are some more details that hopefully answer your questions.

There are various query methods in the repository like e.g.:

List<HostProjection> findAllProjectedOrderByName();
List<HostProjection> findTop100ProjectedOrderByName();

I would expect this to return HostProjection instances for each Host object in question. Instead I get instances for each Alias linked to the hosts.
The query to the database is a LEFT JOIN like this:

SELECT
    h.id,
    h.name,
    a.name
FROM
    hosts h
LEFT OUTER JOIN
    aliases a
ON
    h.id = a.host_id
ORDER BY
    h.name;

The result of course contains duplicate rows (in regard of the Host objects):

ID     NAME          NAME   
------ ------------- ------ 
160425 host123       (null) 
8398   host456       (null) 
34566  host1         alias1
34566  host1         alias2

While JPA / Hibernate merge these into distinct Host objects with all their aliases, the projections will be created purely on the returned table rows.
I tried to track this down to the ResultProcessor, but am unable to find a real solution to the problem

[spring-projects-issues]

Kamil commented

I think I have simmilar problem.
As stated in this wise article we should use DTOs (Projections) when we want to query specific parts of Entity instead eagerly loading all fields and collections.

So I have a large entity:

public class User {
    private String login;
    /*
    many more fields and collections here
    */
    private Set<Role> roles;
}

And in UI I have a table that displays just User's roles.
It would be ineffictient to make collection of Roles Eager.
Using EntityGraph is ok, but you still load whole entity while you just need Roles.
Then Projections come in! Having this Projection:

public interface UserRoleDto {
    String getLogin();
}
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<UserRoleDto> findRolesDtoById(Long id);
}

projection works as expected - it just "SELECT login FROM user", but when I modify Projection to contain collection of Role as well:

public interface UserRoleDto {
    String getLogin();
    Set<Role> getRoles();
}

I expect it to return Dto for this single User with eagerly loaded collection. Instead I get:

javax.persistence.NonUniqueResultException: result returns more than one elements

Projections are savior from OpenSessionInView pattern and ugly LazyInitializationExceptions... Could you please make projections to behave well and eagerly load requested collections as well?
Thank you in advance

[spring-projects-issues]

Kamil commented

I think this post could be related to the issue: https://vladmihalcea.com/2017/09/13/the-best-way-to-fix-the-hibernate-hhh000104-firstresultmaxresults-specified-with-collection-fetch-applying-in-memory-warning-message/

[spring-projects-issues]

Mark Paluch commented

Moved to Spring Data JPA since Spring Data JPA controls how the actual query gets created. Other modules (e.g. Spring Data MongoDB) follow the object graph/document representation and don't suffer from this limitation