Android list view with data from multiple table joins

Question

I have written a ContentProvider which returns data from multiple tables (parent and child) using join. The result is a Cursor that contains one row for each child table row that was retrieved. For eg. If there are 2 rows in my child table for one row in parent table, the cursor has 2 rows with duplicated values for parent columns and unique values for child table.

While using this in ListView, I want to show one ListView item per parent i.e. per two records.

I have written a custom adaptor extending from CursorAdaptor to do so, where I have overridden getView, newView and bindView methods as follows -

@Override
public View getView(int position, View convertView, ViewGroup parent) {
    if (!mDataValid) {
        throw new IllegalStateException("this should only be called when the cursor is valid");
    }
    if (!mCursor.moveToPosition(position)) {
        throw new IllegalStateException("couldn't move cursor to position " + position);
    }
    View v;
    if (convertView == null) {

  //If the lookup value _id is same for this record don't create new view
  //instead only bind view and set the data from child table into existing view

        String lookupValue = mCursor.getString(mCursor.getColumnIndex(mLookupColumn));
        v = parent.findViewWithTag(lookupValue);
        if(v == null){
            v = newView(mContext, mCursor, parent);
            v.setTag(lookupValue);
        }

    } else {
        v = convertView;
    }
    this.bindView(v, mContext, mCursor);
    return v;
}   

@Override
public void bindView(View view, Context context, Cursor cursor) {
    mAdapterView.setData(context, convertToDomainObject(cursor), view);
}

@Override
public View newView(Context context, Cursor cursor, ViewGroup parent) {

    return mInflater.inflate(mLayout, parent, false);

}

However, the result of this is weird, I get blank views at alternate positions which is understandable (because my getView returns null) but this also is not consistent.

I have been struggling with this for quite some time. Would really appreciate any help.

Note - using distinct, group by doesn't help because I want to fetch both records and use them only map them to one row causing *:1 mapping between records and list items.

I've read your question several times and can't get it clear. So you don't want duplicate values in the ListView? — nKn, Feb 01 '14 at 12:55
i beleive you are not supposed to override getView in a CursorAdapter.http://stackoverflow.com/questions/5457699/cursor-adapter-and-sqlite-example - every operation about filtering your data should be done using the database, as its a lot faster — Daniel Bo, Feb 01 '14 at 13:04
I don't want to filter or eliminate duplicate values. For eg. http://developer.android.com/guide/topics/providers/contacts-provider.html#Access android contact entities, it provides data from multiple tables - contact,contact_email where contact is parent table and contact_email being child which has foreign key reference to contact table. In a use case where I want to show all contacts' summary in a ListView where each item contains contact name and all emails, ...continued... — user2111500, Feb 01 '14 at 14:06
I need to query using join on these tables eg. `select contact.name, contact_email.email from contact, contact_email where contact.id = contact_email.contact_id`. Now considering I have 3 emails per contact this returns me 3 records per contact. I want to transform these 3 records and show them in a ListView item. I hope this explains the case. — user2111500, Feb 01 '14 at 14:07

score 0 · Answer 1 · answered Feb 01 '14 at 18:19

In the case you just want to organize your results, I'd simply do that prior to assigning your adapter. You don't specify whether you use a custom layout for your ListView's rows, but this would be a possible way of approach.

Supposing you've a principal field (e.g., a name) and for that field some other several fields (e.g., several e-mail addresses), the first and probably key step is choosing the right data container. For instance I'd use an ArrayMap<String, ArrayList<String>>. The left value for the name and the right one for the e-mail addresses. Upon that, I'd build my ListView definition and overriding the getView() method, build my customized layout.

So are you suggesting extending both `CursorAdapter` and `ListView`? How are you going about binding the children in their parent's row - in getView() of a custom `CursorAdapter`? — Spinner, Mar 04 '14 at 13:49

Android list view with data from multiple table joins

1 Answers1