Best practice: Runtime filters with Room and LiveData

Question

I am working on a screen that shows the contents of a Room wrapped DB using a recycler. The adapter gets the LiveData from a ViewModel that hides the query call on the Room DAO object. So, the LiveData object is actually a ComputableLiveData object that is aware of changes to the Room DB.

Now I want to add filter options to the screen. Where / how would I implement this in this Room-LiveData-ViewModel setup?

Should the adapter or ViewModel "postfilter" the results in the LiveData? Should I requery the data from room for every filter change? Can I reuse the underlying (Computable)LiveData for that? If not, should I really create new LiveData for every filter change?

A similar question is discussed here: Reload RecyclerView after data change with Room, ViewModel and LiveData

Libradalibrarian

How big is your data set? If you are not holding the entire data set in memory, by definition you have to go back to the database for any change in filter state. – Hellenist

are you using paging support library? – Parnas

I am looking for an answer that is independent of the size of my data set. ;) But thanks for the advise that a post filter is probably not the best practice. – Libradalibrarian

Paging support lib: no – Libradalibrarian

"I am looking for an answer that is independent of the size of my data set. ;)" then see the first paragraph in the link i posted - "The paging library makes it easier for your app to gradually load information as needed from a data source, without overloading the device or waiting too long for a big database query." – Parnas

Thanks for pointing that out. Maybe I do need to apply (the) paging (library). But I don't see how that helps with my initial problem. How would I change the underlying query statement of the LiveData or - using the paging lib - the DataSource.Factory? – Libradalibrarian

you have create a new LivePagedListBuilder, build() it and of course observe() the returned LiveData each time you filter your db - more here – Parnas

Ok, thanks. I am new to MVVM. Where / how would I implement that? Would that be the single LiveData of my ViewModel, would the ViewModel hold different model views (for different filter combinations) or would the client (the adapter / recycler / fragment / activity) hold multiple ViewModels? – Libradalibrarian

@Parnas thanks again for your suggestions. My question was not about something not working, but about best practice. I am currently working on different details of the screen while waiting for more opinions. I would be happy if you would take the time to render an answer that satisfies every detail of the OP (How to do it right regarding MVVM and arch libs). I will eventually compose an answer with my findings when I'm done and no satisfying answer exists up to then. – Libradalibrarian

@Parnas Been a year, is there a newer/better solution to filtering with Room? (Checking before asking my own question). I use LiveData<PagedList<T>>, so can't even use MediatorLiveData. I'm requering and reattaching observers on each filter change. Just doesn't feel right :( – Tzar

@AdiB you dont need MediatorLiveData - just use Transformations.switchMap on every change of search criteria – Parnas

@Parnas Thanks! Discovered that yesterday, and implemented it. Works perfectly. – Tzar

Answer 1

So, I ended up doing it like this:

• The fragment fowards the filter state to the ViewModel. Side effect: the filter state may be used by multiple (i.e. subsequent due to configuration change) fragment instances. Maybe you want that, maybe not. I do.
• The ViewModel holds a MediatorLiveData instance. It has a single source: The Room DB LiveData object. The source simply forards changes to the mediator. If the filter is changed by the fragment, the source is swapped by a requery.

Answering my detailed questions:

• No postfiltering
• Yes, requery on filter change
• I don't reuse the ComputableLiveData (not sure wether it would be possible)

Regarding the discussion in the comments:

• I don't apply paging

Final note on Room: Am I wrong or do I need to write seperate DAO methods for every filter combination I want to apply? Ok, I could insert optional parts of the select statement via a String, but then I would lose the benefits of Room. Some kind of statement builder that makes statements composable would be nice.

EDIT: Please note the comment by Ridcully below. He mentions SupportSQLiteQueryBuilder together with @RawQuery to address the last part I guess. I didn't check it out yet though.

Thanks to CommonsWare and pskink for your help!

Libradalibrarian

Hello @Oderik, I am also trying to filter my recycler view that consumes result of viewmodel's lovedata entries. Can you please explain me that what solution you have found because I couldn't understand your answer clearly. – Heritor

That how to forword filter state to the view model as we initialize viewmodel once in the fragment lifecycle, I manage to do so inside onCreateView. And how to use MediatorLiveData instance for requerying on the change of filter state. Apart from that, I am using like statement "@Query("SELECT * FROM network WHERE name LIKE :networkName || '%'") public abstract LiveData<List<NetworkEntry>> getNetworksByName(String networkName);" in this way and it just filters if name "starts with" the given text, but how to query if the name "contains" the given word. – Heritor

I think that's out of scope of this questions. Maybe you should post your own. – Libradalibrarian

There is the SupportSQLiteQueryBuilder, you can use together with @RawQuery in the DAO. – Plantain

Answer 2

I'm working in a similar problem. Initially I had RxJava but now I'm converting it to LiveData.

This is how I'm doing inside my ViewModel:

// Inside ViewModel
MutableLiveData<FilterState> modelFilter = new MutableLiveData<>();
LiveData<PagedList<Model>> modelLiveData;

This modelLivedata is constructed in the following way inside view model constructor:

        // In ViewModel constructor
        modelLiveData = Transformations.switchMap(modelFilter,
                    new android.arch.core.util.Function<FilterState, LiveData<PagedList<Model>>>() {
                        @Override
                        public LiveData<PagedList<Model>> apply(FilterState filterState) {
                            return modelRepository.getModelLiveData(getQueryFromFilter(filterState));
                        }
                    });

When the view model receives another filter to be applied, it does:

// In ViewModel. This method receives the filtering data and sets the modelFilter 
// mutablelivedata with this new filter. This will be "transformed" in new modelLiveData value.
public void filterModel(FilterState filterState) {

    modelFilter.postValue(filterState);
}

Then, this new filter will be "transformed" in a new livedata value which will be sent to the observer (a fragment).

The fragment gets the livedata to observe through a call in the view model:

// In ViewModel
public LiveData<PagedList<Model>> getModelLiveData() {

    return modelLiveData;

}

And inside my fragment I have:

@Override
public void onActivityCreated(@Nullable Bundle savedInstanceState) {
    super.onActivityCreated(savedInstanceState);

    ViewModel viewModel = ViewModelProviders.of(this.getActivity()).get(ViewModel.class);

    viewModel.getModelLiveData().observe(this.getViewLifecycleOwner(), new Observer<PagedList<Model>>() {
        @Override
        public void onChanged(@Nullable PagedList<Model> model) {
            modelListViewAdapter.submitList(model);
        }
    });

}

I hope it helps.

Whereabouts

Answer 3

Based on Francisco's answer (thank you VERY much for that!), here is how I implemented similar dynamic database filtering based on EditText input, but in Kotlin.

Here is the Dao query example, where I perform a select based on a passed in filter String:

// Dao query with filter
@Query("SELECT * from myitem WHERE name LIKE :filter ORDER BY _id")
fun getItemsFiltered(filter: String): LiveData<List<MyItem>>

I have a repository, but in this case it's just a simple pass-through. If you don't have a repository, you could call the dao method directly from the ViewModel.

// Repository
fun getItemsFiltered(filter: String): LiveData<List<MyItem>> {
    return dao.getItemsFiltered(filter)
}

And then in the ViewModel I use the Transformations method that Francisco also used. My filter however is just a simple String wrapped in MutableLiveData. The setFilter method posts the new filter value, which in turn causes allItemsFiltered to be transformed.

// ViewModel
var allItemsFiltered: LiveData<List<MyItem>>
var filter = MutableLiveData<String>("%")

init {
    allItemsFiltered = Transformations.switchMap(filter) { filter ->
        repository.getItemsFiltered(filter)
    }
}

// set the filter for allItemsFiltered
fun setFilter(newFilter: String) {
    // optional: add wildcards to the filter
    val f = when {
        newFilter.isEmpty() -> "%"
        else -> "%$newFilter%"
    }
    filter.postValue(f) // apply the filter
}

Note the initial filter value is set to a wildcard ("%") to return all items by default. If you don't set this, no items will be observed until you call setFilter.

Here is the code in the Fragment where I observe the allItemsFiltered and also apply the filtering. Note that I update the filter when my search EditText is changed, and also when the view state is restored. The latter will set your initial filter and also restore the existing filter value when the screen rotates (if your app supports that).

// Fragment
override fun onViewCreated(view: View, savedInstanceState: Bundle?) {
    super.onViewCreated(view, savedInstanceState)

    // observe the filtered items
    viewModel.allItemsFiltered.observe(viewLifecycleOwner, Observer { items ->
        // update the displayed items when the filtered results change
        items.let { adapter.setItems(it) }
    })

    // update the filter as search EditText input is changed
    search_et.addTextChangedListener {text: Editable? ->
        if (text != null) viewModel.setFilter(text.toString())
    }
}

override fun onViewStateRestored(savedInstanceState: Bundle?) {
    super.onViewStateRestored(savedInstanceState)

    // update the filter to current search text (this also restores the filter after screen rotation)
    val filter = search_et.text?.toString() ?: ""
    viewModel.setFilter(filter)

}

Hope that helps!!

Disclaimer: this is my first post, so let me know if I missed something. I'm not sure how to link to Francisco's answer, otherwise I would have done that. It definitely helped me get to my implementation.

Tricho

Did you pentest this code? It seems to be vulnerable for SQL injection attack. – Fossick

Hi @pkuszewski, no I did not. So if you have more information about the potential vulnerability and how to mitigate please share. Thanks! – Tricho

Answer 4

You can sort database using CASE WHEN and THEN Look at this code

Create an Constant class for sorting id

object Constant{
  const val NAME_ASC = 1    
  const val NAME_DESC = 2   
  const val ADDED_ASC = 3  
  const val ADDED_DESC = 4 
}

Interface Dao

@Query(
    "SELECT * FROM table WHERE name=:name ORDER BY" +
            " CASE WHEN :sortBy = $NAME_ASC THEN title END ASC , " +
            " CASE WHEN :sortBy = $NAME_DESC THEN title END DESC , " +
            " CASE WHEN :sortBy = $ADDED_ASC  THEN added END ASC , " +
            " CASE WHEN :sortBy = $ADDED_DESC THEN added END DESC , " +
)
fun getItems(name: String, sortBy: Int): MutableLiveData<Item>

Your repository Class

fun getItems(name: String, sortBy: Int) : MutableLiveData<Items>{
    return myDao.getItems(name,sortBy)
  }

Viscous

Thanks, I wasn't aware of that option. It helps reducing the number of required Dao methods. Regarding the main question of this thread: Just like all the other answers this one suggests a requery. – Libradalibrarian