JPQL count Parent Objects on Multiple Children Match in OneToMany Relationship

Question

In a JavaEE JPA web application, Feature entity has bidirectional ManyToOne relationship with Patient Entity. I want to write a query to count the number of Patients who have one or more matching criteria features. I use EclipseLink as the Persistence Provider.

For example, I want to count the number of patients who have a feature with 'variableName' = 'Sex' and 'variableData' = 'Female' and another feature with 'variableName' = 'smoking' and 'variableData' = 'yes'.

How can I write a JPQL query to get the count of patients?

After the first answer, I tried this Query does not give any results as expected.

public void querySmokingFemales(){
    String j = "select count(f.patient) from Feature f "
            + "where ((f.variableName=:name1 and f.variableData=:data1)"
            + " and "
            + " (f.variableName=:name2 and f.variableData=:data2))";
    Map m = new HashMap();
    m.put("name1", "sex");
    m.put("data1", "female");
    m.put("name2", "smoking");
    m.put("data2", "yes");
    count = getFacade().countByJpql(j, m);
}

The Patient entity is as follows.

@Entity
public class Patient implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    @OneToMany(mappedBy = "patient")
    private List<Feature> features;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }



    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Patient)) {
            return false;
        }
        Patient other = (Patient) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "entity.Patient[ id=" + id + " ]";
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Feature> getFeatures() {
        return features;
    }

    public void setFeatures(List<Feature> features) {
        this.features = features;
    }

}

This is the Feature Entity.

@Entity
public class Feature implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String variableName;
    private String variableData;
    @ManyToOne
    private Patient patient;



    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Feature)) {
            return false;
        }
        Feature other = (Feature) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "entity.Feature[ id=" + id + " ]";
    }

    public String getVariableName() {
        return variableName;
    }

    public void setVariableName(String variableName) {
        this.variableName = variableName;
    }

    public String getVariableData() {
        return variableData;
    }

    public void setVariableData(String variableData) {
        this.variableData = variableData;
    }

    public Patient getPatient() {
        return patient;
    }

    public void setPatient(Patient patient) {
        this.patient = patient;
    }

}

Oleksii Valuiskyi · Accepted Answer · 2019-10-01T08:18:29.267

2

For single feature counts you can use this

select count(f.patient) from Feature f where f.variableName=:name and f.variableData:=data

Two feature counts

select count(distinct p) from Patient p, Feature f1, Feature f2 
where 
  p.id=f1.patient.id and p.id=f2.patient.id and 
  f1.variableName=:name1 and f1.variableData:=data1 and 
  f2.variableName=:name2 and f2.variableData:=data2

Multiple feature counts solution is a bit tricky. org.springframework.data.jpa.domain.Specification can be used

    public class PatientSpecifications {
      public static Specification<Patient> hasVariable(String name, String data) {
        return (root, query, builder) ->  {
                    Subquery<Fearure> subquery = query.subquery(Fearure.class);
                    Root<Fearure> feature = subquery.from(Fearure.class);

                    Predicate predicate1 = builder.equal(feature.get("patient").get("id"), root.get("id"));

                    Predicate predicate2 = builder.equal(feature.get("variableName"), name);
                    Predicate predicate3 = builder.equal(feature.get("variableData"), data);

                    subquery.select(operation).where(predicate1, predicate2, predicate3);

                    return builder.exists(subquery);
        }
      }
    }

Then your PatientRepository have to extend org.springframework.data.jpa.repository.JpaSpecificationExecutor<Patient>

@Repository
public interface PatientRepository 
    extends JpaRepository<Patient, Long>, JpaSpecificationExecutor<Patient> {

}

Your service method:

@Service
public class PatientService {    

   @Autowired
   PatientRepository patientRepository;

   //The larger map is, the more subqueries query would involve. Try to avoid large map
   public long countPatiens(Map<String, String> nameDataMap) {
         Specification<Patient> spec = null;

         for(Map.Entry<String, String> entry : nameDataMap.entrySet()) {
            Specification<Patient> tempSpec = PatientSpecifications.hasVariable(entry.getKey(), entry.getValue());
            if(spec != null)
              spec = Specifications.where(spec).and(tempSpec);
            else spec = tempSpec;

         }

         Objects.requireNonNull(spec);

         return patientRepository.count(spec);        
    }
}

edited Oct 01 '19 at 08:18

answered Sep 30 '19 at 17:46

Oleksii Valuiskyi

2,691
1
8
22

It gives this error. Problem compiling [select count(f.patient) from Feature f where f.variableName=:name and f.variableData:=data]. [72, 87] The state field path 'f.variableData:' cannot be resolved to a valid type. [90, 94] The identification variable 'data' is not defined in the FROM clause. – Buddhika Ariyaratne Sep 30 '19 at 17:51
Sorry, It was a type. Now the query does not count records. – Buddhika Ariyaratne Sep 30 '19 at 17:53
Again it was my typing mistake. It works fine. Thank you. – Buddhika Ariyaratne Sep 30 '19 at 17:55
It works with one feature. But I need to search for two features. "sex" = "male" in one and "smoking"="yes" as stated in the question. – Buddhika Ariyaratne Sep 30 '19 at 17:58
Do you need only two features patients count or multiple features patients count? – Oleksii Valuiskyi Sep 30 '19 at 18:15
I want to count the patients who have both features. Like one feature "sex" = "female" and another feature "smoking" = "yes". If one patient have both, then that patient is counted as one. If another patient have "smoking"="yes", but "sex"="male" (NOT "female") is not counted. – Buddhika Ariyaratne Sep 30 '19 at 18:22
Multiple feature patient counts. – Buddhika Ariyaratne Sep 30 '19 at 18:23
Is this ever possible with JPQL? – Buddhika Ariyaratne Sep 30 '19 at 18:27
I have edited the answer. But I did not run, so it needs a revision – Oleksii Valuiskyi Sep 30 '19 at 18:47
Thank you. Will try to implement and will state the result. – Buddhika Ariyaratne Sep 30 '19 at 18:55
I tried my best and I think that it needs Spring JavaSpecifications. I do not use spring. Will try to see how it can be done. – Buddhika Ariyaratne Sep 30 '19 at 23:31
Yes, It needs Spring Data Jpa – Oleksii Valuiskyi Oct 01 '19 at 04:23
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/200208/discussion-between-alex-valuiskyi-and-buddhika-ariyaratne). – Oleksii Valuiskyi Oct 01 '19 at 04:28
This was very helpful. All the required features could be fulfilled with this one. – Buddhika Ariyaratne Oct 01 '19 at 07:09

score 1 · Answer 2 · answered Oct 01 '19 at 07:28

1

We also handled same situation for two feature and after extracting the IDs, we used a nested loops after and counting the number of common count. It was resource intensive and this two feature query in the answer helped a lot.

answered Oct 01 '19 at 07:28

Niluka Gunasekara

391
2
3

score 1 · Answer 3 · answered Oct 01 '19 at 08:05

1

May need to redesign the Class Structure so that querying is easier.

answered Oct 01 '19 at 08:05

Sunila SS

93
5

JPQL count Parent Objects on Multiple Children Match in OneToMany Relationship

3 Answers3

Linked