CODEWITHSUNDEEP
javaspringhibernatejpaspring-data-jpa
msp
msp

Reputation: 3364

Using JPA is empty in "or" statement

I'm building a JPA query using spring-data-jpa's @Query annotation and can't get it working. I have two classes class (setters/getters stripped)

public class Article {
    @Id
    @GeneratedValue
    private Integer id;

    @OneToMany(cascade = CascadeType.ALL)
    @OrderColumn(name = "review_index")
    private List<Review> reviews;
}

and

public class Review {
    @Id
    @GeneratedValue
    @Column(unique = true, nullable = false)
    private Integer id;

    @Column(name = "review_state")
    @Enumerated(EnumType.STRING)
    private ReviewState state;  // simple enum
}

What it should do: the query should return all Article with either no review at all or with the Review with the highest index in the collection having the state PASSED.

@Query("select distinct article from Article article "
    + "left outer join article.reviews r " // outer join to include articles without review
    // be sure to be either unreviewed or passed the review
    + "where (article.reviews is empty or article.reviews[maxindex(article.reviews)].state = 'PASSED'))"

What it actually does: The problem is that Articles with no review at all get excluded from the result. It seems as if article.reviews is empty isn't honoured at all. On a sidenote: is empty works if the second part of the or statement is left out.

Upvotes: 2

Views: 1566

Answers (1)

mh-dev
mh-dev

Reputation: 5503

Answer extracted from the Comments:

The core issue was related to the implicit join (not left join) of the path navigation "article.reviews". This can be bypassed with a dedicated sub select for this part.

select distinct article 
from Article article
left outer join article.reviews r 
where article.reviews is empty or 
    exists(
        select subArticle 
        from Article subArticle 
        where article.id = subArticle.id 
        and subArticle.reviews[maxindex(subArticle.reviews)].state = 'PASSED'
    )

Upvotes: 2

Related Questions