About Android ContentResolver Query Group By

Question

I test sql and it works fine in Sqlite Spy:

select ifnull(_name, _number) as identifer, count(_id) as amount from call group by identifer

And I wanna use it in ContentConsolver but it can't work with "group by":

String[] projections = new String[] { "ifnull(name, number) as identifer", "count(_id) as amount" };
String group = "identifer";
//String selection = ") GROUP BY (" + group;
Cursor cursor = getContentResolver().query(CallLog.Calls.CONTENT_URI, projections, null, null, null /*!group*/ );

What should I do?

If the `ContentProvider` queried by the `ContentResolver` doesn't expose an URI with group-by or supports some URI parameter for a group-by argument you're going to have to do the grouping in Java code after reading the entire contents of the `Cursor`. — Jens, Aug 16 '12 at 05:51
Solved temporarily, error with ICS! add： String selection = " 1 = 1 ) GROUP BY ( identifer"; — thecr0w, Aug 16 '12 at 06:56

score 0 · Accepted Answer · edited May 23 '17 at 11:48

@njzk2 did the trick by using HashSet: Group By in ContentResolver in Ice Cream Sandwich, But it didn't work with count() if you want sum. I think the best solution is to make a copy of CallLog Database, then you can use rawQuery() or whatever you want (maybe it is a waste of performance).

private void refreshDbCache()
{
    CallDbCache dbCache = new CallDbCache(this);
    dbCache.clear(CallDbCache.TB_CALL);

    String[] columns = new String[] { CallLog.Calls._ID, CallLog.Calls.NUMBER, CallLog.Calls.CACHED_NAME };
    Cursor cursor = getContentResolver().query(URI_CALL, columns, null, null, null);
    while (cursor != null && cursor.moveToNext()) {
        int id = cursor.getInt(cursor.getColumnIndex(CallLog.Calls._ID));
        String number = cursor.getString(cursor.getColumnIndex(CallLog.Calls.NUMBER));
        String name = cursor.getString(cursor.getColumnIndex(CallLog.Calls.CACHED_NAME));
        dbCache.insert(CallDbCache.TB_CALL, new LogData(id, number, name, "", "", "", ""));
    }
    cursor.close();

    dbCache.close();
}

About Android ContentResolver Query Group By

1 Answers1