how to use join query in CursorLoader when its constructor does not support it

Question

I am having two tables with 1:n relationship, I am using content provider and cursorloader.

How would I make a join query to work with cursor loader? I could hack it up somehow with rawSql inside content provider but how to do it in cursor loader constructor is beyond me.

Thanks a lot !

CursorLoader(Context context, Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)

How will query for join when Uri could only point to one table

Answer 1

The Uri does not point to any table. It points to whatever you feel like pointing it to.

Let's pretend that your two tables are Customer and Order. One customer may have many orders. You want to execute a query to get all outstanding orders... but you want to join in some customer-related columns that you will need, such as the customer's name.

Let's further pretend that you already have content://your.authority.goes.here/customer and content://your.authority.goes.here/order defined to purely query those tables.

You have two choices:

1. Add the join of the customer's display name on your /order Uri. Having another available column probably will not break any existing consumers of the provider (though testing is always a good idea). This is what ContactsContract does -- it joins in some base columns, like the contact's name, on pretty much all queries of all tables.

2. Create content://your.authority.goes.here/orderWithCust that does the same basic query as /order does, but contains your join. In this case, you could have insert(), update(), and delete() throw some sort of RuntimeException, to remind you that you should not be modifying data using /orderWithCust as a Uri.

In the end, designing a ContentProvider Uri system is similar to designing a REST Web service's URL system. In both cases, the join has to be done on the provider/server side, and so you may need to break the one-table-to-one-URL baseline to offer up some useful joins.

Answer 2

I found a solution using sub-classes of ContentProvider. Let's say you have table tblA and another table tblB. I recommend creating two classes "AContentProvider" and "BContentProvider". Most importantly, make sure that both tables are set up in the same database.

The main part of the solution is to override ContentProvider.query() in the ContentProvider that you will call from your CursorLoader - the URI decides which one it is:

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sort) {
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    qb.setTables(
            "tblA LEFT JOIN tblB"
                    + " ON ("
                    + "tblA.b_id"
                    + " = "
                    + "tblB.id"
                    + ")"
    );

    ...
    // Content of projection is set by CursorLoader
    // usually in an Activity that implements LoaderManager.LoaderCallbacks<>

    Cursor c = qb.query(
            database,
            projection,
            selection,
            selectionArgs,
            groupBy,
            having,
            orderBy
    );

    ...
    return c;
}

As you can see, the JOIN is done in setTables(). Using projection you make sure that you only display the columns you really need and most of all, you have no duplicate columns, like "id" from both tables:

final String[] projection = new String[] {
        "tblA.*",
        "tblB.columnThatOnlyBHas"
};

Make use of the override and try to get as much work done in the sub-classes as you can; for example: all my overridden query() methods call setNotificationUri() to notify the ContentResolver if the cursor result set changes.:

c.setNotificationUri(getContext().getContentResolver(), uri);