mercredi 4 février 2015

SQLiteQueryBuiler.setProjectionMap() doesn't affect where clause

I have an Android app with a ContentProvider class that queries two tables, users and items.


users table has the following columns:



_id (primary key)
online (integer)


items table has the following columns:



_id (primary key)
user_id (foreign key, maps to users._id)
name (text)


I then have a query that returns the result of the two tables joined together. In my ContentProvider, I use this code to map the column names:



SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setTables("items INNER JOIN users ON users._id=items.user_id");
Map<String, String> columnMap = new HashMap<String, String>();
columnMap.put("item_id", "items._id");
columnMap.put("user_id", "items.user_id");
columnMap.put("user_online", "users.online");
columnMap.put("item_name", "items.name");
queryBuilder.setProjectionMap(columnMap);


However, when I perform the following query, to find all items which are owned by online users:



String[] projection = {"item_name"};
String selection = "user_online=1";
Cursor cursor = getContentResolver().query(uri, projection, selection, null, null);


I get the following exception:



android.database.sqlite.SQLiteException: no such column: user_online (code 1): , while compiling: SELECT items.name AS item_name FROM items INNER JOIN users ON users._id=items.user_id WHERE (user_online=1)


The problem appears to be that setProjectionMap() affects projection, but not selection. Adding "user_online" to the projection as a hack works, because SELECT user_online turns into SELECT users.online AS user_online.


Is there any other way of solving this problem, short of performing string manipulation on the projection to map the column names manually?


Aucun commentaire:

Enregistrer un commentaire