How do I request the MEDIA option "group by"?

Question

I am new to Android. In fact, I want to request data from a media provider using the Content Provider and Content resolver.

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

My question is: how can I request data from a media provider, as shown below, using the GROUP BY :

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

I tried installing projection and where as follows:

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

and where :

 _data LIKE "A" OR _data LIKE "B" 

but I could not find how to set the query parameter GROUP BY _id .

Please help me.

Answer 1

 where = "_data LIKE 'A' OR _data LIKE 'B'"; where += ") GROUP BY (_id"; // note the char ')' and '(', the ContentResover will completed for U c = mContent.query(CONTENT_URI,projection,where,null,null); 

link page = http://zengyan2012.iteye.com/blog/1118963

Answer 2

You cannot from ContentProvider . Now, if you are writing your ContentProvider , you can implement it. Inside the content provider, you must use the SQLiteQueryBuilder class, which has a query() method that takes a GROUP BY string.

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

This class also has a setDistinct(true) method, which sets the query as DISTINCT, as you specified in the SQL query.

Answer 3

I am not sure if this is possible.

Recently, I struggled with such things, and I managed to work ContentProvider by inserting the data from the ContentProvider into a temporary table and query the table to get the results.

The story is that the data behind the ContentProvider may not be a database. It can be XML, JSON, FileSystem, etc. Therefore, they do not have the GROUP BY option, and therefore they left it. You also cannot assume that count(_id) will work.

Answer 4

Sorry my WRONG English first! I'm new to Java / Android, started with 4.2.1 and struggled with this after almost 2 days, then started reading more details about SQLiteQueryBuilder the query part is what you are looking for;)

he has:

 public Cursor query (SQLiteDatabase db, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder) 

the content provider request function of the content provider gives you:

 query(Uri uri, String[] projection, String selection,String[] selectionArgs, String sortOrder) 

here you can cheat, I will send you my code:

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

here it is!

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