Using GROUP BY with CursorLoader - Android

Question

I'm trying to get all Contacts from ContactsContract.CommonDataKinds.Email.CONTENT_URItable

grouped by their ContactID and Name using CursorLoader.

So for example, if contact have 2 emails, I will get only one row regarding this contact.

Basically, it should go like that:

SELECT ContactID, Name
FROM Table
GROUP BY ContactID, Name

My code:

String filter = ContactsContract.CommonDataKinds.Email.DATA + " NOT LIKE '')) 
GROUP BY ((" + ContactsContract.CommonDataKinds.Email.CONTACT_ID + ", display_name";

final static String[] PROJECTION =
{
   Utils.hasHoneycomb() ? Contacts.DISPLAY_NAME_PRIMARY : Contacts.DISPLAY_NAME,
   ContactsContract.CommonDataKinds.Email.CONTACT_ID,
};

return new CursorLoader(getActivity(),
                    ContactsContract.CommonDataKinds.Email.CONTENT_URI,
                    ContactsQuery.PROJECTION,
                    filter,
                    null,
                    ContactsQuery.SORT_ORDER);

I'm getting next SQLite error:

"Caused by: android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: SELECT DISTINCT display_name, contact_id FROM view_data_restricted data WHERE (1 AND mimetype_id = 1) AND ((data1 NOT LIKE '')) GROUP BY ((contact_id, display_name)) ORDER BY sort_key"

So this is my query:

SELECT DISTINCT display_name, contact_id FROM view_data_restricted data 
WHERE (1 AND mimetype_id = 1) AND ((data1 NOT LIKE '')) 
GROUP BY ((contact_id, display_name)) ORDER BY sort_key

"DISTINCT" added by the cursorLoader. Why?
I know there are multiple "(" / ")" signs, It's the best way I found to handle the adding of the "GROUP_BY". any other ideas will help.
I can't find the problem near "," as the error said.

Have a look here: http://stackoverflow.com/questions/10580883/group-by-with-cursorloader — joe1806772, Aug 01 '14 at 19:22

score 1 · Answer 1 · answered Sep 03 '14 at 04:29

the problem lies in how CursorLoader construct your query statement.

According to this:

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

Unfortunately, you can't achieve this if you are passing Selection, Projection and SortOrder as parameters. There's no place to "hack" in the "Group By" statement..

score -1 · Answer 2 · edited May 23 '17 at 11:58

-1

select (SELECT  group_concat(DISTINCT display_name) FROM view_data_restricted) as a,(SELECT  group_concat(DISTINCT contact_id) FROM view_data_restricted) as b FROM view_data_restricted data 
WHERE (1 AND mimetype_id = 1) AND ((data1 NOT LIKE '')) 
GROUP BY ((contact_id, display_name)) ORDER BY sort_key

distict multiple column :

please refer this link:

reference link

edited May 23 '17 at 11:58

Community

1
1

answered Jan 09 '14 at 08:21

dipali

10,966
5
25
51

i hope its useful to you. – dipali Jan 09 '14 at 08:30
I'm sorry, but I'm using Android CursorLoader. The second parameter in CursorLoader constructor is the Uri of the table and the "SELECT" part is written by the CursorLoader not by me. What wrong in my above query? – David Jan 09 '14 at 08:59

Using GROUP BY with CursorLoader - Android

2 Answers2