'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 |
|---|
