android - ListView, SimpleCursorAdapter, an an EditText filter -- why won't it do anything?

Question

I'd like to filter a SimpleCursorAdapter-driven ListView with an EditText box right above it. I have the following code, but when I type in the box, nothing happens; the full list continues to display. What am I doing wrong?

    mCursor = getDirectoryList(null);

    adapter = new SimpleCursorAdapter(this,
            R.layout.directory_people_item, mCursor,
            new String[]{
                directoryPeople.LAST_NAME,
                directoryPeople.FIRST_NAME,
                directoryPeople.MIDDLE_NAME,
                directoryPeople.JOB_TITLE},
            new int[]{
                R.id.txtLastName,
                R.id.txtFirstName,
                R.id.txtMiddle,
                R.id.txtTitle} 
    ); 

    ListView av = (ListView)findViewById(R.id.listPeople);
    av.setAdapter(adapter);
    av.setFastScrollEnabled(true);
    av.setTextFilterEnabled(true);

    EditText etext=(EditText)findViewById(R.id.search_box);
    etext.addTextChangedListener(new TextWatcher() {
        public void onTextChanged(CharSequence s, int start, int before, int count) {
        }

        public void beforeTextChanged(CharSequence s, int start, int count, int after) {
        }

        public void afterTextChanged(Editable s) {
            ListView av = (ListView)findViewById(R.id.listPeople);
            SimpleCursorAdapter filterAdapter = (SimpleCursorAdapter)av.getAdapter();
            filterAdapter.getFilter().filter(s.toString());
        }
    });

    adapter.setFilterQueryProvider(new FilterQueryProvider() {
        public Cursor runQuery(CharSequence constraint) {
            return getDirectoryList(constraint);
        }
    });

And here's getDirectoryList():

public Cursor getDirectoryList (CharSequence constraint)  {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(
        directoryPeople.PEOPLE_TABLE
    );

    String asColumnsToReturn[] = { 
            directoryPeople.PEOPLE_TABLE + "."
            + directoryPeople.LAST_NAME + "," +
            directoryPeople.PEOPLE_TABLE + "."
            + directoryPeople.FIRST_NAME + "," +
            directoryPeople.PEOPLE_TABLE + "."
            + directoryPeople.MIDDLE_NAME + "," +
            directoryPeople.PEOPLE_TABLE + "."
            + directoryPeople.JOB_TITLE + "," +
            directoryPeople.PEOPLE_TABLE + "."
            + directoryPeople._ID
    };

    if (constraint == null  ||  constraint.length () == 0)  {
        //  Return the full list
        return queryBuilder.query(mDB, asColumnsToReturn, null, null,
                null, null, directoryPeople.DEFAULT_SORT_ORDER);
    }  else  {
        return mDB.query(directoryPeople.PEOPLE_TABLE, asColumnsToReturn, "LAST_NAME like '%'" + 
            constraint.toString() + "'%'", null, null, null,
            "CASE WHEN LAST_NAME like '" + constraint.toString() +
            "%' THEN 0 ELSE 1 END, LAST_NAME");
    }
}

I've tried every example and answer I've come across in my searches, but to no avail. It's frustrating, as you can imagine. Thanks in advance for anyone who can help!

Answer 1

Try this way:

}  else  {
    String value = "%"+constraint.toString()+"%";

    return mDB.query(directoryPeople.PEOPLE_TABLE, asColumnsToReturn, "LAST_NAME like ? ", new String[]{value}, null, null, null);
}

I skipped the last argument because I don't know what it really should do:

"CASE WHEN LAST_NAME like '" + constraint.toString() + "%' THEN 0 ELSE 1 END, LAST_NAME"

Have you tried this query in shell in emulator?