在冰淇淋三明治的 ContentResolver 中分组 [英] Group By in ContentResolver in Ice Cream Sandwich

问题描述

我正在对 Android Contacts ContentProvider 进行查询.我需要一个 Group By 子句.在 Gingerbread 和 Honeycomb 中，我会做这样的事情来同时搜索电话号码和电子邮件:

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:

(实际的 WHERE 子句要复杂得多，因为它包括类型检查.这是一种简化，但产生了相同的结果)

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

')' 的注入结束了 WHERE 子句并允许插入一个 GROUP BY 子句.

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

但是，在 Ice Cream Sandwich 中，ContentProvider 似乎检测到了这一点并添加了正确数量的括号以防止我的注入.在单个游标查询中还有其他方法吗?

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?

目前，我已经删除了 GROUP BY，并添加了一个 MatrixCursor 来限制影响，但我宁愿有一个真正的光标:

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

推荐答案

我最近在查询 CallLog.Calls DB(我们无法修改 ContentProvider)时遇到了这个问题.我们最终要做的是构建一个如下所示的查询:

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

这里的想法是我们将任何有效的 sqlite 放在我们的子查询中，返回一个 id 列表，然后再次查询所有具有这些 id 的调用.

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.

最终代码如下所示:

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

性能会有所下降(因为我们实际上是为相同的结果查询两次)，但它肯定会比查询所有调用和执行 GROUP BY 工作快得多在 Java 世界中，还允许您使用附加子句构建查询.

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.

这篇关于在冰淇淋三明治的 ContentResolver 中分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！