How to retrieve data from child table using CursorLoader which loads data from main table?

My app is using CursorLoader to load data from main table. The data will be displayed in ListView with the help of CursorAdapter. The view displayed in ListView also requires data from child table. The relationship of the tables is one-to-many.

• If I join both tables during the query, the combined records will be as many as child records, thus in 开发者_JAVA百科ListView, it will display multiple item for one single record in the main table.

• If I don't join the table, I am not sure what the best way to retrieve child records after the CursorLoader has delivered the data via the cursor. Anyone able to help me out?

If the child table data doesn't need to be queried separately then you could put that data into the main table using something like JSON notation and then parse it out inside of the bindView method for the CursorAdapter. This might have performance problems depending on the complexity of the data in the child table. But that would allow you to use a single cursor.

Another approach you could take is to make a DAO object that ran the raw query as a join (so you get multiple rows) and then processes the cursor it to a List where Foo is a POJO representing the data. (or run n+1 queries - the main query and then a sub query for each row). Then make a Loader that calls the DAO to return a List and use a ListAdapter from that. Your custom loader could still register for the data change notifications and do the reloading. I've used CursorLoader as a pattern to make my own loaders that reload when I didn't want to mess with the Cursor in the ListView and it works well.

You should try the join option and set your sort order for your query to make sure that the results are sorted by the results in the main table.

Afterwards, you can use an ExpandableListView to show the main item and all the child items underneath to indicate the "one-to-many" relationship in a good way. Of course you need to write your adapter appropriately to handle this case.

If you want to get the aggregate data from child table ,you should use 'group by' in sql statements.for example,two tables:

parent(id,name)
child(id,parentId,age)

To query all parents with the oldest child:

select a.id,a.name,max(b.age)
from parent as a inner join child as b on a.id=b.parentId
group by a.id,a.name