Entity field representing a count with conditions

Question

I have an @Entity Location which has a field :

• Set<Alert> openAlerts

Then I have an @Entity Alert which has two fields :

• AlertState state which is an enum (can be OPEN or CLOSED)
• Location originatedIn which is the location where the alert originated.

At the moment in Location I have :

@OneToMany(mappedBy = "originatedIn")
@JsonIgnoreProperties({"originatedIn"})
private Set<Alert> openAlerts;

It retrieves all the alerts linked to the location (open or closed).

I want to replace that field with Integer numberOpenAlerts. This new field should retrieve and count only the alerts linked to the location that have the AlertState of OPEN.

How can I do that ?

Answer 1

There are a few possibilities that I can think of to start off with.

Since you are using Hibernate, you could use the @Where annotation on your openAlerts to limit this Set to Alerts that are open:

@OneToMany(mappedBy = "originatedIn")
@JsonIgnoreProperties({"originatedIn"})
@Where(clause="state='OPEN'")
private Set<Alert> openAlerts;

depending on what the state column actually contains corresponding to the OPEN enum value.

See this question about @Where for some pointers.

Alternatively, you could create a database view with the location details and the count of the open alerts. Then you would create another Entity corresponding to that view called something like LocationWithOpenAlertCount.

Finally, you could create a DTO for the Location plus count and create a JPQL query that does a Constructor Select query with the appropriate location fields and the open Alert count. See this question and answer for an example.