#SqlDelight #SqlLite3-35 #Type Hello people. I have the following View:

CREATE VIEW IF NOT EXISTS screenplay AS
    SELECT
        movie.tmdbId AS movieTmdbId,
        NULL AS tvShowTmdbId,
        ...
    FROM movie
    UNION ALL
    SELECT
        NULL AS movieTmdbId,
        tvShow.tmdbId AS tvShowTmdbId,
        ...
    FROM tvShow;

where

movieTmdbId

and

tvShowTmdbId

are of types

sealed interface ScreenplayTmdbId 
value class MovieTmdbId : ScreenplayTmdbId
value class TvShowTmdbId: ScreenplayTmdbId

I would like to query a single row by id. It would be awesome if SqlDelight could infer the super-type

ScreenplayTmdbId

, but I see that’s not happening, as the following resolves

tmdbId

as String

WHERE IFNULL(movieTmdbId, tvShowTmdbId) == :tmdbId;

I tried then to separate into two separate queries, but, somehow, the first of the following queries compiles, while the second one doesn’t

findByMovieId:
    SELECT
        movieTmdbId,
        tvShowTmdbId,
        ...
    FROM screenplay
    WHERE movieTmdbId == :tmdbId;

findByTvShowId:
    SELECT
        movieTmdbId,
        tvShowTmdbId,
        ...
    FROM screenplay
    WHERE tvShowTmdbId == :tmdbId;

Caused by: java.lang.IllegalArgumentException: Cannot bind unknown types or null

I’m pretty sure I’m doing something wrong

First, sqldelight has no idea of Kotlin. Yeah, you could use import and as Value, but that's all. There is no source lockup, and I don't think so in the near future. I still don't understand your String problem.

Thank you for your answer. The problem with the string is that I could not call

findById("123")

as it could match two different entries, like a movie row with id

"movie:123"

and a tv show one with id

"tv:123"

. As now I kept two different queries:

WHERE tvShowId LIKE 'tv_' || :tmdbId || ''

and same for the movie counterpart, but still I'm duplicating logic in the adapter and in sqlite statement.

Honestly, I don't get it and it sounds kind of strange, why do you need to use 'like tv' or have different toString implementations? 😅 In the end, sqldelight is a db abstraction, and you should handle logic in your logic layer, not at the db layer.

I understand your thoughts. The fact is that on Tmdb, movies and tv shows are different entities, and a movie can share the same id of a tv show. In order to query them together, I created a View and resolved the conflicts by prefixing the ids by

"movie:"

or

"tv:"

. Now, since the two types of id are strongly typed in my domain, I don't need the prefix ( see

MovieId(123) != TvShowId(123)

). Because of this I decided to map

123 -> "movie:123"

in my db adapters: this might an arguable choice, but it seemed the most sensed one. TL;DR, the problem with the String is that the adapter would be bypassed. It would be great if the mentioned statement (last one in the main post) would compile as expected, allowing me to have 2 queries generated with the typed parameter, over a String

I would still not prefixing the ids at db level, but simple return both and create a getter:

val id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!

Okay, could you create a reproducer and file a bug then?

I would still not prefixing the ids at db level, but simple return both and create a getter:

val id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!

Yes, that’s indeed what I did (see View declaration in the first post). The problem is querying them by id.

Okay, could you create a reproducer and file a bug then?

Sure thing!