Android: how to do an inner-join query in sqlite using the ContextResolver?

Question

I want to create a sqlite cursor who u can handle an inner-join query using the contextResolver.

this is my current cursor:

Cursor cursor = getContentResolver().query(Exercise.CONTENT_URI, new String[]{Exercise.Columns._ID, Exercise.Columns.EXERCISE_NAME, Exercise.Columns.DONE_LAST},
            "",null, Exercise.Columns.DONE_LAST);

My new Cusor should handle something like this:

private final String MY_QUERY =
                "SELECT a."+Exercise.Columns.EXERCISE_NAME+", a."+Exercise.Columns.DONE_LAST+", b."+Progress.Columns.WEIGHT+", " +
                "b."+Progress.Columns.SETS+", b."+Progress.Columns.REPITITIONS+" " +
                "FROM "+Exercise.TABLE_NAME+" a " +
                "INNER JOIN "+Progress.TABLE_NAME+" b " +
                "ON a.+"+Exercise.Columns._ID+" = b."+Progress.Columns._ID +
                "WHERE b.+"+Progress.Columns.WHENDONE+" = ( Select MAX (b."+Progress.Columns.WHENDONE+") from b";

If found some solutions in SOV - they all use rawQuery, but I don't have an SQLiteOpenHelper object in my activity. So is there another Solution, executing this query by getContentResolver()?

Answer 1

You would create a persistant view over the two database tables. Then access the view from the content provider. You could also create the view if it doesn't exist but it wouldn't be as efficient as having it already built.

private static final String VERSION_66_CREATE_VIEW = "CREATE VIEW IF NOT EXISTS "
        + VIEW_MARKERS
        + " AS SELECT "
        + TABLE_MARKERS
        + "."
        + KEY_ID
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_LOCATION_ID
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_ID
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_SNIPPET
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_PROCESSED_ID
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_URL
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_URL_THUMBLARGE
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_URL_THUMBMEDIUM
        + ", "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_IMAGE_URL_THUMBSMALL
        + ", "
        + TABLE_MARKERS + "." + KEY_MARKER_TITLE + ", "
        + TABLE_MARKERS + "." + KEY_MARKER_ALBUM_ID + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_LATITUDE
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_LONGITUDE
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_ACCURACY
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_ALTITUDE
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_BEARING
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_PROVIDER
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_SPEED
        + ", "
        + TABLE_LOCATIONS
        + "."
        + KEY_LOCATION_TIME
        + " FROM "
        + TABLE_MARKERS
        + " JOIN "
        + TABLE_LOCATIONS
        + " ON "
        + TABLE_MARKERS
        + "."
        + KEY_MARKER_LOCATION_ID
        + " = "
        + TABLE_LOCATIONS + "." + KEY_ID;

 db.execSQL(VERSION_66_CREATE_VIEW);

Access the view in the ContentProvider

@Override
public Cursor query(Uri uri, String[] projection, String selection,
                    String[] selectionArgs, String sortOrder) {


    // Uisng SQLiteQueryBuilder instead of query() method
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

    // check if the caller has requested a column which does not exists

    // Set the table

    int uriType = sURIMatcher.match(uri);
    switch (uriType) {
        case MARKERS:
            // create the view here if you can't change it in the database.
            checkColumns(availableMarkers, projection);
            queryBuilder.setTables(RidesDatabaseHandler.VIEW_MARKERS);
            break;

 ...

 SQLiteDatabase db = database.getWritableDatabase();
    Cursor cursor = queryBuilder.query(db, projection, selection,
            selectionArgs, null, null, sortOrder);
    // make sure that potential listeners are getting notified
    cursor.setNotificationUri(getContext().getContentResolver(), uri);
    return cursor;