How to get all of one entity but filtered relation in JPQL

246
September 13, 2017, at 9:47 PM

I have a OneToMany relation between to entities: Area and Reservation. Area has many reservations (or none) and Reservation has one Area. I would like to query all Area's but for each area, I would only have the Reservations which fullfill a condition.

So far I've tried something like this:

em.createQuery("SELECT DISTINCT a FROM Area a LEFT JOIN a.reservations r WHERE r.startDate < :fromDate")
                .setParameter("fromDate", fromDate, TemporalType.TIMESTAMP)
                .getResultList();

But then it will not return those Areas which does not have any Reservations that fullfill the condition.

Answer 1

This is because your query has a Where clause, that doesn't return the records which is present in Area but not in Reservation.

Here you want something that possibly JPA doesn't support an ON clause instead of Where.

JPA JPQL does not support arbitrary relationships between object being defined in the "on" clause

So what could be the solution for this:

Using SQL Query instead of JPQL.

Try something using below based on your objects tables and field value:

em.createSQLQuery("SELECT DISTINCT a.area_name FROM area a LEFT JOIN a.reservations r ON r.start_date< :fromDate")
                .setParameter("fromDate", fromDate, TemporalType.TIMESTAMP)
                .getResultList();
READ ALSO
Java UI thread blocked - Out of Memory error

Java UI thread blocked - Out of Memory error

In my application, I get messages every second, which contains the altitude, speed and headingEach time, I get the message, I need to generate a symbol (jpg) and show it in UI of the desktop application

258
why method called two times on changing styleClass condition

why method called two times on changing styleClass condition

This is my snipped part of xhtmlI have on condition changing styleClass

138
Doc file to Html convert using google-api

Doc file to Html convert using google-api

I am converting adoc file to the

233