CODEWITHSUNDEEP
androiddatabasesqlite
daniel_c05
daniel_c05

Reputation: 11518

SQLite Change Sort Order for items on database

So imagine this is a quick mockup of my database:

enter image description here

The items from the database are presented to the user per list, each list being displayed on a new fragment, which are displayed on a viewpager. So let's say in this hypotetical case, there would be two fragments on the viewpager, first fragment would display first_list and second fragment would display second_list. Here's the code for that query: 

 public static Cursor getListItems (final Context context, String listName) {
    if (mDatabase == null || !mDatabase.isOpen())
        open(context);  //This gets the writable db.

    String where = LIST_NAME + " = '" + listName + "'";
    return mDatabase.query(TABLE_LIST_ITEMS, PROJECTION_LIST_ITEMS, 
    where, null, null, null, SORT_ORDER);
}

Where SORT_ORDER is order_in_list, this works well, to begin with.

Now, the listviews are re-arrangeable using a public library, which attempts to allow the user to control the order of the items in each list. Here's where I am having issues, there is no add(int index, Object object) for the cursor, or some other easy way to manage the sorting. I first thought I could simply call mDatabase.update() to change the value for order_in_list but that works, but the results are not as intended. For example, user drags item two to position zero, remeber: zero-index values, we would now have two items with order_in_list as zero. And although I can call mDatabase.update() on item one to update his position to one, imagine how much work that'd be to handle several items on a well-formed database.

Does anyone have any good suggestions on how I could work this out? I thought I had been clever by adding the extra col for sorting purposes :(

INB4:

Yes, I Know arrays handle this well. But the database doesn't only store 4 cols, it has many more fields. Populating arrays each time from the database would be a waste of time and effort. And I would, anyways, have to write back to the database when the app is closed.

EDIT So I changed the listview to only display one String of text, and further columns upon actual clicking on the item (and therefore displaying a new fragment with the specified list item data). This allowed me to simply keep an ArrayAdapter which easily handles the drag and drop. During onStop, I update the reference only if there was a change that required to be saved: 

@Override
public void onStop() {
    if (updateDbOnExit) {
        //Update rows on database.
        for (int i = 0; i < items.size(); i++) {
            //Set the order in list be the actual order on the array.
            Constants.LogMessage("Updating db content");
            DbManager.moveListItemTo(getActivity(), items.get(i), i);
        }
            updateDbOnExit = false;
    }
    super.onStop();
}

Where MoveListItemTo updates the value for order_in_list: 

 public static void moveTaskItemTo (final Context context, String item, int to) {
    if (mDatabase == null || !mDatabase.isOpen())
        open(context);  

    String where = COL_CONTENT + " = '" + item+ "'";

    ContentValues mContentValues = new ContentValues();
    mContentValues.put(ORDER_IN_LIST, to);  
    int rows = mDatabase.update(TABLE_LIST_ITEMS, mContentValues, where, null);

    Constants.LogMessage(rows + " row updated. Item moved to position: " + to);

    close();
}

That will work for now. However, I am still interested on knowing if there is an alternate way, especially when for example, the adapter is using data from more than one column on the database, and is therefore required to use a CusorAdapter and not a regular ArrayAdapter, which in turn requires the Database itself to update upon each Drag and Drop to reflect the change on the UI via cursorAdapter.swapCursor(). As stated, updating ALL of the items on a database upon each drag (which realistically doesn´t happen that often btw), is expensive, updating only Two rows, would be a saner choice.

Upvotes: 2

Views: 3293

Answers (2)

Tim
Tim

Reputation: 8921

I just meant I wanted a more effective way to update the fields in the db, rather than manually updating each and every single row

Make the user-specified-order column a decimal, not an integer. Then you need to update only the moved row(s).

Allow negative numbers.

0.00 cat
1.00 aardvark
2.00 wolf
3.00 dog

If "dog" is dragged above "wolf" then "dog" becomes 1.50 and no need to change other rows. If "aardvark" is dragged above "cat" (special case -- prepending to list rather than inserting between rows) then subtract 1 from the topmost value, and "aardvark" becomes -1.00.

This will require you to know the values of the adjacent rows, but you won't have to update them. Only the moved row's value must change.

Upvotes: 6

Tim
Tim

Reputation: 8921

I would suggest that you have an additional column, user_specified_order which would represent the user's reordering of the rows in the UI via drag-drop.

You must update each row when its user_specified_order value is invalidated by the drag-drop repositioning. When to persist that value is up to you -- either at the "end" of the user's manipulations, however that be defined (e.g. click on Save button) or after each drag/drop if there is no clearcut UI indicator of "end of manipulation".

EDIT: ContenProvider in Android: Android - Can you update a Cursor for SQLite results?

Android SQLite transactions: Android Database Transaction

Upvotes: 1

Related Questions