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

Question

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 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?

Preceptor

Did you find an answer for this? I'm looking for something similar. I too want have 2 tables with a One-to-Many relationship; and I'm looking for an efficient way to load the data using Loaders. – Chronister

Answer 1

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.

Mozarab

Answer 2

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.

Organon

3 years later and I still can't find a better approach to this problem. Your suggestion of using a column in the parent table to hold a json representation of the child data works well for my use case. My one-to-many is about 1-to-"a handful" so the serialization time from collection to json array is quite minimal as is the space needed to store the string. I also still store the child records in a separate table to facilitate querying if needed. Thanks a bunch of the suggestion. – Zebedee

Thanks for the comment. It's nice to know that I'm helping out! I still use this approach too as a simple way to store child data. The accepted answer is also good, especially if the data is presented in an expandable list view. – Organon

Answer 3

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

Haffner