MySQL SELECT WHERE... AND (based on parameter value)-CodePudding

@Query(value =
            "SELECT * "  
                    "WHERE (type REGEXP ?1) "  
                    "AND status= ?2 "  
                    "AND date(createdAt)..."  
                    "ORDER BY id DESC "  
                    "LIMIT ?4",
            nativeQuery = true)
    Optional<List<Item>> findLatest(String type, String status, String date, int limit);

I have this query where I'd like to filter by createdAt, only if the date value from the parameter is not null.

If String date = null, the query should not consider the AND date(createdAt)...

However if String date = "today" the query should include something like AND date(createdAt) = CURRENT_DATE

How can I achieve this?

CodePudding user response：

Simplified you can make

If you have more choice you need case when

The idea is when the condition is not met, we let the and part always be true so that the where calsue is true if all the other conditions are met

SELECT * FROM
A
WHERE
    status= 72 
    AND IF( date = "today" , date(createdAt), current_date) = current_date

CodePudding user response：

The way we usually handle this is by adding a logical check to the WHERE clause which allows a null date.

@Query(value = "SELECT * "  
               // ...
               "WHERE (type REGEXP ?1) AND "  
               "      status = ?2      AND "  
               "      (DATE(createdAt) = ?3 OR ?3 IS NULL) "  
               "ORDER BY id DESC "  
        nativeQuery = true)
Optional<List<Item>> findLatest(String type, String status, String date);

Note that it is not possible to bind parameters to the LIMIT clause, therefore I have removed it. Instead, you should use Spring's pagination API.