CODEWITHSUNDEEP
javasqlspringspring-data-jpa
Wojciech Abacki
Wojciech Abacki

Reputation: 251

Only last record from Custom query

The following query return a list but I am only interested in the last element of the list. 

@Query("SELECT r FROM Reservation r WHERE r.reservationSeance.id=:seanceId AND r.seanceDate=:seanceDate")
public Reservation findReservationBySeanceDateAndSeanceId(@Param("seanceId") int seanceId, @Param("seanceDate") java.time.LocalDate seanceDate);

How shall I rewrite the SQL-Query in order to implement my idea?

Upvotes: 1

Views: 822

Answers (3)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59960

One possible solution is to use ORDER BY r.id DESC :

@Query("SELECT r FROM Reservation r  " +
        "WHERE r.reservationSeance.id=:seanceId AND r.seanceDate=:seanceDate " +
        "ORDER BY r.id DESC")
public Reservation findReservationBySeanceDateAndSeanceId(
        @Param("seanceId") int seanceId,
        @Param("seanceDate") java.time.LocalDate seanceDate, Pageable pageable);

and because there are no way to use limit in JPQL, you can use Pageable

Pageable pageable = new PageRequest(0, 1);
Reservation reservation = r.findReservationBySeanceDateAndSeanceId(seanceId, seanceDate, pageable);

Another possible solution without Query :

public Reservation findTop1ByReservationSeanceAndSeanceDateOrderByIdDesc(
               ReservationSeanceEntity reservationSenace, 
               java.time.LocalDate seanceDate
)

In this second solution you have to pass the ReservationSeance Object and not the id of ReservationSeance, the query can be read as :

Find top 1 (first one) by `ReservationSeance` and `SeanceDate` order by `Id` Desc order

Upvotes: 1

amit rawat
amit rawat

Reputation: 783

You can try the following, if you are using mysql as your database:

SELECT r 
FROM Reservation r 
WHERE r.reservationSeance.id=:seanceId 
  AND r.seanceDate=:seanceDate 
order by r.reservationSeance.id desc limit 0,1

Upvotes: 1

Zephyr
Zephyr

Reputation: 10253

You need to provide a couple more parameters to your query, especially an ORDER BY clause.

To get the latest seanceId, you'll want to order your results by that id, but in reverse order. Then, just tell the query to return only the first result:

SELECT r FROM Reservation r 
WHERE r.reservationSeance.id=:seanceId 
AND r.seanceDate=:seanceDate 
ORDER BY seanceId 
DESC LIMIT 1;

Upvotes: 1

Related Questions