Best practice: Runtime filters with Room and LiveData

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!

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.

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)
  }

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.