I am trying to create a method in the repository to be able to find out what is the most recent contract signed by an employee. The problem I have is that if I want to filter by the start date of the contract, I also have to pass it as a parameter and I don't have that, that's what I need. I tried this but the result is all contracts of this employee.

@Query(value = "SELECT * FROM Contracts c WHERE c.employeeName = :name AND c.dateStartContract = (SELECT MAX(dateStartContract) from Contracts d where d.employeeName = :name)") public Contract findByContractIDEmployeeName(@Param("name") String name);

CodePudding user response：

What you can do is to do a query to find all and sort it using descending order:

List<Contract> findAllByOrderByIdDesc();

Then you can just pick the first item of the list and that will represent the most recent contract signed.

You can also append so you can find by a specific name only:

List<Contract> findAllByOrderByIdDescAndName(String name);

It might be some slight error in my code since I have not had the opportunity to test it.

CodePudding user response：

The problem was that the import of @Query at the repository was import org.springframework.data.jdbc.repository.query.Query; and must be import org.springframework.data.jpa.repository.Query;. After this change, the consult to data base is exactly my Query request.

My query is this.

@Query(value = "SELECT c FROM Contract c WHERE c.contractID.employeeName = :name AND c.contractID.contractStartDate = (SELECT MAX(contractID.contractStartDate) from Contract d where d.contractID.employeename = :name)")public Contract findByContractIDNameEmployeeAndByContractIDDateStartContract(@Param("name") String name);