'Single jpa @Query to return true/false when count in one table is equal to column value in other table

I have 2 entities: Leaflet and Page with One to Many relation (many Pages per Leaflet)

@Entity
Leaflet {
 @Id
 @GeneratedValue
 private UUID leafletId;
 private Integer noPages;
 @OneToMany(mappedBy = "leaflet", cascade = CascadeType.ALL, orphanRemoval = true)
 Set<Page> pages = new HashSet<>();
}

@Entity 
Page {
 @Id
 @GeneratedValue
 private UUID pageId;
 @ManyToOne(fetch = FetchType.LAZY)
 @JoinColumn(name = "leaflet_id")
 private Leaflet leaflet;
 @Enumerated
 private PageStatus status = PageStatus.CREATED;
}

and status enum

public enum PageStatus {
CREATED,
FRAMED

}

I would like to write single query to return whether all Pages for given Leaflet are already FRAMED. So I wrote this

@Repository
public interface PageRepository extends JpaRepository<Page, UUID> {

@Query("SELECT case when (COUNT(p) = l.noPages) then true else false end from Page p inner join Leaflet l on p.leaflet.leafletId = l.leafletId  where p.status = 1 and l.leafletId = ?1")
    boolean allPagesFramed(UUID leafletId);
}

but error comes which means I cannot use l.noPages directly

ERROR: column "leaflet1_.no_pages" must appear in the GROUP BY clause or be used in an aggregate function org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Is there a way to make it 1 query ? Of course, I can first select l.noPages with first hit to DB, then inject this value to above query (instead of join) which I'm doing right now as workaround.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source