CODEWITHSUNDEEP
iossqlobjective-csqlite
jamiemax
jamiemax

Reputation: 189

ios - reorder items in uitableview and apply changes to sqlite database

i'm trying to reorder a table view - have set up the canMoveRowAtIndexPath and moveRowAtIndexPath so that I can drag the cells around when i'm in editing mode, but I need to basically mirror these changes in an sqlite database that I have. i have a column called "orderid" which i'm using to keep track of the order of the cells. My approach has been to basically set the cell being moved to orderid=9999, then add or subtract 1 from from the orderid of the rest of the affected cells, then set the new cell to its destination orderid. i think it's likely to be an sql error but i can't figure it out. if anyone has any advice it would be much appreciated.

these are my methods:

- (BOOL)tableView:(UITableView *)tableView canMoveRowAtIndexPath:(NSIndexPath *)indexPath {
return YES;
}
 - (void)tableView:(UITableView *)tableView moveRowAtIndexPath:(NSIndexPath *)sourceIndexPath toIndexPath:(NSIndexPath *)destinationIndexPath{

long source = (long)sourceIndexPath.row;
long dest = (long)destinationIndexPath.row;

source++;
dest++;

//reorder in db
NSString *sql;
if (source>dest) {
    sql = [NSString stringWithFormat:@"UPDATE entries SET orderid=9999 where orderid=%ld; update entries set orderid = orderid + 1 where orderid<%ld AND orderid>=%ld update entries set orderid=%ld where orderid=9999", source, source,dest, dest];
} else if (source<dest){
    sql = [NSString stringWithFormat:@"UPDATE entries SET orderid=9999 where orderid=%ld; update entries set orderid = orderid - 1 where orderid BETWEEN orderid>%ld AND orderid<=%ld; update entries set orderid=%ld where orderid=9999", source, source, dest, dest];
}

NSLog(@"%@", sql);

char *err;
if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) !=SQLITE_OK) {
    sqlite3_close(db);
    NSLog(@"could not reorder");
} else {
    NSLog(@"rows reordered");
}


}

Upvotes: 0

Views: 527

Answers (4)

Eddy
Eddy

Reputation: 407

I am using pageOrder to create an order in my pages table. This is table :

.s pages
CREATE TABLE pages (              
uuid INTEGER PRIMARY KEY,              
name TEXT,              
pageOrder INTEGER DEFAULT 0              
);

Here is the code :

int fromRow = [sourceIndexPath row];
int toRow = [destinationIndexPath row];

if (toRow != fromRow) {

  FMDatabase* db = [FMDatabase databaseWithPath:[appDelegate dbPath]];
  [db open];

  [db executeUpdate:@"UPDATE pages SET pageOrder = -? WHERE pageOrder = ?",
   [NSNumber numberWithInt:toRow + 1],
   [NSNumber numberWithInt:fromRow + 1]];

  if (toRow < fromRow) {

    [db executeUpdate:@"UPDATE pages SET pageOrder = pageOrder + 1 WHERE pageOrder > ? AND pageOrder <= ?",
     [NSNumber numberWithInt:toRow],
     [NSNumber numberWithInt:fromRow]];


  } else {

    [db executeUpdate:@"UPDATE pages SET pageOrder = pageOrder - 1 WHERE pageOrder > ? AND pageOrder <= ?",
     [NSNumber numberWithInt:fromRow],
     [NSNumber numberWithInt:toRow + 1]];

  }

  [db executeUpdate:@"UPDATE pages SET pageOrder = abs(pageOrder) WHERE pageOrder < 0"];

  [db close];

}

Should be working,

Cheers,

Eddy

Upvotes: 1

Fernando Juarez
Fernando Juarez

Reputation: 31

This Works fine with me!

- (void) tableView:(UITableView *)tableView moveRowAtIndexPath:(NSIndexPath *)sourceIndexPath toIndexPath:(NSIndexPath *)targetIndexPath{

// Define las variables a utilizar: IDorigen hace referencia al ID que tiene la fila antes de moverse e IDDestino hace referencia al ID que va a tener la fila luego de moverse
int IDorigen = (int)[sourceIndexPath row]+1;
int IDdestino = (int)[targetIndexPath row]+1;
int diferencia;

// Define la variable de consulta sobre la base de datos
NSString *consulta;


// Si el IDorigen es menor al IDdestino (es decir, mueve una fila hacia abajo)
if (IDorigen < IDdestino)
{
    // Obtiene la diferencia para saber cuantas filas hay entre IDdestino e IDOrigen
    diferencia = IDdestino - IDorigen;

    // Hace un FOR desde cero hasta diferencia, y a cada fila que encuentra le pone el ID=9000+i. Esto se hace para mandar las filas invoulucradas en el movimientohacia abajo de todo y evitar conflictos de ID
    for (int i=0;i<diferencia;i++){

        // Define la consulta
        consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d", 9000+i,IDorigen+1+i];

        // Ejecuta la consulta
        [self.dbTouchMeCIS executeQuery:consulta];
    }

    // Cuando finaliza el FOR, le asigna a la fila que se está moviendo, el IDdestino deseado definiendo la consulta
    consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d", IDdestino,IDorigen];

    // Ejecuta la consulta
    [self.dbTouchMeCIS executeQuery:consulta];

    // Hace un FOR para asignarle el valor que corresponde a aquellas filas que se le asigno el ID=9000+i
    for (int i=0;i<diferencia;i++){

        // Define la consulta
        consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d",IDorigen+i,9000+i];

        // Ejecuta la consulta
        [self.dbTouchMeCIS executeQuery:consulta];
    }

// Si el IDorigen es mayor al IDdestino (es decir, mueve una fila hacia arriba)
}else if (IDorigen > IDdestino){

    // Obtiene la diferencia para saber cuantas filas hay entre IDdestino e IDOrigen
    diferencia = IDorigen - IDdestino;

    // Hace un FOR desde cero hasta diferencia, y a cada fila que encuentra le pone el ID=9000+i. Esto se hace para mandar las filas involucradas en el movimiento hacia abajo de todo y evitar conflictos de ID
    for (int i=0;i<diferencia;i++){

        // Define la consulta
        consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d", 9000+i,IDdestino+i];

        // Ejecuta la consulta
        [self.dbTouchMeCIS executeQuery:consulta];
    }

    // Cuando finaliza el FOR, le asigna a la fila que se está moviendo, el IDdestino deseado definiendo la consulta
    consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d", IDdestino,IDorigen];

    // Ejecuta la consulta
    [self.dbTouchMeCIS executeQuery:consulta];

    // Hace un FOR para asignarle el valor que corresponde a aquellas filas que se le asigno el ID=9000+i
    for (int i=0;i<diferencia;i++){

        // Define la consulta
        consulta = [NSString stringWithFormat:@"update homeTabla set ID=%d where ID=%d",IDdestino+1+i,9000+i];

        // Ejecuta la consulta
        [self.dbTouchMeCIS executeQuery:consulta];
    }

}

// Recarga la base de datos en la tabla una vez finalizado el movimiento
[self recargarDatos];

}

Upvotes: 0

Almas Sapargali
Almas Sapargali

Reputation: 451

I would suggest using float order_id. And when user reorders item, get order_id of previous and next item, get their mean (float) and save to DB. This way, you need to update only reordered item.

Upvotes: 0

Murat Zazi
Murat Zazi

Reputation: 357

One possibility may be if the numberOfRows in your tableView does not match the number of entries in your database, for example: if you have an ‘Add new row...’ cell on the bottom of the tableView, then you may run into trouble, like I have in the past. My trouble was that the tableView:moveRowAtIndexPath:toIndexPath was returning the wrong sourceIndexPath. Two solutions both solved the problem. First solution: Call [tableview reloadData] in the tableView:moveRowAtIndexPath:toIndexPath:, you may lose the row animation but you will keep the functionality and sourceIndexPath/destinationIndexPath will be updated. Second solution: Don’t use an ‘Add new row...’ cell on the bottom and find alternate solution to add. Good luck!

Upvotes: 0

Related Questions