[FIXED] How to make one query to the database to find the date between, before, after and pageable?

Issue

If startDate not null and endDate not null = beetwen + pageable
If startDate is null and endDate not null = before + pageable
If startDate not null and endDate is null = after + pageable

I also wanted to use userIdentifier in the request, but I probably have to divide it into 2 requests when null and when with a value

I can't figure out how to come up with a request with these conditions

@Query("SELECT m FROM notification m WHERE :userIdentifier is null")
Flux<Notification> findAllByUserIdentifier(Timestamp startDate, Timestamp endDate, int page, 
int size);

Solution

you could try to short circuit your query like this:

@Query("SELECT n FROM notification n WHERE " +
"(:start is null or n.date >= :start) " + 
"and (:end is null or n.date <= :end) ")
Page<Notification> findAllByUserIdentifier(@Param("start") Timestamp startDate, @Param("end") Timestamp endDate, Pageable pageable);

Here we are checking for the conditions you want and using the filter only when the actual parameters are present, the Pageable parameter is an object that you will need to pass to the method to get a Page from the db. You can create that parameter like this: PageRequest.of(page, size) with page being the current page you want to query and size being the number of records to return.

The response Page will have the following methods for extracting the page values:

• getTotalElements gets the total element count for the query
• getContent gets the list of entities in selected by the query

Answered By - Fernando Bravo Diaz
Answer Checked By - Senaida (JavaFixing Volunteer)