Group By in ContentResolver in Ice Cream Sandwich

Question

I am making a query on the Android Contacts ContentProvider. I need a Group By clause. In Gingerbread and Honeycomb, I do something like this to search phone numbers and emails at the same time:

(The actual WHERE clause is much more complicated as it includes types checks. This is a simplification, but it yields the same result)

String request = Phone.NUMBER + " LIKE ? OR " + Email.DATA + " LIKE ?";
String[] params = new String["%test%", "%test%"];

Cursor cursor = getContentResolver().query(
    Data.CONTENT_URI,
    new String[] { Data._ID, Data.RAW_CONTACT_ID },
    request + ") GROUP BY (" + Data.RAW_CONTACT_ID,
    params, "lower(" + Data.DISPLAY_NAME + ") ASC");

The injection of the ')' finishes the WHERE clause and allow the insertion of a GROUP BY clause.

However, in Ice Cream Sandwich, it appears that the ContentProvider detects this and adds the correct number of parenthesis to prevent my injection. Any other way of doing this in a single cursor query?

Edit

Currently, I have removed the GROUP BY, and added a MatrixCursor to limit the impact, but I'd rather have a real cursor:

MatrixCursor result = new MatrixCursor(new String[] { Data._ID, Data.RAW_CONTACT_ID });
Set<Long> seen = new HashSet<Long>();
while (cursor.moveToNext()) {
    long raw = cursor.getLong(1);
    if (!seen.contains(raw)) {
        seen.add(raw);
        result.addRow(new Object[] {cursor.getLong(0), raw});
    }
}

Answer 1

I recently battled this issue querying the CallLog.Calls DB (where we were not able to modify the ContentProvider). What we ended up going with was building a query that looked like this:

SELECT _id, date, duration, type, normalized_number FROM calls WHERE _id IN (
  SELECT _id FROM calls WHERE date < ? GROUP BY normalized_number ORDER BY date DESC LIMIT ?
);

The idea here is that we place any valid sqlite in our subquery, return a list of ids and then query again for all calls with those ids.

The final code looked something like this:

String whereClause = "_id IN (SELECT _id FROM calls WHERE data < ? GROUP BY normalized_number ORDER BY date DESC LIMIT ?)";

Cursor cursor = context.getContentResolver().query(
    CallLog.Calls.CONTENT_URI,
    new String[] { "_id", "date", "duration", "normalized_number" },
    whereClause,
    new String[]{ String.valueOf(amount), String.valueOf(dateFrom) },
    null
);

...

In the case that you're querying for contacts, it would look something like this:

String whereClause = "_id IN (SELECT _id FROM contacts WHERE " + Phone.NUMBER + " LIKE ? OR " + Email.DATA + " LIKE ? GROUP BY " + Data.RAW_CONTACT_ID + " ORDER BY lower(" + Data.DISPLAY_NAME + ") ASC)";

String[] params = new String["%test%", "%test%"];

Cursor cursor = getContentResolver().query(
    Data.CONTENT_URI,
    new String[] { Data._ID, Data.RAW_CONTACT_ID },
    whereClause,
    params,
    null
);

There will be some decrease in performance (since we're essentially querying twice for the same results), but it will surely be a lot faster than querying for all calls and doing the GROUP BY work in java world and also allows you to build up the query with additional clauses.

Hope this helps. We used this on Oreo and it fulfilled our needs.

Answer 2

You could create a custom Uri such that when your UriMatcher in your ContentProvider gets it, you can insert your group by clause and then execute the raw sql directly on the database.