Proper way to filter database items using RxAndroid

Question

I’m using the sample provided and have tweaked the code to my requirement. I’m learning Rx and not completely familiar with how it works. So, in my fragment I have

private static String LIST_QUERY = "SELECT * FROM " + TodoItem.TABLE;

And my onResume looks like:

@Override
public void onResume() {
    super.onResume();

    subscription = db.createQuery(TodoItem.TABLE, LIST_QUERY)
            .mapToList(TodoItem.MAPPER)
            .observeOn(AndroidSchedulers.mainThread())
            .subscribe(adapter);
}

The adapter showed the data in a listview and it works fine but now I have added a TextView on top of the list and want to filter results whenever the user enters text. I believe RxAndroid provides a debounce operator to make this efficient (and not query the DB instantly) but I’m unsure how I can combine this subscription code to listen to the textView changes and filter according to that text.

I already tried changing LIST_QUERY to "SELECT * FROM " + TodoItem.TABLE + " WHERE " + TodoItem.DESCRIPTION + " LIKE \"" + query + "\" LIMIT 5”;

and then in tried:

    etSearch.addTextChangedListener(new TextWatcher() {

        @Override
        public void onTextChanged(CharSequence searchQuery, int i, int i1, int i2) {
            query = searchQuery.toString();
            adapter.notifyDataSetChanged();
        }

        @Override
        public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) { //ignore }

        @Override
        public void afterTextChanged(Editable editable) { //ignore }
    });

This resulted in the list being empty (I thought this was okay since the initial query is empty string) but when I typed some text the query didn’t update/filter the list at all.

I tried an alternate approach and added take(5) (since I want LIMIT 5) after mapToList and that worked and showed only 5 items. And then I added .filter(, typed new and let Android Studio generate the Func1 code and it looked like:

    subscription = db.createQuery(ListItem.TABLE, LIST_QUERY)
            .mapToList(Item.MAPPER)
            .filter(new Func1<List<ListItem>, Boolean>() {
                @Override
                public Boolean call(List<ListItem> items) {
                    return null;
                }
            })

So problem is that it asks me to filter the whole list. I tried this:

public Boolean call(List<ListItem> items) {
   for(ListItem i: items)
      if(!i.description().startsWith(query))
          items.remove(i);
   return true;
}

But still the entire list shows and the changes in the text do not make the list change at all. I believe I missed something, that I have to subscribe to the EditText but I’m not sure how I would do this and combine it with this existing database subscribe code. Looking for solutions.

Answer 1

There's a few different ways you can do this. If you want the filter to exist in java then you can use the asRows operator on QueryObservable:

database.createQuery(ListItem.TABLE, ListItem.QUERY)
  .flatMap(query -> query.asRows(Item.MAPPER)
    .filter(item -> item.description.startsWith(query))
    .toList())

This will be an Observable<List<Item>> with only items that match the query.

However your original approach is the ideal way to do it, you were just using LIKE incorrectly. You probably want to include wildcards (% or _) to include results that dont match the query exactly. For example:

"SELECT * FROM " + TodoItem.TABLE + " WHERE " + TodoItem.DESCRIPTION + " LIKE \"%" + query + "%\" LIMIT 5”;

will match any results where the query is contained in the description. The % wildcard matches 0 or more of any character. The _ wildcard matches a single character.

The other part you mentioned is that the list isn't updating properly. You want to use RxAndroid here and not textChangedListeners most likely. In your view you will have something like

RxTextView.textChanges(editText)
  .switchMap(query -> database.createQuery(ListItem.TABLE, ListItem.query(query))
    .mapToList(ListItem.MAPPER))
  .observeOn(AndroidSchedulers.mainThread())
  .subscribe(list -> {
    adapter.setData(list);
    adapter.notifyDataSetChanged();
  });

This way you're not worrying about any races between the query executing and the adapter getting the notify call. The ListItem.query(query) method just composes that sqlite string with the query.