Room and ViewModel, multiple queries?

Question

So I have an android application that has a Room database, this backend is managed by a class that acts as a repository which feeds my view model, and then my activity (fragment) observes the view model, (everyone still with me?) in one of my fragments I want to get a list of all the cards (entities) in my database with a clicked value above X but I also want to get all cards that are marked as a favourite regardless of their clicked value.

My current solution is running 2 separate queries from my view model, through my repository and I have my activity observing both queries and combining the lists returned to it, this isn't working properly as my lists are being doubled and I don't have a great place for clearing the lists as the observers come back at different times because they are asynchronous (I'll post all the code).

I think what I want is to combine the queries somehow so that I'm only managing one list but my SQL knowledge isn't great and I'm not sure if this is possible, alternatively I could somehow merge these lists in my repository class but because I'm working with Live Data I'm unsure how to get the data safely to combine the lists, but maybe there is a better solution I'm unaware of but I'll post what I'm doing so far below

DAO Query these are the two queries I run, one gets all cards clicked x amount of times and the other gets all cards marked as favourite, both take a search string which isn't relevant right now

@Query("SELECT * FROM card WHERE cardFavourite AND cardWord LIKE :searchWord ORDER BY cardWord ASC")
LiveData<List<Card>> searchFavourites(String searchWord);

@Query("SELECT * FROM card WHERE cardClicked >= :clicks AND cardKeyStage <= :keystage  AND cardWord LIKE :searchWord ORDER BY cardFavourite ASC, cardClicked DESC, cardWord ASC")
LiveData<List<Card>> searchCardListRecents(String searchWord,int clicks, int keystage);

Repository my repository is a class full of calls to the database that return live data to their view model, the ones needed for this are :

public LiveData<List<Card>> searchFavouriteCards(String searchString){
    return cardDao.searchFavourites(searchString);
}

public LiveData<List<Card>> searchRecentCards(String searchString, int clicks){
    return cardDao.searchCardListRecents(searchString,clicks,keystage);
}

View Model my view model calls the repository methods using a switch map (for the searching)

public CardFavouritesViewModel(Application application, int clicks){
    cardRepository = new CardRepository(application);
    search = new MutableLiveData<>();
    cardRepository.getCardListByWordTypeAndWordDescription(args[0], args[1]);
    favourites = Transformations.switchMap(search, mySearch -> cardRepository.searchFavouriteCards(mySearch));
    recents = Transformations.switchMap(search, mySearch -> cardRepository.searchRecentCards(mySearch,clicks));
}

public LiveData<List<Card>> getLiveCardFavouriteList(){
    return favourites;
}

public LiveData<List<Card>> getLiveCardRecentsList(){
    return recents;
}

Activity and then my activity observes changes, as mentioned currently this is working in the sense that it does get the relevant data but its not working in respect that it duplicates the data and I don't have an obvious solution for clearing the list when its done any ideas welcome

ArrayList<Card> cardArrayList = new ArrayList<>();
cardFavouritesViewModel = ViewModelProviders.of(this,
                new 
CardFavouritesViewModelFactory(getActivity().getApplication(),5))
.get(CardFavouritesViewModel.class);
       cardFavouritesViewModel.setSearchString(mSearchString);
       cardFavouritesViewModel.getLiveCardFavouriteList().observe(this, 
       new Observer<List<Card>>() {
            @Override
            public void onChanged(@Nullable List<Card> cards) {
                cardArrayList.addAll(cards);
                cardAdapter.refreshList(cardArrayList);
                checkResults(cardArrayList.size());
            }
        });
        cardFavouritesViewModel.getLiveCardRecentsList().observe(this, 
        new Observer<List<Card>>() {
            @Override
            public void onChanged(@Nullable List<Card> cards) {
                cardArrayList.addAll(cards);
                cardAdapter.refreshList(cardArrayList);
                checkResults(cardArrayList.size());
            }
        });

Answer 1

You can try something like this:

 @Query("SELECT * FROM card WHERE cardFavourite OR (cardClicked >= :clicks AND cardKeyStage <= :keystage)  AND cardWord LIKE :searchWord ORDER BY cardFavourite ASC, cardClicked DESC, cardWord ASC")
    LiveData<List<Card>> searchCardListRecentsAndFavourites(String searchWord,int clicks, int keystage);