Android's SimpleCursorAdapter with queries using DISTINCT

Question

Here's an interesting question that I'm shocked hasn't been asked more often on the internet. Android's CursorAdapters are extremely useful once you get a ContentProvider up and running and learn how to use it, but they are limited due to their requirement on having the _id field as part of their query (an error is thrown without it). Here's why:

My specific problem is that I have two spinners: One spinner should contain unique "category" names from the database, and the other should populate with all the database entries from the selected "category" (category being the column name, here). This seems like a pretty simple setup that many programs might use, no? Trying to implement that first spinner is where I've run into problems.

Here's the query that I would like for that first spinner:

SELECT DISTINCT category FROM table;

Making this query throws an error on CursorAdapter because the _id column is required as part of the query. Adding the _id column to the projection naturally returns every row of the table, since you're now asking for distinct id's as well, and every id is distinct (by definition). Obviously I would rather see only one entry per category name.

I've already implemented a work around, which is to simply make the query above and then copy the results into an ArrayAdapter. My reason for this post is to see if there was a more elegant solution to this odd little problem and start a discussion on what I could be doing better. Alternate implementation suggestions, such as using different kinds of controls or adapters, are very welcome.

Woodward

Answer 1

Here's the query I ended up with:

SELECT _id, category FROM table_name GROUP BY category;

I used the rawQuery() function on an SQLiteDatabase object to carry this out. The "GROUP BY" piece was the key towards getting the right results, so thanks to user Sagar for pointing me in the right direction.

Do consider user Al Sutton's answer to this question as well, as it may be a more elegant solution to this problem.

Thanks everyone!

Woodward

Answer 2

I'd suggest having a separate table with just _id & category in it which contains one row per unique category. Your data rows can then replace their category field with the _id from the category table.

This has the added advantage you can change the category in the categories table and it will show up in all entries in that category.

Winery

Ooh, this looks like excellent database design to me! For the sake of keeping my content provider as simple as possible, I've been instructed to keep the number of tables to a minimum, or else I'd be doing as you suggest. Thanks for adding this! – Woodward

Your content provider can handle brining the category name in and out of the rows supplied to it and returned from it by using a multi-table query. Have a look at the SQL "join" instruction. – Winery

Answer 3

SELECT DISTINCT category,_id FROM table GROUP BY category;

I think this should give you what you are looking for. The results from this will be the category, and the first _id for that category. You can ignore the second column (_id).

Imbroglio

The DISTINCT token makes this not work, but GROUP BY was an excellent suggestion! – Woodward

Answer 4

You can specify an _id field alias in your select statement that is just a constant value, for example:

SELECT DISTINCT 0 _id, category FROM table;

Evocative

Answer 5

Better yet, I solved this problem by using:

SELECT DISTINCT category AS _id FROM table

Now, you have a column with the name _id which has what you want in it

Wassyngton