SQLite: How to join tables and present the cursor using SimpleCursorAdapter

Question

I have been using SQLite in Android for long but it's my first time to perform a join-table operation. I am totally frustrated about it because I have been working on it all day.

Now I have 2 tables, FTSProfile and FTSCell, and I want to join them using a common key A with a LEFT JOIN. So I implemented a series of code, ContentProvider and Database to perform what I want. In the Database, I used SQLiteQueryBuilder to build the query and hence get the cursor i want. I used:

SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    builder.setTables(FTS_VIRTUAL_TABLE_PROFILE+" LEFT JOIN "+FTS_VIRTUAL_TABLE_CELL+" ON "+FTS_VIRTUAL_TABLE_CELL+"."+Database.KEY_CID+"="+FTS_VIRTUAL_TABLE_PROFILE+"."+Database.KEY_CELL_ID);
    builder.setProjectionMap(mColumnMap_CombinedTable);

and my Hashmap is like:

HashMap<String,String> map = new HashMap<String,String>();
      for (String key: KEYS_PROFILE) map.put(key, FTS_VIRTUAL_TABLE_PROFILE+"."+key);
      for (String key: KEYS_CELL) map.put(key, FTS_VIRTUAL_TABLE_CELL+"."+key);
      map.put(BaseColumns._ID, BaseColumns._ID);
      map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
      map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);

And then when i run the program and try to open the list for the result, i get an SQLiteException: no such column: _id...

I have no ideas how to solve it but i really want to know how can i get rid of it. Can anyone help?

Answer 1

Ha funny question. I was just looking at the same thing 30 min ago ;). You could try following the example as in used in the android native contact app.

http://www.google.com/codesearch/p?hl=en#cbQwy62oRIQ/src/com/android/providers/contacts/ContactsProvider2.java&q=ContactsProvider.java&d=5

They declare a interface

private interface DataContactsQuery {
        public static final String TABLE = "data "
                + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) "
                + "JOIN contacts ON (raw_contacts.contact_id = contacts._id)";

        public static final String[] PROJECTION = new String[] {
            RawContactsColumns.CONCRETE_ID,
            DataColumns.CONCRETE_ID,
            ContactsColumns.CONCRETE_ID
        };

        public static final int RAW_CONTACT_ID = 0;
        public static final int DATA_ID = 1;
        public static final int CONTACT_ID = 2;
    }

Then when trying to query the results they use this piece of code.

Cursor cursor = null;
        try {
            cursor = mDb.query(DataContactsQuery.TABLE, DataContactsQuery.PROJECTION,
                    mSb.toString(), mSelectionArgs.toArray(EMPTY_STRING_ARRAY), null, null,
                    Contacts.IN_VISIBLE_GROUP + " DESC, " + Data.RAW_CONTACT_ID);
            if (cursor.moveToFirst()) {
                dataId = cursor.getLong(DataContactsQuery.DATA_ID);
                rawContactId = cursor.getLong(DataContactsQuery.RAW_CONTACT_ID);
                contactId = cursor.getLong(DataContactsQuery.CONTACT_ID);
            } else {
                // No contact found, return a null URI
                return -1;
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }

Is this usefull?

Answer 2

What about creating a VIEW in your database? If this is a frequent query, surface it as a view, and you can avoid a lot of the mess of setting up the join, and just worry about the selection criteria. If you can write out the join as straight SQL, then you should be able to create a view on it, and then use that.

The problem I see with your code appears to be that you're trying to select a column named _id (which is needed for a CursorAdapter), but that column does not exist. You're mapping BaseColumns._ID to BaseColumns._ID, but those are just constants, and if your database doesn't have a column _id, then you will get the error you're seeing.

CREATE VIEW FooBar AS 
    SELECT Foo.field1 AS FooField1, 
           Foo.field2 AS FooField2, 
           Bar.field1 AS BarField1, 
           Bar.field2 AS BarField2, 
           (Foo._id * 1000000000) + Bar._id AS _id 
    FROM Foo LEFT JOIN Bar ON Foo.Field1 = Bar.Field2;

That (Foo._id * 1000000000) + Bar._id AS _id is a quick, dirty, and probably painfully incorrect way of synthesizing a unique _id for the row returned by the view, which you will need if you're using this as an adapter. It also has the downside of being read-only. You can't update a VIEW.

Once you have your VIEW, you can SELECT against it as if it were a table itself. i.e., SELECT * FROM FooBar WHERE BarField1 > 10;