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});
    }
}

Cask

I had to do the same by looping through the returned Contacts cursor and creating a new Matrix cursor based on same Projection (after checking for duplicate email in my case). Thank for the pointer about matrix cursor. – Purge

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.

Cy

that's pretty cool! I have long since changed project, so I can't try this, but it sure looks like it would work! – Cask

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.

Figge

Yes. However, in this case, the contentprovider is external to the application. It is provided by the Contacts application. For internal contentprovider your suggestion would work. – Cask

Answer 3

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);

Trichinosis

Thank you for your answer. However, I am making a query on the Android Contacts ContentProvider, which means that the ContentProvider already exists and I can't make any modification to it. – Cask