Android - select max in contentProvider

Question

I am trying to run this request on my custom contentprovider.

cursor = activity.getContentResolver().query( GoalDescriptor.CONTENT_URI, "max(priority)", null, null, null); 

to get the maximum value of the int priority column.

I also tried:

 cursor = activity.getContentResolver().query( GoalDescriptor.CONTENT_URI, null, "max(priority)", null, null); 

no success.

This code returns this exception:

  java.lang.IllegalArgumentException: Invalid column MAX(priority) E/DatabaseUtils( 688): at android.database.sqlite.SQLiteQueryBuilder.computeProjection(SQLiteQueryBuilder.java:523) E/DatabaseUtils( 688): at android.database.sqlite.SQLiteQueryBuilder.buildQuery(SQLiteQueryBuilder.java:370) E/DatabaseUtils( 688): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:323) E/DatabaseUtils( 688): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:280) E/DatabaseUtils( 688): at nan.salsa.contentprovider.goal.GoalContentProvider.query(GoalContentProvider.java:118) E/DatabaseUtils( 688): at android.content.ContentProvider$Transport.bulkQuery(ContentProvider.java:150) E/DatabaseUtils( 688): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:111) E/DatabaseUtils( 688): at android.os.Binder.execTransact(Binder.java:288) E/DatabaseUtils( 688): at dalvik.system.NativeStart.run(Native Method) D/AndroidRuntime( 727): Shutting down VM 

Implementation of my content provider (standard - see sample notes)

 public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables(GoalDescriptor.TABLE_NAME); switch (sUriMatcher.match(uri)) { case GOAL: qb.setProjectionMap(sGoalProjectionMap); qb.appendWhere(GoalDescriptor._ID + "=" + uri.getPathSegments().get(1)); break; case GOALS: qb.setProjectionMap(sGoalProjectionMap); break; default: throw new IllegalArgumentException("Unknown URI " + uri); } // If no sort order is specified use the default String orderBy; if (TextUtils.isEmpty(sortOrder)) { orderBy = GoalInfo.GoalDescriptor.DEFAULT_SORT_ORDER; } else { orderBy = sortOrder; } // Get the database and run the query SQLiteDatabase db = mOpenHelper.getReadableDatabase(); Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, orderBy); // Tell the cursor what uri to watch, so it knows when its source data // changes c.setNotificationUri(getContext().getContentResolver(), uri); return c; } 

Could you help me?

welcomes

Answer 1

You send maximum (priority) as a projection, send it as a choice, and it should work.

See this question: Android: get the highest value in the column

Edit:

This seems to work:

 cursor = activity.getContentResolver().query( GoalDescriptor.CONTENT_URI, new String[] {"MAX(priority) AS max_priority"}, null, null, null); 

Answer 2

Unfortunately, the nEx response (using "AS" to provide an alias for the column) only works with providers that do not set the strictProjectionMap flag in their SQLiteQueryBuilder. For instance. the contact provider sets this flag, so if you try to use the AS method, you will still get an "Invalid column" error.

And if someone tells me how the aggregation function should work, for example MAX in the selection, I will be very grateful. If I try, I just get the “misuse of the aggregate function” exception, and that is exactly what I would expect from trying to put the aggregate function in a WHERE clause. I know that you can put a subquery in a WHERE clause, but it is assumed that you know the actual structure of the table and that it will never change at any particular device or OS level that I would not want to bet on (after all, therefore why use the provider interface to abstract from the main data warehouse).

So ... I don’t know how to do this for providers who set the strictProjectionMap flag, except to sort DESC by the desired field and preferably LIMIT to 1 line if the provider supports it (or if you "I wish to risk breaking something by including LIMIT to the sort string.) I would be very happy if someone knew other approaches to these cases!

Answer 3

In my case, the nEx answer did work, but I forgot to add "max_" + COLUMN_NAME in the getColumnIndexByName () method of the cursor.

After I realized that this also needed to be changed, it finally worked!