@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!
