jeudi 23 avril 2015

Joining Tables via Foreign Keys and FMDB

I'm trying to join two tables in FMDB but not sure to go about doing it correctly. I have a table recipesCocktails which is attached to the table recipesIngredients via my recipeName column. I am trying to display the ingredients in a UIlabel of my detailviewcontroller, however they don't display like the other columns from recipesCocktails.

Basically I turned on foreign keys via PRAGMA foreign_keys with FMDatabase. I have no idea on how to go about the next step. I don't need to create a new table from the both existing ones, I just need to read from the recipesIngredients and attach those to the UIlabels for the corresponding ingredient column into my detailviewcontroller.

I'm guessing I have to create an if statement either in my NSMutableArrays that I created for both tables or I create one in my viewDidLoad method?

I thought maybe just calling self.title = cocktails.recipeName would do the trick considering recipeName is the linking column, but it's a no go.

CocktailsDetailTableViewController.m

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view.

    self.title = cocktails.recipeName;

    self.recipeIngred1.text = cocktailIngredients.recipeIngred1;
    self.recipeIngred2.text = cocktailIngredients.recipeIngred2;
    self.recipeIngred3.text = cocktailIngredients.recipeIngred3;
    self.recipeIngred4.text = cocktailIngredients.recipeIngred4;
    self.recipeIngred5.text = cocktailIngredients.recipeIngred5;
    self.recipeIngred6.text = cocktailIngredients.recipeIngred6;
    self.recipeIngred7.text = cocktailIngredients.recipeIngred7;
    self.recipeDirections.text = cocktails.recipeDirections;
    }

CocktailsListTableViewController.m

- (NSMutableArray *)recipeCocktails {
    recipeCocktails = [[NSMutableArray alloc] init];

    NSString *databasePath = [(AppDelegate *) [[UIApplication sharedApplication] delegate] databasePath];

    FMDatabase *fmDB = [FMDatabase databaseWithPath:databasePath];

    if(![fmDB open])
    {
        NSLog(@"Could not open DB, try again");
        return nil;
    }

    if ([fmDB open]) {
    NSString *foreignkeys = @"PRAGMA foreign_keys";
    FMResultSet *rsfk = [fmDB executeQuery:foreignkeys];
    int enabled;
        if ([rsfk next]) {
            enabled = [rsfk intForColumnIndex:0];
        }
        [rsfk close];
        if (!enabled) {
            // enable foreign keys
            foreignkeys = @"PRAGMA foreign_keys=ON";
            [fmDB executeUpdate:foreignkeys];
            // check if successful
            foreignkeys = @"PRAGMA foreign_keys";
            FMResultSet *rsfk = [fmDB executeQuery:foreignkeys];
            if ([rsfk next]) {
                enabled = [rsfk intForColumnIndex:0];
            }
            [rsfk close];
        }
    }

    FMResultSet *results1 = nil;
    results1 = [fmDB executeQuery:@"SELECT * FROM recipesCocktails"];
    NSLog(@"result %@ ",results1);
    if ([fmDB hadError]) {
        NSLog(@"DB Error %d: %@", [fmDB lastErrorCode], [fmDB lastErrorMessage]);
    }
    while ([results1 next]) {
        Cocktails *cocktails = [[Cocktails alloc] init];
//        cocktails.recipeID = [results1 intForColumn:@"recipeID"];
        cocktails.recipeName = [results1 stringForColumn:@"recipeName"];
        cocktails.recipeGlass = [results1 stringForColumn:@"recipeGlass"];
        cocktails.recipeShaker = [results1 stringForColumn:@"recipeShaker"];
        cocktails.recipeDirections = [results1 stringForColumn:@"recipeDirections"];

        [recipeCocktails addObject:cocktails];
    }

    [fmDB close];

    return recipeCocktails;


}

I apologize ahead of time if this question is dumb/menial, but I'm new to both how databases work/sqlite works and iOS programming. I've only come across answers regarding turning foreign keys on and not on reading the shared column. Any help would be greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire