Nullable list in where statement

[chason-choate-trilliant]

I'm struggling to find the correct way to do this. Any help would be appreciated.

From Kotlin I want to be able to do the following:

val incomingIDs: List<String>? = <could be a list or null>

myQueries.getResources(ids = incomingIDs)

where the SQL is something like:

SELECT *
FROM table
WHERE 
    id IN :ids OR :ids IS NULL

Is it possible to have a nullable list or should I look at creating a separate query for that case?

[AlecKazakova]

I'd recommend passing an additional parameter in and have something like

val incomingIDs: List<String>? = <could be a list or null>

myQueries.getResources(matchIds = incomingIds != null, ids = incomingIDs ?: emptyList())

SELECT *
FROM table
WHERE :matchIds IS FALSE OR id IN :ids

[chason-choate-trilliant]

Thanks, that's very helpful!

[vanniktech]

I do it like this WHERE (yatzyGame.variant IN :variants OR :variantsEmpty) and then in kotlin:

variants = filter.variants,
variantsEmpty = filter.variants.isEmpty(),