This seems like a nasty problem I've run into...

Anyone who's used a Palm PDA should be familiar with what I'm trying to do. In my application I want to have a list of categories which are stored in a database table. The other items in my application can then be assigned to any one of those categories, so the logical UI choice here would be to build a Spinner and retrieve a list of all Categories as a Cursor, then add the Cursor to the SpinnerAdapter for this Spinner. Easy.

Code: Select all

Spinner cat_spin = (Spinner)footer.findViewById(R.id.cat_spinner);
Cursor catcursor = mDbHelper.fetchAllCategories();
SpinnerAdapter spinadapter = new SimpleCursorAdapter(this,
      android.R.layout.simple_spinner_item,
      catcursor,
      new String[] { DbAdapter.KEY_CATEGORY_NAME },
      new int[] { android.R.id.text1 });
cat_spin.setAdapter(spinadapter);
cat_spin.setOnItemSelectedListener(this);


The problem comes in when displaying my items in a ListView. Just like the good ol' Palm, I want to be able to filter that ListView by category using a Spinner to select a single Category, but I also want an "All" option in that Spinner so the user can see the entire list unfiltered. The problem is, how do I add that "All" option to the Cursor? The only way I can come up with is to add an "All" record to the database, but that would be a huge database level hack (since I'd be calling it a category but I can't actually use its index in a foreign key field) and would require filtering it out in every other category query.

This should be easier to do. Isn't it?

It seems like this is easy to do with a ListView since you can add headers and footers, but not so with a Spinner. WTF?

I decided to give up on the easy approach and came up with a way to do this using an ArrayAdapter. The problem is I still needed to keep track of database ID's in the Spinner, so I made my own simple object that I could pull the ID out of later.

Almost all of the functionality is embedded in a single class CategorySpinnerHelper. The ListActivity's onCreate method instantiates a CategorySpinnerHelper object, then calls method initCatSpinner with the Spinner object (returned with findViewById) and the initial Category ID. Next, the onCreate method sets an OnItemSelectedListener.

The onItemSelected method just has to call the handleCategoryChange method to get the new category and does not have to worry about the Edit case. onItemSelected then should take that category ID and rebuild the Cursor that's driving the ListView.

Code: Select all

import java.util.LinkedList;

import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Spinner;

/**
* A class for handling any UI screen with a category filter Spinner
* @author BenR
*/
public class CategorySpinnerHelper {
   
   // These are special items in the Category list.
   public static final int ID_ALL=-1;      // All is used to show items in any
                                 // category. Doing this involves knowing
                                 // what queries to make to the database
                                 // adapter so the logic behind this is
                                 // handled by the Activity.
   public static final int ID_EDIT=-2;      // Edit launches a separate activity
                                 // to edit the available categories.
                                 // This does not change the currently
                                 // selected category.
   
   protected Context mCtx;
   protected Spinner mCatSpinner;
   
   // Store the current spinner position internally. This is used so we can
   // revert back to it if the user selects "Edit Categories" and comes back
   // to the Activity.
   protected int mLastCategoryPosition;

   /**
    * We can't just build the category list using a Cursor adapter because we need
    * to show the "All" option to show all categories. This means we have to build
    * an ArrayAdapter composed of objects to show Spinner items for. This is that
    * object.
    *
    * @author BenR
    */
   public static class CategorySpinnerItem {
      
      private long mId;
      private String mName;
      
      public long getID() { return mId; }
      protected void setID(long id) { mId = id; }
      public String getName() { return mName; }
      public void setName(String name) { mName = name; }
      
      public CategorySpinnerItem(String text, long id) {
         mId=id;
         mName=text;
      }
      
      public String toString() {
         return mName;
      }
   }
   
   public CategorySpinnerHelper(Context c) {
      mCtx=c;
   }
   
   /***
    * Initialize the Spinner that allows the user to select a category. After
    * initializing, the caller must assign an onItemSelectedListener to the
    * returned Spinner.
    * @param cat_spin The Spinner in the layout
    * @param initial_category The ID of the category to display first (or it could be ID_ALL)
    * @param all_categories The Cursor of all categories returned from MyDbAdapter
    * @return The initialized Spinner
    */
   public Spinner initCatSpinner(Spinner cat_spin, long initial_category, Cursor all_categories) {
      mCatSpinner=cat_spin;

      // First build the Category list. This begins with the Add option, includes
      // every category in all_categories, then ends with the Edit Categories
      // option.
      LinkedList<CategorySpinnerItem> categories = new LinkedList<CategorySpinnerItem>();

      categories.add(new CategorySpinnerItem(
            mCtx.getResources().getString(R.string.all),
            ID_ALL));
      while(all_categories.moveToNext()) {
         String name = all_categories.getString(all_categories.getColumnIndexOrThrow(MyDbAdapter.KEY_CATEGORY_NAME));
         long id = all_categories.getLong(all_categories.getColumnIndexOrThrow(MyDbAdapter.KEY_CATEGORY_ID));
         categories.add(new CategorySpinnerItem(name, id));
         if(initial_category == id) {
            mLastCategoryPosition=categories.size()-1;
         }
      }
      categories.add(new CategorySpinnerItem(
            mCtx.getResources().getString(R.string.edit_categories),
            ID_EDIT));
      
      if(initial_category == ID_ALL) {
         mLastCategoryPosition=0;
      }

      // Build the spinner adapter and set up the Spinner
      ArrayAdapter<CategorySpinnerItem> spinadapter = new ArrayAdapter<CategorySpinnerItem>(mCtx,
            android.R.layout.simple_spinner_item,
            android.R.id.text1,
            categories);
      spinadapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
      mCatSpinner.setAdapter(spinadapter);
      mCatSpinner.setSelection(mLastCategoryPosition);
      mCatSpinner.setPrompt(mCtx.getResources().getString(R.string.choose_category));

      return mCatSpinner;
   }

   /**
    * Call this from your onItemSelected method to get the new category ID.
    * @param parent The AdapterView where the selection happened
    * @param position The position of the view in the adapter
    * @return The new Category ID
    */
   public long handleCategoryChange(AdapterView<?> parent, int position) {
      CategorySpinnerItem item = (CategorySpinnerItem) parent.getItemAtPosition(position);
      long id=item.getID();
      switch((int)id) {
      case ID_EDIT:
         // Set the Spinner back to where it was
         mCatSpinner.setSelection(mLastCategoryPosition);
         // Launch a Edit Categories Activity
         Intent i = new Intent(mCtx, EditCategories.class);
         mCtx.startActivity(i);
         return ((CategorySpinnerItem)parent.getItemAtPosition(mLastCategoryPosition)).getID();
      // The caller's category change handler can handle everything else
      case ID_ALL:
      default:
         mLastCategoryPosition=position;
         return id;
      }
   }
}


I'm not finished writing my application yet so this is not fully tested. The All case, selecting individual categories, and the list reverting on the Edit case have all been tested; it's just that I don't have a EditCategories.class yet.

I like to make SQL do the work for me. Do a standard SELECT query but UNION it with that "all" item you want.

SELECT 0, 'all' FROM categories
UNION
SELECT catID, category FROM categories

Now you have a "all" category with an ID = 0 at the top of your spinner list.

Binary Sheep wrote:I like to make SQL do the work for me. Do a standard SELECT query but UNION it with that "all" item you want.

SELECT 0, 'all' FROM categories
UNION
SELECT catID, category FROM categories

Now you have a "all" category with an ID = 0 at the top of your spinner list.

This is a nice solution, but you may have to alias the fields.

Code: Select all

SELECT 0 AS catID, 'all' AS category FROM categories
UNION
SELECT catID, category FROM categories