CODEWITHSUNDEEP
androidsqlite
MobileMateo
MobileMateo

Reputation: 459

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.

Upvotes: 0

Views: 829

Answers (2)

CL.
CL.

Reputation: 180172

You have seven parameters (?), but only four values in the args array, so the last three parameters have the value NULL.

You can use parameter numbers to reuse parameter values:

String where = ""
    +"("+DTab.display_name+" LIKE ?1) OR "
    +"("+DTab.phone       +" LIKE ?2) OR "
    +"("+DTab.email       +" LIKE ?3) OR "
    +"("+DTab.address     +" LIKE ?4)";
String orderBy =
    "CASE "
    +"WHEN "+DTab.display_name+" LIKE ?1 THEN 1 "
    +"WHEN "+DTab.phone       +" LIKE ?2 THEN 2 "
    +"WHEN "+DTab.email       +" LIKE ?3 THEN 3 "
    ...

(And as Karakuri said, the != '' did not make sense.)

Upvotes: 0

Karakuri
Karakuri

Reputation: 38605

Remove the != '' comparisons from your order clause:

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 ASC "
        + "LIMIT 50";

Upvotes: 1

Related Questions