Group By in ContentResolver in Ice Cream Sandwich

first off all excuse my POOR English! I'm new to Java/Android, started with 4.2.1 and fight with that too almost 2 days, then i start reading some more details about SQLiteQueryBuilder the query part is pretty much that what u are looking for ;)

it have:

public Cursor query (SQLiteDatabase db, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder)

the query "function" of the Content Provider only gives you:

query(Uri uri, String[] projection, String selection,String[] selectionArgs, String sortOrder)

here u can trick around, i will post you my code snip:

    @Override
public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    final SQLiteDatabase db = mOpenHelper.getReadableDatabase();
/* a String is a Object, so it can be null!*/
    String groupBy = null;
    String having = null;

    switch (sUriMatcher.match(uri)) {
...
...
...
        case EPISODES_NEXT:
        groupBy = "ShowID";
        queryBuilder.setTables(EpisodenTable.TableName);
        break;
    default:
        throw new IllegalArgumentException("Unknown URI " + uri);
    }

    Cursor c = queryBuilder.query(db, projection, selection, selectionArgs,
            groupBy, having, sortOrder);
    c.setNotificationUri(getContext().getContentResolver(), uri);
    return c;
}

thats its!

here the code i use to execute:

        Cursor showsc = getContext().getContentResolver().query(
            WhatsOnTVProvider.CONTENT_EPISODES_NEXT_URI,
            EpisodenTable.allColums_inclCount,
            String.valueOf(Calendar.getInstance().getTimeInMillis() / 1000)
                    + " < date", null, null);

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.

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.