IssueTracker

there are some image for help

hello?

By the way, I currently used a workaround for this query.

    @OptIn(ExperimentalCoroutinesApi::class)
    fun readRoutesClients(): Flow<List<RouteClients>> = readRoutes().flatMapLatest { routes ->
        combine(
            routes.map { route ->
                clientsDao.readRouteClients(route.id).map {
                    RouteClients(
                        route = route,
                        clients = it
                    )
                }
            }
        ){
            it.toList()
        }
    }.distinctUntilChanged()

Are there any updates?

Hey - Sorry for the late response.

The TL;DR is that you should use a Map return type instead of the @Relation functionality in Room since you are trying to apply a filter (a WHERE clause) to one of the relations which is not currently supported. A multimap return type for your test that returned the expected result is:

    @Query("SELECT * " +
            "FROM Routes " +
            "LEFT JOIN ClientRoutes ON Routes.routeId = ClientRoutes.refRouteId " +
            "LEFT JOIN Clients ON ClientRoutes.refClientId = Clients.clientId " +
            "LEFT JOIN ClientRouteDays ON ClientRoutes.id = ClientRouteDays.clientRouteId " +
            "WHERE ClientRouteDays.day = :day " +
            "OR ClientRouteDays.day IS NULL ")
    fun readRouteClientsMap(
        day: String = Clock.System.dateToday.day
    ): Flow<Map<Route, List<Client>>>

The actual root of the problem is that Room actually makes an additional query when fetching relations via @Relation and it doesn't take into account the original query for which you have a WHERE clause. Essentially what occurs is you query all the right rows, but when Room is building the RouteClientsRelation object, it maps all the Route objects from the result rows that have been correctly filtered, i.e. only the right routes of the day will be in the result, but when fetching the clients, Room perform a second query, it will look like ... FROM ClientRoutes INNER JOIN Clients ... and it will simply try to match all Route objects to Clients via the junction table ClientRoutes but it will no longer take into account your ClientRouteDays junction table or the date filter in the WHERE clause.

In general @Relation is a shortcut for simple relations, no filters (WHERE), no sorting (ORDER BY), no grouping (GROUP BY), for all these use-cases multimap return types is the best approach.

I will close this issue as 'intended behavior' but please feel free to reply with more questions or if the suggestion is not working as you expect it to.

Thank you for the response. I'll try that out. Would this also be applicable for something like Flow<Map<Route, List<ClientDayRelation>>>?