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

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.

CodePudding user response：

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.