0

I have an Sqlite table that has a column for play_order.
If I delete an item is there a command to reorder the data to remove the now missing number from the column?

For example, say the table looks like this:

_id | play_order
----------------
1   | 0
2   | 1
3   | 2
4   | 3
5   | 4

I delete _id 3, I'd like to reorder the play_order so _id 4 now has a play_order of 2 and _id 5 has 3 etc...

I can do this in Java (Android), but would rather do it in SQL if possible.

I've tried using a fake row id, but the data is all over the table and can be full of other random data at any point.

I've also thought of doing a subquery and getting all the data, but don't know if I can loop over it or something to set the the order.

As far as Android goes, I've looked into doing a "applybatch" on the contentresolver, but can't get that to work either.

Improve this question
3
  • I can't think of a way to do this with just a SQLite statement. Maybe if you describe why you need to do this, perhaps someone can suggest an alternative that doesn't require doing this. Commented Jul 22, 2015 at 6:16
  • 1
    Actually, I can think of a way using triggers, but I'm not sure it's a good idea. Commented Jul 22, 2015 at 6:19
  • The data is a playlist. I need the play_order to start with 0 and increase as the number of items added grows. But when you delete an item I need to reorder everything else so there is no gaps. I'm using the built in Android content resolver and their table, so I can't change the structure. Commented Jul 22, 2015 at 6:40

3 Answers 3

Reset to default
4

Degen Sharew's answer works fine, but here's an alternative:

In your SQLiteOpenHelper, when you create your database, run this statement:

db.execSQL("CREATE TRIGGER trigger_compress_play_order" +
        " AFTER DELETE ON " + TABLE_NAME + " BEGIN " + 
        " UPDATE " + TABLE_NAME + 
        " SET " + PLAY_ORDER_COLUMN + " = " + PLAY_ORDER_COLUMN + " - 1" + 
        " WHERE " + PLAY_ORDER_COLUMN + " > OLD." + PLAY_ORDER_COLUMN + "; END;");

This will compress the values as you desire no matter how many rows you delete at any time. You don't even have to run a statement after the delete, this does it automatically.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

sure this is better one.
This looks good, but using the built in Android content resolver I don't believe I can edit the table structure. Even if I could I don't think it's a good idea.
I don't understand. Is this your database or another app's database?
Another apps database. It's the built in MediaStore database that Android provides.
In that case I would expect the MediaStore database to handle that for you when you delete an item. If it doesn't then you might want to consider structuring your code such that you don't care about the gaps, since the order is unaffected by the presence of gaps (the items are still in play_order ascending).
|
1

Execute this update statement after you deleted the record ( but you need to store play_order of the deleted record before deleting it). 

update yourTableName set play_order = play_order - 1 where play_order > play_order_deleted;

yourTableName => Name of the table
play_order_deleted => The play_order of the record you deleted.

Improve this answer

4 Comments

This works, although it's worth mentioning that you must first read the play_order of the row about to be deleted and requires only deleting a single row at a time.
@Karakuri I already mentioned this. He needs to have the play_order of the row being deleted. ?
you are right, I missed that part, but the part about only deleting one row at a time for this to work is still important.
Deleting one row at a time isn't difficult, but I'm concerned with outside sources editing the data. Because I'm using the Android MediaStore, it's available to all apps, and they can change things around too. It would be nice to ensure I've always left things in the best order I can.
1

Playlists.Members has a static method moveItem() which should take care of updating the play orders in the playlist. If you know the size of the playlist, try first moving the item to the end of the playlist, then delete the item.

private boolean deleteFromPlayList(long playlistId, int position, int playlistSize) {
    ContentResolver cr = getContentResolver(); // or pass it in method args
    // I'm assuming play order starts at 1, but if it starts at 0, 
    // then change this check accordingly
    if (position != playlistSize) {
        // this will return true on success, in case you want to check that it moved
        Playlists.Members.moveItem(cr, playlistId, position, playlistSize);
    }
    return cr.delete(...) > 0;
}

One thing to note is that moveItem() will result in a Uri notification when it moves the item, so if you have a Loader or something listening for that, it will get triggered. This may or may not be an issue. You might be able to avoid this by using applyBatch(), in which case you'll have to build the move operation yourself. Based on the source code around line 1653, it should be an update operation.

ArrayList<ContentProviderOperation> ops = new ArrayList<ContentProviderOperation>(2);
Uri moveUri = Playlists.Members.getContentUri("external", playlistId)
        .buildUpon()
        .appendPath(Integer.toString(position))
        .appendQueryParameter("move", "true")
        .build();
ops.add(ContentProviderOperation.newUpdate(moveUri)
        .withValue(Playlists.Members.PLAY_ORDER, playlistSize)
        .build());
Uri deleteUri = Playlists.Members.getContentUri("external", playlistId)
        .buildUpon()
        .appendPath(Long.toString(playlistMemberId))
        .build();
ops.add(ContentProviderOperation.newDelete(deleteUri).build());
contentResolver.applyBatch(MediaStore.AUTHORITY, ops);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.