Map multiple columns to one column for custom search suggestions

Question

What I'm trying to accomplish: custom search in Android that can look across multiple columns for suggestions to the user.

I can map a single column "Topics" to the system-specified SUGGEST_COLUMN_TEXT_1, and that works wonderfully, I even have it grouping topics to remove duplicate entries.

However, what I'd like is to include "Author First Name || ' ' || Author Last Name" (concat these two for a single row entry) and "Author Group Name" into that single SUGGEST_COLUMN_TEXT_1 (and then figure out how to group by any duplicate I guess?).

I've thought about using a HashMap, but the query builder calls for a single String, so I'm not sure if that would work.

Is this possible? No. I'm certain it is. Any ideas? Perhaps a Union? I've commented out some of the code below that doesn't seem to work.

private static final HashMap<String, String> SEARCH_PROJECTION_MAP;
    static {

        SEARCH_PROJECTION_MAP = new HashMap<String, String>();
        SEARCH_PROJECTION_MAP.put("_id", "_id");
        SEARCH_PROJECTION_MAP.put(SearchManager.SUGGEST_COLUMN_TEXT_1,
                ExternalDbContract.QuoteEntry.TOPIC
//                        + " OR \"" + ExternalDbContract.QuoteEntry.AUTHOR_FIRST_NAME
//                        + "\" || \' \' || \"" + ExternalDbContract.QuoteEntry.AUTHOR_LAST_NAME
//                        + "\" OR \"" + ExternalDbContract.QuoteEntry.AUTHOR_GROUP_NAME
                        + "\" AS "
                        + SearchManager.SUGGEST_COLUMN_TEXT_1);    
    }

As far as raw SQL goes, I think this is what I'm trying to accomplish:

"SELECT Topic FROM quotes WHERE Topic LIKE " + selectionArgs[0] +
                "UNION SELECT \"Author First Name\" || ' ' || \"Author Last Name\" FROM quotes WHERE \"Author First Name\" LIKE " + selectionArgs[0] + " OR " + "\"Author Last Name\" LIKE " + selectionArgs[0] +
                "UNION SELECT \"Author Group Name\" FROM quotes WHERE \"Author Group Name\" LIKE " + selectionArgs[0] +
                "ORDER BY ASC LIMIT 10";

How might I accomplish this with Android's custom suggestions format?

score 0 · Answer 1 · answered May 18 '16 at 15:54

In the end, I abandoned the projection map and went straight to raw SQL. A couple caveats - I have spaces in my column names. Don't do this. '%a%' represents the user query, in the code it will actually be selectionArgs[0].You'll notice I have two CASE statements, one is for suggestions, and the other is to use the tapped suggestion as the intent data string instead of the row id. Required in my case.

Like so:

SELECT CASE WHEN "Author First Name" LIKE '%an%' THEN "Author First Name" || ' ' || "Author Last Name" WHEN "Author Last Name" LIKE '%a%' THEN "Author First Name" || ' ' || "Author Last Name" WHEN "Author Group Name" LIKE '%a%' THEN "Author Group Name" WHEN Topic LIKE '%a%' THEN Topic END AS suggest_text_1,

CASE WHEN "Author First Name" LIKE '%a%' THEN "Author First Name" || ' ' || "Author Last Name" WHEN "Author Last Name" LIKE '%a%' THEN "Author First Name" || ' ' || "Author Last Name" WHEN "Author Group Name" LIKE '%a%' THEN "Author Group Name" WHEN Topic LIKE '%a%' THEN Topic END AS suggest_data_intent_id, _id

FROM quotes WHERE (Topic LIKE '%a%' OR "Author First Name" LIKE '%a%'
OR "Author Group Name" LIKE '%a%' OR "Author Last Name" LIKE '%a%') GROUP BY suggest_text_1 ORDER BY suggest_text_1 ASC

Map multiple columns to one column for custom search suggestions

1 Answers1