CursorAdapter and pivot queries

I like Content Providers, but sometimes is difficult to use Loaders and CursorAdapter when you have to make “pivot” queries.

Let’s see an example of the problem and a solution that can help to solve a lot of cases.

Imagine we have a list of shops, and you want to show the list of cities in this table. Also, you want to show the postal codes with address in each city.

An initial approach would be to have a ContentProvider that returns a list of cities, and for each bindView execute a query that returns the associated postal codes. But this is extremely slow.

A nice solution I’ve found is to use the aggregate function group_concat from SQLite.

For example:

That will return a list like:

Voila! now we can process the second field  the bindView and show it accordingly. I usually create a CursorHelper for each content provider cursor that makes the task to transfor object to cursor and cursor to object. In this case, we can have a City object with a String for name and an Array or Collection for the post codes and just use the String.split to transform it when reading the row.

Quick and dirty!

 

Leave a Reply