HQL/JPQL query with nested query and UUID array field-CodePudding

I need help with a HQL/JPQL query in Java Spring Boot project with Hibernate and Postgresql database.

Main entities are Student and Lesson (extra fields and annotations are ommited for brevity).

Student

Persistent entity:

@Entity
@Table(name = "student")
public class Student {

    @Id
    @Column(name = "id")
    private UUID id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "lesson_id")
    private Lesson lesson;

}

DB table:

create table student
(
    id         uuid,
    lesson_id  uuid
);

Lesson can be a part of different lesson cycles which are stored in uuid[] filed in DB.

Persistent entity:

@Entity
@Table(name = "lesson")
@TypeDefs({
        @TypeDef(
                name = "uuid-array",
                typeClass = UUIDArrayType.class
        )
})
public class Lesson {

    @Id
    @Column(name = "id")
    private UUID id;

    @Column(name = "name")
    private String name;

    @Type(type = "uuid-array")
    @Column(
            name = "lesson_cycle_ids",
            columnDefinition = "uuid[]"
    )
    private List<UUID> lessonCycleIds;

}

DB table:

create table lesson
(
    id               uuid,
    name             varchar(1000),
    lesson_cycle_ids uuid[]
);

I need a method List<Student> getStudents(UUID lessonCycleId) which returns list of students, which are linked to lessons with supplied lessonCycleId.

Now I ended up with below method in JPA repository:

@Query("FROM Student s WHERE s.lesson IN (SELECT les FROM Lesson les WHERE :id MEMBER OF les.lessonCycleIds)")
    List<Student> getStudents(@Param("id") UUID lessonCycleId);

but it doesn`t work. I have the following error on app startup:

Validation failed for query for method public abstract java.util.List ... StudentJpaRepository.getStudents(java.util.UUID)!

So I need help to fix the query please.

CodePudding user response：

I had no luck with JPQL so in the end I decided to use native Postgresql query instead:

@Query(nativeQuery = true, value = "select * from student where lesson_id IN (select id from lesson where :id = ANY (lesson_cycle_ids))")
List<Student> getStudents(@NonNull @Param("id") UUID lessonCycleId);