SQLDelight select query with when clause where argument is List of Strings

Question

I have a sql query which selects data based on some filter values

getFiltered:
SELECT * FROM TaskEntity
WHERE
    CASE WHEN :searchWord IS NOT NULL THEN name LIKE '%' || :searchWord || '%' OR code LIKE '%' || :searchWord || '%' ELSE 1 END
    AND CASE WHEN :gId IS NOT NULL THEN gId = :gId ELSE 1 END
    AND CASE WHEN :assignedUserIds IS NOT NULL THEN assignedUserIds IN (:assignedUserIds) ELSE 1 END
    ORDER BY position ASC
LIMIT :take OFFSET :skip
;

and I want to select data based on assignedUserIds which is type of List<String>.

TaskEntity table has field assignedUserIds Text AS List<String>

when I'm passing list of ids to the getFiltered(assignedUserIds = listOf("1","2")) function it returns 0 records, instead of returning some records, cause some of them has values matching with that params. I'm assuming something is wrong with that line:
AND CASE WHEN :assignedUserIds IS NOT NULL THEN assignedUserIds IN (:assignedUserIds) ELSE 1 END

SQLDelight version = "1.5.3"
dialect = "sqlite:3.25"

Table definiton

CREATE TABLE IF NOT EXISTS `TaskEntity` (
`id` TEXT NOT NULL,
`code` TEXT NOT NULL,
`name` TEXT NOT NULL,
`sId` TEXT,
`mId` TEXT,
`position` INTEGER AS Int NOT NULL,
`assignedUserIds` Text AS List<String>,

PRIMARY KEY(`id`));

Answer 1

You seem to want to build a flexible prepared statement which has the ability to handle the case where one or more parameters may be null. You should use something like this version:

SELECT *
FROM TaskEntity
WHERE
    (name LIKE :searchWord OR :searchWord IS NULL) AND
    (gId = :gId OR :gId IS NULL) AND
    (assignedUserId IN :assignedUserIds OR :assignedUserIds IS NULL)
ORDER BY position;

Some comments:

I have reworked the logic such that either the parameter has to match the assertion or the parameter has to be left NULL. If the latter be the case, then that entire condition essentially no-ops, and the SQL parser will ignore it and check the remaining conditions.

To :searchWord you should bind a string representing the entire LIKE expression, e.g. if you wanted to find banana anywhere in the name column, you would bind %banana% from your code. As for :assignedUserIds, it should be valid to use that in an IS NULL assertion. But note that WHERE col IN (...) compares a single scalar value against a collection, not a collection against a collection.