Best Practice: Runtime Filters With Room And Livedata

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 obj

Solution 1:

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 ViewModelMutableLiveData<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>>>() {
                        @OverridepublicLiveData<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 ViewModelpublicLiveData<PagedList<Model>> getModelLiveData() {

    return modelLiveData;

}

And inside my fragment I have:

@OverridepublicvoidonActivityCreated(@Nullable Bundle savedInstanceState) {
    super.onActivityCreated(savedInstanceState);

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

    viewModel.getModelLiveData().observe(this.getViewLifecycleOwner(), newObserver<PagedList<Model>>() {
        @OverridepublicvoidonChanged(@Nullable PagedList<Model> model) {
            modelListViewAdapter.submitList(model);
        }
    });

}

I hope it helps.

Solution 2:

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!

Solution 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")fungetItemsFiltered(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.

// RepositoryfungetItemsFiltered(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.

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

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

// set the filter for allItemsFilteredfunsetFilter(newFilter: String) {
    // optional: add wildcards to the filterval 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).

// FragmentoverridefunonViewCreated(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())
    }
}

overridefunonViewStateRestored(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.

Solution 4:

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

Create an Constant class for sorting id

object Constant{
  constval NAME_ASC = 1constval NAME_DESC = 2constval ADDED_ASC = 3constval 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 , " +
)fungetItems(name: String, sortBy: Int): MutableLiveData<Item>

Your repository Class

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