Davide Giuseppe Farella
03/17/2023, 6:39 PMCREATE 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 nullI’m pretty sure I’m doing something wrong
MovieTmdbId(123)
is transformed to "movie:123"
from the adapter, since a Movie and a TvShow could have the same id and I don’t really wanna cook some spaghetti in my data layerhfhbd
03/17/2023, 11:09 PMDavide Giuseppe Farella
03/18/2023, 6:12 AMfindById("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.hfhbd
03/18/2023, 12:02 PMDavide Giuseppe Farella
03/18/2023, 12:31 PM"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 Stringhfhbd
03/18/2023, 12:47 PMval id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!
Okay, could you create a reproducer and file a bug then?Davide Giuseppe Farella
03/18/2023, 12:54 PMI would still not prefixing the ids at db level, but simple return both and create a getter:Yes, that’s indeed what I did (see View declaration in the first post). The problem is querying them by id.val id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!
Okay, could you create a reproducer and file a bug then?Sure thing!