Android - Select max in contentProvider

Question

I try to run this query on my custom contentprovider.

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

to obtain the max value of priority int column.

i tried also :

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

whit no success.

This code return 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

My Content Provider Implementation (Standard - see notePad sample)

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

can you help me ?

regards

Glaciate

That syntax worked for me on a MyTouch OS 2.1 (don't know version of SQLite), but failed on my G2 :( – Famous

Answer 1

You are sending the max(priority) as the projection, send it in as the selection and it should work.

See this question: Android: Get highest value in column

Edit:

It appears this should work:

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

Locoweed

What is your ContentProvider implementation doing with the query? Perhaps it isn't translating it properly when it hits the database? – Locoweed

i've updated my post with my content provider query implementation .... my code run pretty well on standard query and crud operation. – Glaciate

It appears that Android simply requires a name to be set for the column. – Locoweed

Answer 2

Unfortunately nEx's answer (using "AS" to provide an alias for the column) only works with providers who don't set the strictProjectionMap flag in their SQLiteQueryBuilder. E.g. the contacts provider sets this flag, so if you try the AS approach you'll still get the "Invalid column" error.

And if someone can tell me how specifying an aggregate function like MAX in the selection is supposed to work I'd be very grateful. If I try it I just get a "misuse of aggregate function" exception, which is exactly what I'd expect from trying to put an aggregate function in a WHERE clause. I know you can put a subquery in the WHERE clause, but that assumes that you know the actual table structure, and that it will never change on any given device or OS level, which I wouldn't want to bet on (after all, that's why are using the provider interface in the first place, to abstract from the underlying data store).

So...I don't know of any way to do this for providers who set the strictProjectionMap flag other than to sort DESC by the desired field, and preferably LIMIT to 1 row if the provider supports it (or if you're willing to take a chance on breaking something by including the LIMIT in the sort string). I would be very happy if someone knows other approaches for these cases!

Dismay

Answer 3

In my case the answer of nEx actually worked, but I forgot to also add "max_"+COLUMN_NAME within the getColumnIndexByName() method of the cursor.

After I figured out this had also to be changed, it finally worked!

Son