CODEWITHSUNDEEP
objective-cioscocoa-touchsqlite
Brad Reed
Brad Reed

Reputation: 443

iOS - cellForRowAtIndexPath SQLite sort data?

I am trying to use SQLite to hold data for my app. This database could have thousands of records in, so loading them all into an array on startup probably isn't a good idea. So I load them each individually in cellForForAtIndexPath by id. This works, but I can't sort them... Can anybody think of a better way? I bet it's really simple :L

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *CellIdentifier = @"VerbCell";
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];

    if (!cell) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
    }

    VerbData *verb = [[VerbData alloc] init];
    if(isFiltered)
        verb = [searchResults objectAtIndex:indexPath.row];
    else
    {
        const char *sql = [[NSString stringWithFormat: @"SELECT infinitive, english FROM verbs WHERE id=%d", indexPath.row+1] UTF8String];
        sqlite3_stmt *sqlStatement;
        if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK){
            NSLog(@"Problem with prepare statement: %s", sqlite3_errmsg(db));
        }else{
            while (sqlite3_step(sqlStatement) == SQLITE_ROW) {
                verb.infinitive = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement,0)];
                verb.english = [NSString stringWithUTF8String:(char *)sqlite3_column_text(sqlStatement,1)];
            }
        }
    }

    cell.textLabel.text = verb.infinitive;
    cell.detailTextLabel.text = verb.english;

    return cell;
}

Thanks(:

Upvotes: 1

Views: 741

Answers (2)

pro_metedor
pro_metedor

Reputation: 1176

You can select in order using offset and limit

Row Offset in SQL Server

Belkadmin is right, when you use ORDER BY [ ASCENDING | DESCENDING ] all sorting job will be made by DBMS which is good for mobile if you have remote database, so server side woll do all hard work. You can also use

SELECT ... FROM ... ORDER BY ...OFFSET LIMIT

where: offset - is offset from the beginning of database limit - is the maximum amount of data rows gathered by query

You can then load your table in chunks. Other trick is to query for rows asynchronously as the table scrolls i.e. load first 100 rows but when user is below 70th row another query is made asynchronously and adds data to datasource array

Upvotes: 0

Solidus
Solidus

Reputation: 251

You should add "ORDER BY yourField" on your sql query

Upvotes: 1

Related Questions