Returning only the last value of a table and filling a collection via JPQL

Question

I have the following tables in the database:

CLIENT

ID | NAME | CPF

BUYING

ID | DESCRICAO_ | DATA | ID_CLIENTE

the CLIENT table with the columns id, name and Cpf

the PURCHASE table with the columns id, value, date, id_client.

In java code the Customer class has a collection of purchases

I want to make a query via Spring data JPA that returns the pagination of the following DTO

public class ClienteDTO { 
    private Long id;
    private String nome;
    private Set<Compra> compras = new HashSet<>();


    public ClienteDTO(Long id, String nome, String descricaoItem, LocalDate data ){
        this.id = id;
        this.nome = nome;

        Compra compra = new Compra();
        compra.setDescricaoItem(descricaoItem);
        compra.setData(data);

        this.compras.add(compra);
    }       
}

In the DTO returned by the query I want the shopping collection to contain only the last purchase made by the customer according to the date. So far all I’ve been able to do is this consultation:

@Query(
  "SELECT NEW br.meupacote.ClienteDTO(c.id, c.nome, com.descricaoItem, com.data) 
  FROM Cliente c 
  LEFT JOIN c.compras com"
)
Page<ClienteDTO> findPage(PageRequest pageRequest);

In this case, a Clientedto is returned for each purchase made by the customer. I would like to return only one Clientedto per customer containing only their last purchase.

Answer 1

Good afternoon!

You could do it this way:

@Query(
  "SELECT NEW br.meupacote.ClienteDTO(c.id, c.nome, com.descricaoItem, com.data) 
  FROM Cliente c 
  LEFT JOIN c.compras com
  WHERE com.data = (SELECT max(compra.data) FROM Compra compra WHERE compra.id_cliente = com.id_cliente)"
)
Page<ClienteDTO> findPage(PageRequest pageRequest);

Explanation:

I just added a clause that checks whether the purchase has as date the longest purchase date for a particular customer.

With this the query will return only the information of a customer and his last purchase.