springjpaspring-data-jpajpqlspring-projections

Spring Data JPA fetching list always returns at least a single result


I've noticed a slight problem with how my API is working where I'm using Spring Data JPA. My query looks something along the lines of:

@Query("SELECT p.id AS id, COUNT(l) AS likes FROM Post p LEFT JOIN Like l ON l.post = p WHERE p.location.id = ?1")

My actual query is bigger, this this contains everything necessary to explain what the issue is. This query will return a list, but assume the location does not exist, it should return null or an empty list, correct? Oh, how wrong you are, my sweet summer child! This query will instead always return a list of at least one element, regardless of whether or not there are any posts linked to said location.

[{"id": null, "likes": 0}]

That is what the result looks like when serialized to JSON. I am not quite sure what to do about this little predicament, as I obviously don't want to return a list with faulty data, but needing to use processing to filter out duds also seems dumb and unnecessary. Is there any way to prevent this that I've yet to find? If it is of any relevance, I am using projections currently for my responses.

What I've tried so far:

I haven't been able to find any other case which resembles this either, although it most likely exists, but is drowned out by everything else. I'm not quite sure what can be done in this regard, so I'm curious if it's just a quirk with the framework, or if there is an actual solution.

It might be possible to solve through native queries, but I would prefer not to use them.


Solution

  • As the issue is inherently due to a COUNT and CASE keyword in my real query, resulting in there always being at least one row, and I can't find any method of doing this automatically, the solution I've used is the following:

    List<Item> items = repository.customQuery(id);
    if (0 < items.size() && null == items.get(0).getId()) {
      items.remove(0);
    }
    

    The first condition is arbitrary as I know there is always at least one entry, but is done just as a safety measure. A try-catch block would do the trick as well. In the case where you use a primitive int instead of Integer, you'd need to initialize the value in the constructor to something which would normally never be present in the database, such as -1.

    If anyone knows of a better method, I'd love to know about it.