Displaying results of a SQL join in a ListActivity

Question

I need to display results from a SQL join in a ListView/ListActivity.

I've created a cursor:

Cursor cursor = db.rawQuery(LIST_JOIN_SQL, null);

and if I iterate through the cursor, the results are exactly what I expect. However when I try and use a SimpleCursorAdapter to display these results in a ListView I get a runtime exception because there is no column called ' id'.

SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, 
R.layout.item, cursor, FROM, TO);

Where FROM and TO are defined as:

private static String[] FROM = { "name", };
private static int[] TO = { R.id.name, };

Now I figure I'm probably using the wrong approach here, as it seems SimpleCursorAdapters aren't meant for displaying results of joins.

What approach would you recommend here? I'm restricted to using Android 1.6 APIs.

Answer 1

To use CursorAdapter (or any of its subclasses) you're required to have the "_id" column; it's explicitly stated in the documentation. That's because CursorAdapter uses this column heavily.

What I recommend is whatever you think (based on your project) to be the easier of these two paths:

1. Create an _id field in one of the tables so that when you join, you end up with an _id field.

2. Implement your own ListAdapter (starting from BaseAdapter) that is essentially a CursorAdapter but doesn't require an _id field.

I suspect #1 would be easier to do if you control the database, but that you'd have to do #2 if you cannot change the databases' schema.

Answer 2

Try selecting one of your columns as _ID alias

select col1 as _id from table