Search code examples
javajpaspring-data-jpajpqlprojection

JPA Projection with custom collection property


We are using Spring Data and trying to create a custom query with a subquery, results projection have an array and other properties, our problem is with the subquery array.

    public interface ProfesionalRepository extends JpaRepository<Profesional, Long> {

    @Query("SELECT p.id as idProfesional, " +
            " p.name as name, " +
            " p.surname as surname, " +
            " (SELECT a.descripcionIlt FROM Ausencia a WHERE a.profesional.id = p.id) as exclusionesCenso " +
            " FROM Profesional p ")
    List<ProfesionalCensoProjection> findCenso();
}

Projection as:

public interface ProfesionalCensoProjection {
    Long getIdProfesional();
    String getName();
    String getSurname();
    List<String> getExclusionesCenso();
}

We get an error like this:

Caused by: java.sql.SQLException: ORA-01427: single-row subquery
returns more than one row

We found other posts like: Can Spring JPA projections have Collections?

But we cant find examples with subquery. If JPA doesn´t allow, which is the best solution for this problem?

Thanks,


Solution

  • You have not posted the entities however Profesional appears to have a relationship to Ausencia. You can then use nested projection:

     public interface ProfesionalCensoProjection {
        Long getIdProfesional();
        String getName();
        String getSurname();
        // returns a projection which would include only the description
        List<AusenciaSumaryprojection> getExclusionesCenso(); 
    }
    
    public interface ProfesionalRepository extends JpaRepository<Profesional, Long> {
    
        List<ProfesionalCensoProjection> findCenso();
    }