I have some queries in sequence to find a series of lists with rows whose id is into a first list but if they are not present into second list. I'm using JPQL, Spring Boot and Postgres so one of my query is similar to:
@Query("from MyObject m where (size(:ids)>0 and m.id in (:ids)) and (:size(:toExclude)>0 and m not in (:toExclude)")
List<MyObject> findAllAfterTwoChecks (List<>ids, List<MyObject>toExclude);
If one or both lists are empty, Hibernate returns nothing, because for it in(null) is false. How is it possibile to have all records if one or both lists are empty, in addition to these checks?
I tried with coalesce() function but I had a problem with indexes.
Thanks a lot.