ExpandableListView with data from various tables

Question

I want to make an expandable list view with data from my SQLite database.

But I only want to take data for child lists from various tables, and Groups will be always the same.

And then put the data of each table to child list of related group.

It is possible? There is some example?

Thanks.

Answer 1

Ok, a little lead in. The project I'm pulling this example from is a shopping list app. There can be multiple shopping lists, with different items and quantities.

So, three tables in use. The grocery list table, which is just a table with an id and a name. Then an items table that contains the id, item name, unit of measure, category, aisle and price. The final table is the list table, which contains the id, the list id, the item id and the quantity in the list.

I wanted to be able to sort by category or aisle, so I created some cursor methods that take parameters to specify which column to use.

Three things make this work... first you use DISTINCT in the group cursor query to make sure you get one record for each seperate item you want to group on. Second, you set them as the _id. Finally, you JOIN the various tables to get the info you need into a single cursor.

dbhelper expandable list cursors

public Cursor fetchGroup(String group, long list) {
    String query = "SELECT DISTINCT "
            + group
            + " AS _id FROM "
            + LISTITEM_TABLE
            + " JOIN "
            + ITEM_TABLE
            + " ON list_items.item_id=items._id WHERE list_items.list_id = "
            + list;
    return mDb.rawQuery(query, null);
}

public Cursor fetchChildren(String column, String token, long list) {
    String query = "SELECT list_items._id, list_items.item_qty, items.name, items.cost, items.unit, list_items.checked FROM list_items JOIN items ON list_items.item_id=items._id WHERE "
            + column
            + "='"
            + token
            + "' AND list_items.list_id = "
            + list
            + " ORDER BY list_items.checked, items.name";
    return mDb.rawQuery(query, null);
}

Set the expandable list

    ExpandableListView lv;
    mGroupsCursor = mDbHelper.fetchGroup(group,
            ListFragment_ShopList.listId);
    getActivity().startManagingCursor(mGroupsCursor);
    mGroupsCursor.moveToFirst();

    lv = (ExpandableListView) getActivity().findViewById(android.R.id.list);

    mAdapter = new MyExpandableListAdapter(mGroupsCursor, getActivity(),
            R.layout.rowlayout_expgroup, R.layout.rowlayout_itemlist_exp,
            new String[] { "_id" }, new int[] { android.R.id.text1 },
            new String[] { GroceryDB.ITEM_NAME, GroceryDB.LISTITEM_QTY,
                    GroceryDB.ITEM_UNIT }, new int[] { R.id.ListItem1,
                    R.id.ListItem3, R.id.ListItem2 });
    lv.setAdapter(mAdapter);

public class MyExpandableListAdapter extends SimpleCursorTreeAdapter {

    public MyExpandableListAdapter(Cursor cursor, Context context,
            int groupLayout, int childLayout, String[] groupFrom,
            int[] groupTo, String[] childrenFrom, int[] childrenTo) {
        super(context, cursor, groupLayout, groupFrom, groupTo,
                childLayout, childrenFrom, childrenTo);
    }

    @Override
    protected Cursor getChildrenCursor(Cursor groupCursor) {
        Cursor childCursor = mDbHelper.fetchChildren(GroceryListMain.group,
                groupCursor.getString(groupCursor.getColumnIndex("_id")),
                ListFragment_ShopList.listId);
        getActivity().startManagingCursor(childCursor);
        childCursor.moveToFirst();
        return childCursor;
    }

    protected void bindChildView(View view, Context context, Cursor cursor,
            boolean isLastChild) {

        TextView name = (TextView) view.findViewById(R.id.ListItem1);
        TextView qty = (TextView) view.findViewById(R.id.ListItem3);
        TextView unit = (TextView) view.findViewById(R.id.ListItem2);
        TextView cost = (TextView) view.findViewById(R.id.ListItem4);

        name.setTextColor(MyApplication.shoplistitem_name);
        unit.setTextColor(MyApplication.shoplistitem_desc);
        qty.setTextColor(MyApplication.shoplistitem_qty);
        cost.setTextColor(MyApplication.shoplistitem_desc);

        name.setText(cursor.getString(2));
        qty.setText(cursor.getString(1));
        unit.setText(cursor.getString(4));

        DecimalFormat df = new DecimalFormat("0.00");
        Double hold = Double.valueOf(cursor.getString(3));
        Double qtyhold = Double.valueOf(cursor.getString(1));
        Double total = hold * qtyhold;

        cost.setText("$" + df.format(total));
    }
}

Hopefully this illustrates what you need. If you have any questions, leave me a comment and I'll try and clarify.