org.postgresql.util.PSQLException: ERROR: AND的参数必须是布尔类型，而不是记

Question

@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);

我在jpa存储库中有这个本机查询。如你所见，我搜索有user_id IN :userId的记录。只要userId只包含一个元素，就可以正常工作。但如果userId包含多个元素，则抛出org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record异常。同样的问题也发生在"AND (:status IS NULL OR (a.status IN :status)) "

user_id的类型为integer

status的类型为varchar

我很难找到解决这个问题的方法。你能帮我一下吗?而且，它是好的传递整数的列表作为字符串的查询列表通过转换每个元素的字符串，就像我做的userId在这种情况下?这是问题所在吗?我该如何解决?感谢您的宝贵时间!

Answer 1

org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type record.同样的问题也发生在"AND (:status IS NULL OR (a.status IN :status)) "

我认为用COALESCE(:status) IS NULL代替:status IS NULL可以解决以上问题。