@Query(value = "SELECT DISTINCT * "  
            "FROM chores WHERE id in (SELECT a.id "  
            "FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id "  
            "WHERE (a.is_deleted=FALSE "  
            "AND a.family_id=:familyId "  
            "AND (:userId IS NULL OR (cast(b.user_id as VARCHAR) IN :userId)) "  
            "AND (:status IS NULL OR (a.status IN :status)) "  
            "AND (:title IS NULL OR a.title LIKE cast(:title as text)) "  
            "AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)) "  
            "ORDER BY :sortByDeadline"  
            ", CASE WHEN :sortByDeadline THEN a.deadline END DESC "  
            ", CASE WHEN NOT :sortByDeadline THEN a.created_at END DESC)",
            countQuery = "SELECT COUNT(DISTINCT a.id) FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id "  
                    "WHERE a.is_deleted=FALSE "  
                    "AND a.family_id=:familyId "  
                    "AND (:userId IS NULL OR (cast(b.user_id as VARCHAR) IN (:userId))) "  
                    "AND (:status IS NULL OR (a.status IN (:status))) "  
                    "AND (:title IS NULL OR a.title LIKE cast(:title as text))"  
                    "AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)) ",
            nativeQuery = true)
    ArrayList<Chore> findAlLFilteredByUserAndStatusAndTitleSortedByCreatedAtOrDeadLine(@Param("familyId") int familyId,
                                                                                       @Param("userId") List<String> userId,
                                                                                       @Param("status") List<String> status,
                                                                                       @Param("title") String title,
                                                                                       @Param("sortByDeadline") boolean sortByDeadline,
                                                                                       @Param("from") String from,
                                                                                       @Param("to") String to,
                                                                                       Pageable pageable);

I have this native query in a jpa repository. As you can see, I search for the records which have user_id IN :userId. This works fine as long as userId contains only one element. But if userId contains more than one element, this exception will be thrown org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record. The same problem happens to "AND (:status IS NULL OR (a.status IN :status)) "

user_id has type integer

status has type varchar

I am having a hard time finding a way to fix this. Could you please help me with this? And also, is it good to pass a List of integers to the query as a list of string by converting every elements to string just like I do with userId in this case? Can it be the problem and how can I fix it? Thank you for your time!

CodePudding user response：

org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record. The same problem happens to "AND (:status IS NULL OR (a.status IN :status)) "

I think using COALESCE(:status) IS NULL instead of :status IS NULL will solve above problem.