CODEWITHSUNDEEP
hibernatehql
Xeshan J
Xeshan J

Reputation: 372

Using distinct with Column Value within Constructor Query of HQL

I want to retrieve the records based on the distinct Lab Number from the database. The problem i have is there are multiple labs taken by patient and multiple lab numbers are stored in the joined tables.
Here is my query:

 Select new distinct org.laborders.dto.LabOrderDTO(ps.labNumber,ps.patientServiceId,
 ps,patientName,pso.patientServiceOrderId,pso.totalAmount) 
 from patientServices ps join patientServicesOrder pso

I want to retrieve unique records on ps.labNumber.

Upvotes: 1

Views: 1092

Answers (2)

Dominik
Dominik

Reputation: 36

I had a similar problem with "DISTINCT + Constructor" in @Query. The solution i found was rewriting the query like this:

Select new org.laborders.dto.LabOrderDTO(ps.labNumber,ps.patientServiceId,
 ps,patientName,pso.patientServiceOrderId,pso.totalAmount) 
 from patientServices ps join patientServicesOrder pso
GROUP BY ps.labNumber,ps.patientServiceId,
 ps,patientName,pso.patientServiceOrderId,pso.totalAmount

Using GROUP BY with all your select-columns has the same effect as using DISTINCT.

I am using Spring Data JPA and Hibernate.

Upvotes: 1

Waqar
Waqar

Reputation: 426

Have you tried like this -

Select distinct new  org.laborders.dto.LabOrderDTO(ps.labNumber,ps.patientServiceId,
ps,patientName,pso.patientServiceOrderId,pso.totalAmount) 
from patientServices ps join patientServicesOrder pso

Look i have placed the keyword distinct first and then new package.class.

Upvotes: 2

Related Questions