EJBQL - Find all rows that have more than 'x' relationships

Question

I know there must be a simple way to do this, but it's not coming to me at the moment. I've got a simple entity like (irrelevant parts removed for brevity):

@Entity
@Table(name = "reviews")
public class Review {
    private String text;
    private boolean approved;
    private Collection<ReviewFlag> flags;

    public Review() {
        this.text = null;
        this.approved = false;
        this.flags = Collections.emptyList();
    }

    @Column(nullable = false)
    public String getText() {
        return text;
    }

    @Column(nullable = false)
    public boolean isApproved() {
        return approved;
    }

    @OneToMany(mappedBy="review")
    public Collection<ReviewFlag> getFlags() {
        return flags;
    }
}

I want to run a query that finds every unapproved review with two or more flags. Something like:

SELECT r FROM Review r WHERE r.approved = 0 AND COUNT(r.flags) > 1

...but of course that syntax doesn't work (hibernate complains about syntax). I've tried it as:

SELECT r FROM Review r WHERE r.approved = 0 HAVING COUNT(r.flags) > 1

...that one runs, but also returns reviews that have 0 or 1 flags. What's the magic invocation I need to use to find only the ones with 2 or more flags?

Answer 1

Because of annotations I assume you operate in such an environment where JPQL can be used instead of EJB QL. If so, then SIZE operator is there for this kind of purpose. It takes path to collection as argument and returns number of elements.

In your case JPQL query is:

SELECT r FROM Review r WHERE r.approved = 0 AND SIZE(r.flags) > 1

Answer 2

I got it:

SELECT DISTINCT(f.review) FROM Flag f WHERE f.review.hidden = 0 AND f.review.approved = 0 GROUP BY f.review HAVING COUNT(*) > 1

A bit more obtuse than I'd like, but it works.

Note to self: stop answering own questions.