How to query from MEDIA provider with "group by" option?

Question

Hello, I'm a newbie to Android. Actually, I want to query data from Media provider with Content provider & content resolver.

 c = mContent.query(CONTENT_URI,projection,where,null,null); 

My question is, I want to query data from media provider as below

select DISTINCT _id, count(_id), _data FROM aaa_table WHERE _data LIKE "A" OR _data LIKE "B" GROUP BY _id;

I set, 1. projection :

 final String[] projection = new String[] {
                "_id", 
                "COUNT ("+ _id +")" ,
                "_data" 
                }; 

1. where

   _data LIKE "A" OR _data LIKE "B"

but, I couldn't find how to set the query option "GROUP BY _id".

Please help me.

Answer 1

I am not sure if that possible.

I was struggling with similar stuff lately, and I managed to workaround by inserting the data from ContentProvider into a temp table and querying the table for results.

The story is that the data behind a ContentProvider might not be a database. It can be XML, JSON, FileSystem etc... So these do not have the Group By option, and therefor they left it out. You also can't suppose always that count(_id) will work.

Answer 2

You can't from a ContentProvider. Now, if you're writing your content provider you could implement it. Inside your content provider you'd have to use a SQLiteQueryBuilder class which has a query() method that takes a grouBy string.

http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html

This class also has a setDistinct(true) method that sets the query as DISTINCT, as you indicated you require in your SQL statement