Room with LiveData: Many to Many mapping in adapter

Question

Lets take the following example:

A many to many mapping exists for PRODUCTS and ORDERS. So a product can be on multiple orders and an order can have multiple products. In Room I have an entity which has both the product id and order id as foreign keys so I can save the relations. It's now very easy to get all the orders for a specific product and also all the products for a specific order.

Now here comes the trouble. As far as I know there is no way to get the order object with all of it's products in 1 query/entity. This can be read in further detail in this post. In most places I can bypass this by just running two queries. The first to get the order I'm interested in, and the second to get the products based on the Id of the order.

Now I want to display the combination of an order with its products in an adapter. For that I need to combine all my orders with their products. I'm clueless on how to solve this with LiveData.

The best solution in my opinion would be to create one query that fetches the OrderWithProducts directly from the database. This post suggests it should be possible, but I've not managed to get this to work. Also the most crucial part in that example is missing: the OrderItem class.

If that solution is not possible there must be some way to get the LiveData OrderWithProducts list with 2 queries and somehow combine them.

EDIT After the suggestions of @Demigod now I have the following in my ViewModel:

// MediatorLiveData can observe other LiveData objects and react on their emissions.
var liveGroupWithLights = MutableLiveData<List<GroupWithLights>>()

fun createOrdersWithProducts() {
        appExecutors.diskIO().execute {
            val ordersWithProducts = mutableListOf<OrderWithProducts>()
            val orders = orderRepository.getGroupsSync()
            for (order in orders) {
                val products = productRepository.getProductsSync(order.id)
                val orderWithProducts = OrderWithProducts(order, products)
                ordersWithProducts.add(orderWithProducts)
            }

            liveGroupWithLights.postValue(ordersWithProducts)
        }
    }

The function inside my fragment to submit data to the adapter:

private fun initRecyclerView() {
    orderListViewModel.getOrdersWithProducts().observe(this, Observer { result ->
        adapter.submitList(result)
    })
}

So now I'm able to have a OrderWithProduct object as the item for my adapter. This is great, I can use products for each order in my adapter. Now I'm having trouble to update these items whenever the values in the database changes. Any ideas for this part?

Edit2: the invalidationtracker

db.invalidationTracker.addObserver(object : InvalidationTracker.Observer("orders", "products", "order_product_join") {
        override fun onInvalidated(tables: MutableSet<String>) {
            createOrdersWithProducts()
        }
    })

The problem I have now is that the validation tracker gets notified a lot for a single change.

Answer 1

As far as I know, it's not possible currently with a single query. To solve this, you will need to run several queries here. At first - obtain a list of orders with a single query, and after that obtain a list of products per each order. To achieve this, I can think of several options:

1. Make your own OrdersWithProductsProvider, which will return this combined entities (Order with List<Porduct>), and it will subscribe for the changes to database to emit new objects using LiveData on every orders or products table change.
2. You can use a MediatorLiveData to fill the list of Orders with their Products, but I don't think this is a best approach since you will need to run query in a background thread, maybe use of Rx is more convenient here.

Personally, I would use a first option, since probably I want to obtain up-to-date list of orders with their products, which means that the update should trigger on change of three tables (products, orders, products_to_orders), which can be done via Room.InvalidationTracker. Inside that provider I would use Rx (which can work with LiveData via LiveDataReactiveStreams).

Addition on how to achieve that:

How to achieve that isn't really matters, the only thing - run this whole query in the background thread post it to LiveData. You can use Executor, Rx, or a simple Thread. So it will look something like:

private val database : Database // Get the DB
private val executor = Executors.newSingleThreadExecutor()
private val liveData = MutableLiveData<List<OrderWithProducts>>()

fun observeOrdersWithProducts():LiveData<List<OrderWithProducts>> {
    return liveData
}

private fun updateOrdersWithProducts() {
    executor.post {
        val ordersWithProducts = mutableListOf<OrderWithProducts>()
        val orders = db.orders()
        for (order : orders) {
            val products = database.productsForOrder(order)
            val orderWithProducts = OrderWithProducts(order, products)
            ordersWithProducts.add(orderWithProducts)
        }
        liveData.post(ordersWithProducts)
    }
}

Take it as not complete working code, rather an example of implementation. Call updateOrdersWithProducts on initialization/first call and every time InvalidationTracker will notify about the db change.