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.
You can do this based on page table. With nativeQuery = true
@Query(value = "select case when ( count(*) > 0 ) then false else true end " +
"from page p " +
"where p.leaflet_id = ?1 and p.status <> 1 ", nativeQuery = true)
boolean allPagesFramed(UUID leafletId);
If a page has at least one status different from 1 (FRAMED), then the query return false, not all the pages are FRAMED.