Populate single TextView with multiple SQL columns using SimpleCursorAdapter (Suggestion for searchbox)

Tags: , , , ,



I’m using SQL as datasource. My app’s searchbox has autosuggestion, but currently it’s only able to return the results of 1 column (e.g. column1). I want it to be able to search in 2 columns (so, column1 + column2) and show both column’s result in the autosuggestion dropdown. This is the code I’m trying to implement the method with, but without any success.

public void LoadSuggestions(){

final String[] from = new String[] {"column1", "column2"};
final int[] to = new int[] {R.id.suggestion_text}; //suggestion_text is the TextView to populate

suggestionAdapter = new SimpleCursorAdapter(MainActivity.this,
        R.layout.suggestion_row,null, from, to,0){
    @Override
    public void changeCursor(Cursor cursor) {
        super.swapCursor(cursor);
    }
};

search.setSuggestionsAdapter(suggestionAdapter);

search.setOnSuggestionListener(new SearchView.OnSuggestionListener() {
    @Override
    public boolean onSuggestionClick(int position) {

        CursorAdapter ca = search.getSuggestionsAdapter();
        Cursor cursor = ca.getCursor();
        cursor.moveToPosition(position); 
        String clicked_word = cursor.getString(cursor.getColumnIndex("column1"));
        search.setQuery(clicked_word, false);

        search.clearFocus();
        search.setFocusable(false);

        Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
        Bundle bundle = new Bundle();
        bundle.putString(("column1",clicked_word);
        intent.putExtras(bundle);
        startActivity(intent);

        return true;
    }

    @Override
    public boolean onSuggestionSelect(int position) {
        return true;
    }
});

search.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
    @Override
    public boolean onQueryTextSubmit(String query) {
        String text = search.getQuery().toString();

        Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \-.]{1,25}");
        Matcher m = p.matcher(text);

        if(m.matches())
        {
            Cursor c = myDbHelper.getMeaning(text);

            if(c.getCount()==0)
            {
                showAlertDialog();
            }
            else
            {
                search.clearFocus();
                search.setFocusable(false);

                Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
                Bundle bundle = new Bundle();
                bundle.putString(("column1", text);
                intent.putExtras(bundle);
                startActivity(intent);

            }
        }
        else
        {
            showAlertDialog();
        }

        return false;
    }

    @Override
    public boolean onQueryTextChange(final String s) {

        search.setIconifiedByDefault(false);

        Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \-.]{1,25}");
        Matcher m = p.matcher(s);

        if(m.matches()) {
                Cursor cursorSuggestion = myDbHelper.getSuggestions(s);
                suggestionAdapter.changeCursor(cursorSuggestion); 
        }

        return false;
    }
});}

and this is the SQL query behind it

public Cursor getSuggestions(String text) {
Cursor c= myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '"+text+"%' AND column2 LIKE '"+text+"%' LIMIT 40", null);
return c;}

I think the main problem is here:

final String[] from = new String[] {"column1", "column2"};

because with this order, if I search something, it will return column1’s list, but if I change the order from {“column1”, “column2”} to {“column2”, “column1”}, the autosuggestion will show column2’s results only. I understand that I should do something with this line:

final int[] to = new int[] {R.id.suggestion_text};

but I didn’t find any detailed info on this, only a solution where the columns would be shown in different rows, but I need both results to show in a single dropdown. Thank you for your time and for reading this!

Answer

Ok, after some days resting I just saw it clear, the way to accomplish this is changing this string:

Cursor c= myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '"+text+"%' AND column2 LIKE '"+text+"%' LIMIT 40", null);

To this:

Cursor c= myDatabase.rawQuery("SELECT _id, column1 FROM words WHERE column1 LIKE '"+text+"%' UNION SELECT _id, column2 FROM words WHERE column2 LIKE '"+text+"%' LIMIT 40", null);

This way, the search will happen in both rows, so the suggestions will be taken and shown from both rows.



Source: stackoverflow