Querying relational data from ContentProvider

Question

I'm having trouble creating a query request that allows me to retreive an object from multiple tables.

The goal of the data flow is to have a request call 'insert' or 'update' on the ContentProvider, then have my views load the data from the ContentProvider, not the network call (for offline persistent caching).

I'm using GSON for json serialisation and deserialisation.

So say the Expense object received from the webservice is the following:

{
  "name"             : "Expense1",
  "amount"           : 10.10
  "date"             : "2014-03-23 12:32:22"
  "is_reimbursement" : false,
  "note"             : "This is a note :)",
  "spends"           : [
    {
      "user_id"          : 3,
      "amount_credited"  : 0,
      "amount_debited"   : 10.3
    }, {
      "user_id"          : 2,
      "amount_credited"  : 10.3,
      "amount_debited"   : 0
    }
  ],
  "debts"            : [
    {
      "amount"         : 10.3,
      "receiver_data" : {
        "confirmation"   : "wait",
        "user_id"        : 2
      },
      "giver_data" : {
        "confirmation"   : "wait",
        "user_id"        : 3
      }
    }
  ]
}

This is my corresponding POJO fields:

    @SerializedName("is_reimbursement")
    private boolean IsReimbursement;

    @SerializedName("name")
    private String Name;

    @SerializedName("amount")
    private double Amount;

    @SerializedName("date")
    private Timestamp Date;

    @SerializedName("note")
    private String Note;

    @SerializedName("created_by_id")
    private int CreatedById;

    @SerializedName("spends")
    private List<ExpenseSpend> Spends;

    @SerializedName("debts")
    private List<ExpenseDebt> Debts;

    @SerializedName("pictures")
    private List<ExpensePicture> Pictures;

I then have an SQL Table for EXPENSES, and one for each of the children (a table for Expense_Pictures, one for Expense_Debts, one for ExpenseSpend).

To insert into my database, I created insert URIs in my ContentProvider, and I insert an ExpenseObject by using the getContentResolver().applyBatch(ContentProvider.AUTHORITY, ops); method of my ContentProvider, with one insert operation for the expense, then one for each of the children.

This successfully inserts the JSON object copied above into all tables of my Database.

The question now is how can I retrieve this same POJO using a ContentProvider.query(...) ?

To phrase it differently, how can I make a request to the ContentProvider that returns all Expenses in the database (POJOs, with relations)?

For now, my query does not return full POJO information (it does not have the information of relational fields of my Expense POJO):

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

    int requestType = _uriMatcher.match(uri);
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    switch(requestType){
        case EXPENSES:
            queryBuilder.setTables(DatabaseHelper.TABLE_EXPENSE);
            queryBuilder.appendWhere(DatabaseHelper.KEY_ID + "=" + uri.getLastPathSegment());
            break;
        case EXPENSE_ID:
            queryBuilder.setTables(DatabaseHelper.TABLE_EXPENSE);
            queryBuilder.appendWhere(DatabaseHelper.KEY_ID + "=" + uri.getLastPathSegment());
            break;
        default:
            throw new IllegalArgumentException("Content provider error, could not match URI: " + uri);
    }

    SQLiteDatabase sqlDB = _db.getWritableDatabase();
    Cursor cursor = queryBuilder.query(sqlDB, projection, selection, selectionArgs, null, null, sortOrder);
    cursor.setNotificationUri(getContext().getContentResolver(), uri);
    return cursor;
}

Database create code, for information:

//EXPENSE Table create statement
private static final String CREATE_TABLE_EXPENSE = "CREATE TABLE "
        + TABLE_EXPENSE + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_IS_REIMBURSEMENT + " INTEGER,"
        + KEY_NAME + " TEXT,"
        + KEY_AMOUNT + " NUMERIC,"
        + KEY_DATE + " TEXT,"
        + KEY_NOTE + " TEXT,"
        + KEY_CREATED_BY_ID + " INTEGER,"
        + KEY_CREATED_AT + " TEXT,"
        + KEY_UPDATED_AT + " TEXT,"
        + KEY_DELETED_AT + " TEXT)";

//EXPENSE_DEBT Table create statement
private static final String CREATE_TABLE_EXPENSE_DEBT = "CREATE TABLE "
        + TABLE_EXPENSE_DEBT + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_EXPENSE_ID + " INTEGER,"
        + KEY_AMOUNT + " NUMERIC,"
        + KEY_GIVER_DATA_ID + " INTEGER,"
        + KEY_RECEIVER_DATA_ID + " INTEGER)";


//EXPENSE_DEBT_DATA Table create statement
private static final String CREATE_TABLE_EXPENSE_DEBT_DATA = "CREATE TABLE "
        + TABLE_EXPENSE_DEBT_DATA + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_USER_ID + " INTEGER,"
        + KEY_CONFIRMATION + " TEXT,"
        + KEY_CREATED_AT + " INTEGER,"
        + KEY_UPDATED_AT + " INTEGER)";

//EXPENSE_SPEND Table create statement
private static final String CREATE_TABLE_EXPENSE_SPEND = "CREATE TABLE "
        + TABLE_EXPENSE_SPEND + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_EXPENSE_ID + " INTEGER,"
        + KEY_USER_ID + " INTEGER,"
        + KEY_AMOUNT_CREDITED + " NUMERIC,"
        + KEY_AMOUNT_DEBITED + " NUMERIC)";

//EXPENSE_PICTURE Table create statement
private static final String CREATE_TABLE_EXPENSE_PICTURE = "CREATE TABLE "
        + TABLE_EXPENSE_PICTURE + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_EXPENSE_ID + " INTEGER,"
        + KEY_USER_ID + " INTEGER,"
        + KEY_DELETED_AT + " TEXT)";

As always, don't hesitate to tell me if there is something wrong in the database creation code (if I should be joining tables in this case) or if another approach is more suited to what I am trying to do.