0

I have Seat and Booking JPA entities in one-many relation. I want to fetch Seat by id with Bookings with status "ACTIVE". If Seat doesn't have any Bookings with this status, Seat with empty Bookings list should be returned.

The following query works in SQL console:

select * from seats s left join bookings b on b.seat_id=s.id and b.status='ACTIVE' where s.id=1

I translated it to Spring Data JPA query:

@Query("select s from Seat s left join fetch s.bookings b on b.seat.id=s.id and b.status='ACTIVE' where s.id = :seatId")

But i got such error:

org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters
Sampeteq
  • 123
  • 8

1 Answers1

0

You cannot do this - Queries on Entity instances are for fetching the state of those entities as they are in the database, not for manipulating your java view of that data. If you only want the Active bookings for a seat, query for the active bookings. A better translation of your query might be:

"select s, b from Seat s left join s.bookings b on b.status='ACTIVE' where s.id = :seatId"

If your JPA provider doesn't like the join on clause, just move it to the where clause:

"select s, b from Seat s left join s.bookings b where s.id = :seatId and (b is null or b.status='ACTIVE')"

Note that this will return the same seat multiple times. It will be up to you to collect the seat and bookings instances and build them into a DTO if you need it.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • Both queries returned all bookings, active and cancelled – Sampeteq Jul 31 '23 at 18:39
  • The results should give you a list of Object[]- are you looking at the second element in each of these object[]s? The first will be the seat, the second will be the active booking that you want. If you look at the seat.booking collection, it will always be the full list of bookings as they are in the database . Don't use it, use the elements returned from the array instead. – Chris Aug 01 '23 at 13:56
  • Why list of Object[]? Why not Screening? I want Screening with Seats with Bookings – Sampeteq Aug 01 '23 at 15:14
  • What is a screening, an entity? If you want a java wrapper object for Seats and bookings, you will have to take them and build it from them. Or if there is only a single seat/booking pair expected from this query, use a constructor query "select new package.Screening(s, b) from ..." – Chris Aug 02 '23 at 13:09