Creating a nested set with sqlite and Objective-c

Question

I was struggling with nested sets, specifically building one on the iPhone using sqlite. I found a handy script that I then adapted from PHP. It basically sets the left and right values based on parent ids.

So you need a table where each entry has an 'id' and a 'pid' as well as 'lft' and 'rgt'. I find it easy to map the ids to their respective parent ids, but leave the setting of lft and rgt to a script. The method I am using also considers a field called 'name' because I want to be able to easily retrieve an alphabetically ordered list that is ordered separately in each level of the hierarchy.

- (NSInteger)rebuildNestedSetWithParentId:(NSInteger)aParentId left:(NSInteger)aleft {

 NSInteger right = aleft + 1;

 NSString *query = [NSString stringWithFormat:@"SELECT id FROM categories WHERE pid = %i ORDER BY name ASC", aParentId];

 sqlite3_stmt *statement;

 const char *errorMsg;

 if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, &errorMsg) == SQLITE_OK) {

  while (sqlite3_step(statement) == SQLITE_ROW) {

   NSInteger categoryId   = sqlite3_column_int(statement, 0);
   right = [self rebuildNestedSetWithParentId:categoryId left:right];
  }

  NSString *updateQuery = [NSString stringWithFormat:@"UPDATE categories SET lft=%i, rgt=%i WHERE id=%i", aleft, right, aParentId];
  char *errorMsgUpdate;

  if (sqlite3_exec(database, [updateQuery UTF8String], NULL, NULL, &errorMsgUpdate) != SQLITE_OK) {
   sqlite3_close(database);
   NSAssert1(0, @"Error updating table: %s", errorMsg);
  }
 }   

 return right+1;   
}



[self rebuildNestedSetWithParentId:0 left:0];

Although this is not really a question, I thought it might come in handy for someone.

Answer 1

Nice, really Nice!