Grouping data on an ExpandableListView

Question

I have data in an SQLite table in the following format:

id|datetime|col1|col2
1|2013-10-30 23:59:59|aaa|aab
2|2013-10-30 23:59:59|abb|aba
3|2013-10-30 23:59:59|abb|aba
4|2013-10-31 23:59:59|abb|aba
5|2013-10-31 23:59:59|abb|aba

I would like to implement an ExpandableListView so that the data would grouped by datetime and shown like that:

> 2013-10-30 23:59:59 // Group 1
1|aaa|aab
2|abb|aba
3|abb|aba
> 2013-10-31 23:59:59 // Group 2
4|abb|aba
5|abb|aba

I have a custom CursorAdapter that I can easily use to populate ListView, showing date for every single item but I don't know how to "group" the data and populate it on an ExpandableListView - could you please give me any hints?

Answer 1

I have a custom CursorAdapter that I can easily use to populate ListView, showing date for every single item but I don't know how to "group" the data and populate it on an ExpandableListView - could you please give me any hints?

Here is solution that currently i'm using in my projects:

You cannot (shouldn't) use CursorAdapter because it's not suitable for your solution. You need to create and implement own Adapter by extending from BaseExpandableListAdapter

Then since you want to create "your own grouping" you need to change your current application logic:

• You need to create collection of objects returned from database (for demonstrating i will use name Foo)

Solution:

So your Foo object should looks like (due to your requirements, name of variables is only created to explain idea of solution) this:

public class Foo {

   private String title;
   private List<Data> children;

   public void setChildren(List<Data> children) {
      this.children = children;
   }

}

Where title will be date column from your database and children will be columns for specific (unique) date.

Due to your example:

id|datetime|col1|col2
1|2013-10-30 23:59:59|aaa|aab
2|2013-10-30 23:59:59|abb|aba
3|2013-10-30 23:59:59|abb|aba
4|2013-10-31 23:59:59|abb|aba
5|2013-10-31 23:59:59|abb|aba

One specific date (title property of Object Foo) have more associated rows so this will be simulated with defined collection of children in Foo object.

So now you need in your getAll() method (method that returns data from database usually called similarly like this) of your DAO object (object that comunicates with database, it's only terminology) create Foo objects in this logic.

Since you need to properly initialise collection of children for each unique date, you need to use two select queries. Your first select will return distinct dates - so if you have in database 40 rows with 10 different (unique) dates so your select will contain 10 rows with these unique dates.

OK. Now you have "groups" for your ListView.

Now you need to create for each created "group" its children. So here is comming second select that will select all rows and with correct condition you'll assign for each "group" Foo object own collection of children.

Here is pseudo-code #1:

String query = "select * from YourTable";
Cursor c = db.rawQuery(query, null);
List<Data> childen = new ArrayList<Data>();
if (c != null && c.moveToFirst()) {
   for (Foo item: collection) {
      // search proper child for current item
      do {
         // if current row date value equals with current item datetime
         if (item.getTitle().equals(c.getString(2))) {
            children.add(new Data(column3, column4)); // fetch data from columns
         } 
      } while (c.moveToNext());

      // assign created children into current item
      item.setChildren(children);

      // reset List that will be used for next item
      children = null;
      children = new ArrayList<Data>();

      // reset Cursor and move it to first row again
      c.moveToFirst();
   }
}
// finally close Cursor and database

So now your collection is "grouped" and now the remaining work is on your implemented ListAdapter - it's not tricky.

All what you need is to properly implement getGroupView() and getChildView() methods.

In "group method" you will inflate and initialise rows with titles from collection of Foo objects. These rows will become groups in ListView.

In "child method" you'll do same things but you won't inflate titles from collection but children of current Foo object from collection. These rows will become childs of one specific group.

Notes:

Due to #1. I simplified source code for demostrating purposes. But "in action" you can change a few things:

• Instead of c.getString(2) for getting second column is generally recommended to use column name so you should use c.getColumnIndex("columnName") instead.

• Is good practise to wrap source-code to try-finally block and in finnaly block close and release used sources like cursors and databases.

• Instead of "reusing" same collection of children how in example, you can create public method in Foo class that will add item into collection directly (snippet of code #2).

Snippet of code #2:

public class Foo {

   private String title;
   private List<Data> children = new ArrayList<Data>();

   public void addChild(Data child) {
      this.children.add(child);
   }
   ...
}

Summary:

Hope you understood me (i tried to to explain things in as simple way as possible, unfortunetly personal communication face to face is the best but this solution is not available for us) and also i hope that i helped you to solve your problem.