2

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.

daniel_c05
  • 11,438
  • 17
  • 60
  • 78
  • `Where SORT_ORDER is order_in_list...`. Order_in_List column, if it were used in a back-end query, would be ".... order by order_in_list" ? – Tim Mar 10 '13 at 13:15
  • Not sure what you mean by a back-end query. But in my query I am passing "order_in_list" as the "order by" parameter of the query method. – daniel_c05 Mar 10 '13 at 13:21

2 Answers2

6

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.

Tim
  • 8,669
  • 31
  • 105
  • 183
  • This approach is very rare, imagine on your example, "dog" was dragged above "wolf", making "dog" be 1.5, what if we'd now drag "aardvark" after "dog", what number would that be? You would be running into several decimals there, and not using a specific amount to add or substract from the count. I am accepting your first answer I guess, since i turned out to use arrays rather as placeholders. – daniel_c05 Mar 17 '13 at 12:38
  • 1
    If dog is dragged above wolf dog becomes 1.5. If we then drag aardvark after dog, the value of aardvark is between 2.0 and 1.5, e.g. 1.75. The decimal number allows for many thousands and thousands of repositionings by the user -- finite, true, but adequate for a real-world application. The limits are never going to be reached by a person doing drag-drop unless they continued to drag-drop in the afterlife. – Tim Mar 17 '13 at 12:44
  • But how would you dinamically determine what number to add or deduct from the count, say, first how do you tell the app to deduct 0.5, 0.25, 0.125, etc from the current position? – daniel_c05 Mar 17 '13 at 12:52
  • 2
    The moved row is always positioned halfway between the row it follows and the row it precedes. You know those two values, and you calculate the midway position. `movedRowPos = rowBeforePos + (rowAfterPos - rowBeforePos) / 2.00` – Tim Mar 17 '13 at 13:43
  • 2
    Special cases are when row is dragged to become the first row or the last row. Then you simply subtract or add 1 to first/last row's positional value, respectively. – Tim Mar 17 '13 at 13:48
  • @Tim What is the purpose of adding rowBeforePos to the (rowAfterPos - rowBeforePos) / 2.00 computation? – gig6 Dec 09 '20 at 01:41
  • 1
    @gig6: "before" = ahead of it in the display order. You want the new position to fall *between* the values of the preceding and subsequent rows. – Tim Dec 09 '20 at 14:35
1

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

Community
  • 1
  • 1
Tim
  • 8,669
  • 31
  • 105
  • 183
  • That is the column I added, "order_in_list" is my attempt to add an additional column that can store the user-specified order. But as stated, when the user rearranges one item per say, I can only update the value of the item that was moved, and as per the exampe, I would end up with two items that have the same "order_in_list" after a drag. – daniel_c05 Mar 10 '13 at 13:22
  • I assumed _order_in_list was your default order. See my expanded reply. – Tim Mar 10 '13 at 13:25
  • You are correct, the value of all of the rows is technically invalidated after the drag, but how would I update the values of each row after the drag? Simply call update for each and every single row? – daniel_c05 Mar 10 '13 at 13:31
  • I would suggest having a proxy array which keeps the user-specified order, and then update the database from that proxy array at whatever point you determine the user has finished moving things around. You want to wrap that update in a transaction as it involves multiple rows. I've worked a lot with SQLite but not with Android, so you'd have to ask someone else how to wrap the update in a transaction in Android. – Tim Mar 10 '13 at 13:33
  • Once you have "detached" the UI sort order from the cursor sort order, by allowing the user to reposition rows via drag/drop, your question is no longer about database sort order but about how to update the rows in the cursor to reflect the UI sort order. You simply want to write a value to each row reflecting its order in the UI. Your question should be edited to reflect what you're actually trying to accomplish. It really has nothing to do with sort order per se. – Tim Mar 10 '13 at 13:39
  • I mean, updating the UI to reflect the sort order was not a problem, since it is a Cursor, I simply called mCursorAdapter.swapCursor(), and that would reflect changes. 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. – daniel_c05 Mar 10 '13 at 14:19