Maintaining a ListView where each List item has a one to many relationship

Question

The way my app is set up is that I have a ListView, maintained by a CursorLoader, of posts made by the users. Each post has user comments that are associated with it. Each listitem has a custom textview at the bottom where the comments can be scrolled through(with a right or left swipe). Within each of the custom textviews is a list of the comments associated with its particular post. Its kind of like the comments on the Google+ app except the items are scrollable. The comments are stored in a separate database table than the posts.

The issue I'm having is that each time BindView is called I'm querying the database and retrieving a cursor with the associated comments and adding it to the custom textview list. This seems really inefficient to query the comments database table for each item. So I'm wondering if there would be an ideal way to handle this. My code looks something like:

public void bindView(View view, Context context, Cursor cursor)
    final String rowid = cursor.getString(cursor.getColumnIndexOrThrow(Database.ROWID));

    Cursor commentcursor = c.getContentResolver().query(DatabaseProvider.CONTENT_URI_COMMENTTABLE,   freeWriteCommentColumns, Database.PARENTPOSTROWID + " =?", new String[]{rowid}, null);

    commentcursor.moveToFirst();
    while (commentcursor.isAfterLast() == false){
            //get comment
            //add to comment text view list
            }

I've looked into CursorJoiners but that doesn't seem to useful. I've played around with JOINS but that makes the number of rows way larger than it needs to be. I'm currently playing around with a holder for the comments table cursor that is created when the adapter is created and set as a global variable. This seems like a decent avenue because I don't have to requery each time. I'm just not sure how to handle this situation.

Answer 1

I know that u tried it but this is the way to go. I had almost the same problem, even a larger one becuase mine was with JOIN and then a MergeCursor. But lets get back to your issue: bindView() is called on the UI and you doing DB calls on the UI thread, this is bad practice and on top of that you are running all over cursor. I would have JOIN the query for the post and concat the comments at the SQL level of the loading and then i would just use one cursor for the posts and comments and everything would be much much better.

How to concat the string at the SQL level? all the comments together? well this i will have look and edit my answer.