Android Room: How to combine data from multiple SQL queries into one ViewModel

Question

Context: I am using Android Room and Android Architecture Components to load data in a Recyclerview. The data I am loading is a table of Message objects, which have a date, and a message content.

---

The Goal: Upon entering the messaging room, a timestamp of the current time is saved, and I want the Recyclerview to display the 10 most recent messages found in the Room database sent before that timestamp. The query would look something like this, where offset would be 10, and my_date would be the timestamp:

@Query("SELECT * from message_table WHERE date<:my_date ORDER BY date DESC LIMIT :offset")
LiveData<List<Message>> getOldMessages(long my_date, int offset);

Upon entry to the messaging room, I also want to instantiate a LiveData object, being observed by my messaging page Activity, which is updated every time new messages are arriving to the repository. The query for the live messages would look something like this:

@Query("SELECT * from message_table WHERE date>:my_date ORDER BY date DESC")
LiveData<List<Message>> getNewMessages(long my_date);

Additionally, I will later need to create a functionality that allows the user to load more old messages when he scrolls to the top of the recyclerview.

---

My attempts:

1. Combined query: the easiest way would be to write a single query which fetches both the live messages and the 10 older messages. Unfortunately, while I learned that SQL has a "UNION" operator, I couldn't figure out how to use it with the specific syntax in my java DAO.
2. MediatorLiveData: alternatively, I could create two LiveData's through the DAO, one for each query, and then I could join them together with MediatorLiveData. I guess that this could technically work, and I could then feed my mediated data to the recyclerview adapter, but it doesn't feel right to use LiveData for the old messages query where the data is essentially static.
3. Non-live data for old messages: The last option I could think of is to get the LiveData object from the new messages query and observe it, and also create a function to get a simple list (List instead of LiveData>) of the old messages, and in the onChanged function of the new messages LiveData, I could combine both lists of messages manually.

---

Code sources:

In my messaging room activity, I observe the LiveData in the following way:

    LiveData<List<Message>> newMessagesLiveData = mMessageViewModel.getNewMessages();
    newMessagesLiveData.observe(this, new Observer<List<Message>>() {
        @Override
        public void onChanged(@Nullable final List<Message> messages) {
            adapter.setMessages(messages);
            RecyclerView recyclerView = findViewById(R.id.recyclerview);
            recyclerView.scrollToPosition(adapter.getItemCount()-1);
        }
    });

Then the adapter receives the changes and updates itself:

public void setMessages(List<Message> messages){
    mMessages = messages;
    notifyDataSetChanged();
}
@Override
public void onBindViewHolder(MessageViewHolder holder, int position) {
    position = mMessages.size()-1-position;
    if (mMessages != null) {
        Message current = mMessages.get(position);
        holder.messageItemView.setText(current.getMessage());
    } else {
        holder.messageItemView.setText("No messages to display");
    }
}

Answer 1

I am helping you with option 1. You can union same table.

When you enter for chat save the current time in a local variable like

long currentTime = System.currentTimeMillis();

Now you need to union two queries. one query for last offset messages from currentTime and one query for messages greater than currentTime.

       @Query("SELECT * FROM (" +
        "SELECT * from message_table WHERE date<:my_date ORDER BY date DESC LIMIT :offset) UNION SELECT * from (SELECT * from message_table WHERE date>=:my_date ORDER BY date DESC)")
LiveData<List<Message>> getMessages(long my_date, int offset);

If you want any change let me know.