androidsqliteandroid-sqliteandroid-contentproviderandroid-loader

SQLite retrieve foreign key values with content provider

I have a sqlite database with "label" and "idea" tables in my Android app. made a foreign key on idea table as idea_label with int values that connected to label_table on its _id.

enter image description here

I use Loader to load my Cursor on the mainActivity that loads my idea table from the provider. As obvious it load idea_label int (But what I seek is to load the value from label_table which sets in label_body).

My loader on mainActivity class

@Override
public Loader<Cursor> onCreateLoader(int i, Bundle bundle) {
    String[] projection = {
            DatabaseContract.IdeaEntry._ID,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_NAME,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_DESCRIPTION,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_DATE,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_LABEL,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_ICON,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_IS_ACTIVE,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_IS_FAVORITE,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_IS_DONE,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_IS_ARCHIVED,
            DatabaseContract.IdeaEntry.COLUMN_IDEA_ORDER,
    };


    return new CursorLoader(this,
            DatabaseContract.IdeaEntry.CONTENT_URI_IDEA,
            projection,
            null,   // selection
            null,   // selectionArgs
            DatabaseContract.IdeaEntry.COLUMN_IDEA_ORDER   // order
    );
}

That calls this section on my provider class

@Nullable
@Override
public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
    SQLiteDatabase database = mDatabaseHelper.getReadableDatabase();
    Cursor cursor;

    int match = sUriMatcher.match(uri);
    switch (match){
        case IDEAS:
            cursor = database.query(DatabaseContract.IdeaEntry.IDEA_TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder);

Is there a way? trigger another loader? or my implementation is wrong in this case? any way or help that direct me to the right direction, will be very appreciated.

Solution

  • With SQLiteQueryBuilder you can join tables in setTables method and then just specify label_body column of label table in projection.

            String[] projection = {
        DatabaseContract.IdeaEntry._ID,
        ....          
        DatabaseContract.LabelEntry.COLUMN_LABEL_BODY,
        };

        ...

        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables("IDEA JOIN LABEL ON IDEA.IDEA_LABEL = LABEL._ID");
        builder.query(database, projection, selection, selectionArgs, null, null, sortOrder);