When using `sqlDelight` how do you usually combine...

# squarelibraries

[dimsuz]

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:

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

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?

[mbonnin]

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>>>

[dimsuz]

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 🙂

[kevin.cianfarini]

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.

[dimsuz]

VIEW won't help in this case, because one user can have many comments:

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...

[apsaliya]

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

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()
}

[dimsuz]

Ohhh, that's an interesting idea! 😏 Thanks, I'll try this sometime!