mercredi 8 juillet 2015

Sqlite Order By Case

I am trying to search a list of contacts and present the list in a prioritized order to the user. The order is display_name, phone, email and address. I've reviewed a few examples on Stackoverflow and sqlite.org with no luck.

If the user searches for "john" then all the contacts with "john" in the display_name would appear first, in descending order, followed by contacts that might live in Johnson City, etc. The search is case-insensitive and limited to 50 rows.

    String[] projection = {
        DTab._id.toString(),
        DTab.contact_id.toString(),
        DTab.display_name.toString(),
        DTab.phone.toString(),
        DTab.email.toString(),
        DTab.kv.toString(),
        DTab.address.toString(),
};

String where = ""
    +"("+DTab.display_name+" LIKE ?) OR "
    +"("+DTab.phone+" LIKE ?) OR "
    +"("+DTab.email+" LIKE ?) OR "
    +"("+DTab.address+" LIKE ?)";
String[] args = new String[]{
        "%" + search + "%",
        "%" + search + "%",
        "%" + search + "%",
        "%" + search + "%",
};

String orderBy =
        "CASE "
        +"WHEN "+DTab.display_name+" LIKE ? != '' THEN 1 "
        +"WHEN "+DTab.phone       +" LIKE ? != '' THEN 2 "
        +"WHEN "+DTab.email       +" LIKE ? != '' THEN 3 "
        +"ELSE 4 "
+"END LIMIT 50";

Cursor c = detail_db.query(DETAIL_TABLE, projection, where, args, null, null, orderBy);

The sort order is incorrect, as if the order by definition is being ignored. I get a few records that match display_name followed by records matching an email followed by more display_name records. Insights or suggestions on how to debug it is appreciated.

Aucun commentaire:

Enregistrer un commentaire