[FIXED] Retrieve all details with least timestamp using java 8

Issue

Hello I working on a scenario, where I need the latest timestamp data based on Currency using Java 8.

Suppose I'm requesting for USD currency then I should get all USD data with Least timestamp for all date.

class Currency{
   private Integer id;
   private String name;
   private LocalDateTime lastReceived;
}

Record In Database As below.

ID NAME  LAST_RECEIVED
-- ----  -------------
1  USD   18-MAY-22 09.04.01.545899000 AM
2  USD   18-MAY-22 08.04.01.545899000 AM
3  USD   19-MAY-22 08.04.01.545899000 AM
4  USD   20-MAY-22 08.04.01.545899000 AM
5  USD   20-MAY-22 11.04.01.545899000 AM
6  BUSD   18-MAY-22 08.04.01.545899000 AM

Expected:
ID NAME  LAST_RECEIVED
-- ----  -------------
1  USD   18-MAY-22 09.04.01.545899000 AM
3  USD   19-MAY-22 08.04.01.545899000 AM
5  USD   20-MAY-22 11.04.01.545899000 AM

I tried, Suppose I requested for USD Data,

List<Currency> listCurrency = repo.getAllDataBasedOnCurrency("USD");

listCurrency.stream().sorted(Comparator.comparing(Currency::getLastReceived).reversed()).collect(Collectors.toList());

// But here I want latest received Data for a single date.

Solution

It appears that you should group by currency AND date and then select by maximal (most recent) timestamp, this results in a map, and then you can use the map's values() to build the list.

So, a list of currency name and the date can be used as the key in the map, and Collectors.collectingAndThen is needed to convert the Optional result of Collectors.maxBy into the currency instance:

List<Currency> lastByDate = new ArrayList<>(data
        .stream() // Stream<Currency>
        .collect(Collectors.groupingBy(
            // key = List<Serializable & Comparable<? extends Comparable<?>>>
            curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
            Collectors.collectingAndThen(
                Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                Optional::get // Optional<Currency> -> Currency
            )
        )) // Map<List<...>, Currency>
        .values()
);

If a sorted list is required, it may be retrieved by sorting the stream of values:

List<Currency> sortedByDateAndCurr = data
        .stream()
        .collect(Collectors.groupingBy(
                curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
                Collectors.collectingAndThen(
                        Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                        Optional::get
                )
        ))
        .values()
        .stream()
        .sorted(
            Comparator.comparing(Currency::getLastReceived)
                      .thenComparing(Currency::getName)
        )
        .collect(Collectors.toList());

or just by sorting the unsorted list:

lastByDate.sort(
    Comparator.comparing(Currency::getLastReceived)
              .thenComparing(Currency::getName)
);

---

For a specific currency (e.g. USD) the implementation should be changed slightly to include filtering by the currency name then a simpler key should be created in the map:

List<Currency> lastUSDByDate = new ArrayList<>(data
    .stream()
    .filter(curr -> "USD".equalsIgnoreCase(curr.getName()))
    .collect(Collectors.groupingBy(
        curr -> curr.getLastReceived().toLocalDate(),
        Collectors.collectingAndThen(
            Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                Optional::get
        )
    ))
    .values()
);
lastUSDByDate.sort(Comparator.comparing(Currency::getLastReceived));

---

Similar result may be achieved by building a native SQL query using a window function to get the most recent value per date. JPA does not seem to support yet the window functions.

An example for PostgreSQL

// some JPA CurrencyRepository
@Query(nativeQuery = true, value = """
    SELECT id, name, last_received
    FROM (
        SELECT c.*,
        ROW_NUMBER() OVER (
            PARTITION BY name, to_char(last_received, 'yyyy-MM-dd')
            ORDER BY last_received DESC
        ) AS rr
        FROM Currency c
        WHERE c.name = :currName
    ) tbl
    WHERE rr = 1
    ORDER BY last_received
""")
List<Currency> findLastByDateByCurrencyName(@Param("currName") currName);

Answered By - Nowhere Man
Answer Checked By - Mary Flores (JavaFixing Volunteer)