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.

Camus

I'm facing an almost identical scenario, and none of the solutions seems elegant. What makes it challenging is that the main list items are ordered newest first, and can change at any time (new entries can appear). Using a CursorLoader approach was my first thought, with the position of a row as the ID to initLoader(). This fails as soon as a new entry arrives - the old loader for position 0 is tickled and returns results for the now position 1. Looks like I may have to go the managed cursor route. – Donavon

I never really found a true solution for this. I ended up adding a couple more columns in my post database table for the first 5 comments . So I made it so the first 5 comments could be flipped through and if the user wanted to view more of them I signaled for them to click on the view more comments button. I will try to revisit this in the future but for now this solution works fine – Camus

I would do the last you said, create a list with the comments associated to the post on adapter, then you don't need to re-query every time. You can do extend work doing some kind of cache, just keeping the latest comments shown. And querying the comments when not kept on the adapter. – Gnni

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.

Comehither