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.

Answer 2

Your kind of data modeling and representation can be modeled in Java code as a LinkedHashMap<String, List<Data>>, where Data could look like:

public class Data {
    private int id;
    private String col1;
    private String col2;

    public Data(int id, String col1, String col2) {
        this.id = id;
        this.col1 = col1;
        this.col2 = col2;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCol1() {
        return col1;
    }
    public void setCol1(String col1) {
        this.col1 = col1;
    }
    public String getCol2() {
        return col2;
    }
    public void setCol2(String col2) {
        this.col2 = col2;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(id).append(", ").append(col1).append(", ").append(col2);
        return sb.toString();
    }
}

Why LinkedHasMap? Because you need to preserve the order in which you insert the data. So your SQLite reading method could look like this:

public LinkedHashMap<String, List<Data>> readData(SQLiteDatabase db) {
    LinkedHashMap<String, List<Data>> result = new LinkedHashMap<String, List<Data>>();
    Cursor cursor = null;
    try {
        cursor = db.query("MY_TABLE", new String[] {
                "datetime", "id", "col1", "col2"
        }, null, null, null, null, "datetime, id ASC");
        while (cursor.moveToNext()) {
            String dateTime = cursor.getString(cursor.getColumnIndex("datetime"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String col1 = cursor.getString(cursor.getColumnIndex("col1"));
            String col2 = cursor.getString(cursor.getColumnIndex("col2"));
            List<Data> list = null;
            if (result.containsKey(dateTime)) {
                list = result.get(dateTime);
            } else {
                list = new ArrayList<Data>();
                result.put(dateTime, list);
            }
            list.add(new Data(id, col1, col2));
        }
    } catch (Exception ex) {
        Log.e("TAG", null, ex);
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
    return result;
}

A basic adapter would look like this:

public class ExpAdapter extends BaseExpandableListAdapter {
    private LinkedHashMap<String, List<Data>> input;

    private LayoutInflater inflater;

    public ExpAdapter(LayoutInflater inflater, LinkedHashMap<String, List<Data>> input) {
        super();
        this.input = input;
        this.inflater = inflater;
    }

    @Override
    public Object getChild(int groupPosition, int childPosition) {
        return getChildData(groupPosition, childPosition);
    }

    private Data getChildData(int groupPosition, int childPosition) {
        String key = getKey(groupPosition);
        List<Data> list = input.get(key);
        return list.get(childPosition);
    }

    private String getKey(int keyPosition) {
        int counter = 0;
        Iterator<String> keyIterator = input.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            if (counter++ == keyPosition) {
                return key;
            }
        }
        // will not be the case ...
        return null;
    }

    @Override
    public long getChildId(int groupPosition, int childPosition) {
        return getChildData(groupPosition, childPosition).getId();
    }

    @Override
    public View getChildView(int groupPosition, int childPosition, boolean isLastChild,
            View convertView, ViewGroup parent) {
        TextView simpleTextView = null;
        if (convertView == null) {
            // inflate what you need, for testing purposes I am using android
            // built-in layout
            simpleTextView = (TextView) inflater.inflate(android.R.layout.simple_list_item_1,
                    parent, false);
        } else {
            simpleTextView = (TextView) convertView;
        }
        Data data = getChildData(groupPosition, childPosition);
        simpleTextView.setText(data.toString());
        return simpleTextView;
    }

    @Override
    public int getChildrenCount(int groupPosition) {
        String key = getKey(groupPosition);
        return input.get(key).size();
    }

    @Override
    public Object getGroup(int groupPosition) {
        return getKey(groupPosition);
    }

    @Override
    public int getGroupCount() {
        return input.size();
    }

    @Override
    public long getGroupId(int groupPosition) {
        return 0;
    }

    @Override
    public View getGroupView(int groupPosition, boolean isExpanded, View convertView,
            ViewGroup parent) {
        TextView simpleTextView = null;
        if (convertView == null) {
            // inflate what you need, for testing purposes I am using android
            // built-in layout
            simpleTextView = (TextView) inflater.inflate(android.R.layout.simple_list_item_1,
                    parent, false);
        } else {
            simpleTextView = (TextView) convertView;
        }
        simpleTextView.setText(getKey(groupPosition));
        return simpleTextView;
    }

    @Override
    public boolean hasStableIds() {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public boolean isChildSelectable(int groupPosition, int childPosition) {
        return true;
    }

}

While its simple use in a basic activity would be something like this:

public class MyExpandableActivity extends FragmentActivity {
    private ExpandableListView expListView;

    @Override
    protected void onCreate(Bundle savedInstance) {
        super.onCreate(savedInstance);
        setContentView(R.layout.expandable_layout);
        expListView = (ExpandableListView) findViewById(R.id.exp_listview);
        fillList();
    }

    private void fillList() {
        LinkedHashMap<String, List<Data>> input = getMockList(); // get the collection here
        ExpAdapter adapter = new ExpAdapter(LayoutInflater.from(this), input);
        expListView.setAdapter(adapter);
    }
}

Activity's simple layout:

<ExpandableListView xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/exp_listview"
    android:layout_width="match_parent"
    android:layout_height="match_parent" />

Makes sense?