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

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.

Advertisement

Answer