When using `sqlDelight` how do you usually combine...
# squarelibraries
d
When using
sqlDelight
how do you usually combine relations to keep everything reactively observable? Let's say I have a
User
and
Comment
tables, each user has many comments. And now I build a list of users with comments:
Copy code
val users: List<User> = database.userQueries.select_all { userId, userName ->
  val comments = database.commentQueries.find_by_user_id(userId).executeAsList()
  User(userId = userId, name = userName, comments = comments)
}.executeAsList()
But now I want to create a
Flow<List<User>>
. I have no problem doing
Copy code
val usersFlow = database.userQueries.select_all(...).asFlow()
But this won't emit if individual comment changes, won't it? So I need to do
asFlow
on each individual user's "comment"-query and then flatMap them, this gets complicated suddenly. Is there a nicer way?
m
I've done stuff with
combine
and
flattenMerge
previously but yea that took a while to wrap my head around all the Flow<List<Flow<T>>>
d
Yeah, it's doable, even in several ways, with different operators, it's just that every time I'm faced with this again, I silently wonder if there's a better way ­čÖé
plus one 1
k
This is dependent on how you're using this data, but could a
VIEW
help? Expose a flat structure of the comment data and the user identifier info you need. Then just observe the flow of
UserAndComment
or whatever.
d
VIEW
won't help in this case, because one user can have many comments:
Copy code
data class User(val comments: List<Comment>)
You could join comments and have repeated user rows with same id and different comments, but then you'd have to do
groupBy
in kotlin code which is not what I'd like to do in this case...
a
What I generally do is I write a query involving all tables (doesn't matter what rows it selects) and then just initiate flow on it without executing. You can apply mapper on the resulting flow and do actual queries there. Something like this
Copy code
notificationQuery: SELECT * FROM USER JOIN COMMENT ON USER.id = COMMENT.userId
// this query will only be used for observing changes
...
...
notificationQuery.asFlow() //query won't be executed but you still get emissions when USER or COMMENT changes
.map {
val users: List<User> = database.userQueries.select_all { userId, userName ->
  val comments = database.commentQueries.find_by_user_id(userId).executeAsList()
  User(userId = userId, name = userName, comments = comments)
}.executeAsList()
}
d
Ohhh, that's an interesting idea! ­čśĆ Thanks, I'll try this sometime!