Fetching complex data using FMDB

Question

I am using SQLite in an iOS application and I am using FMDB as a wrapper. This is my database schema :

CREATE TABLE Offer (code TEXT PRIMARY KEY NOT NULL, name TEXT);

CREATE TABLE OffreMarket (codeOffer TEXT NOT NULL,
codeMarket TEXT NOT NULL,
FOREIGN KEY(codeOffer) REFERENCES Offer(code),
FOREIGN KEY(codeMarket) REFERENCES Market(code));

CREATE TABLE Market (code TEXT PRIMARY KEY NOT NULL, name TEXT);

My model objects :

@interface Offer : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;
@property (nonatomic,copy) NSArray *markets;
@end

@interface OffreMarket : NSObject
@property (nonatomic,copy) NSString *codeOffer;
@property (nonatomic,copy) NSString *codeMarket;
@end

@interface Market : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;
@end

For example I am fetching all the offers in the database like this :

- (NSArray *)offers {
// Open database

NSMutableArray *offers = [NSMutableArray new];

FMResultSet *resultSet = [database executeQuery:@"SELECT * FROM Offer"];
while ([resultSet next]){

   Offer *offer = [Offer new];
   offer.code = [resultSet stringForKey:@"code"];
   offer.name = [resultSet stringForKey:@"name"];

   // Get the markets for each offer
   FMResultSet *marketResultSet = [database executeQuery:@"SELECT * FROM  OffreMarket WHERE codeOffer = ?",offer.code];

   NSMutableArray *offers = [NSMutableArray new];
   while ([marketResultSet next]) {
      OffreMarket *offerMarket = [OffreMarket new];
      ....
     [offers addObject:offerMarket];
   }

  market.offers = [offers copy];
}

return [offers copy]

}

This is working but it takes time because I am using many SQL requests to fetch all the Offers and the corresponding Markets.

Can i avoid many SQL requests to fetch all the Offers with the corresponding markets? Thanks for your answers

Lines

Answer 1

What I can suggest is:

• Refine your SQL statement. Instead of 2 loops, you can simply change your statement into "SELECT * FROM OffreMarket WHERE codeOffer IN (SELECT code FROM Offer)". If you want to use column "name" in table "Offer", you can join two tables "codeOffer" and "Offer". The rule of thumb here is to avoid too many loops but try to combine or refine your SQL statement.

• Create index for column "code" in table "Offer". It will speed up your searching a lot. For example, once in one of my project, I had to work on a SQL table with 36K records. With a simple index set to the primary key column, I managed to reduce the searching time on that table by 10 seconds.

Perreault

Thanks for your answer. Can you please give a code example how to do one SQL request to fetch the Offers and the corresponding Markets for each offer ? (I don't understand how to parse the same object FMResultSet to have the Offers and the Markets – Lines

Answer 2

In this case you can get the results with just one query:

select * from Offer
left outer join OffreMarket OM on (OM.codeOffer = Offer.code)

Grin

Answer 3

If you are using FMDB as a wrapper than here is your answer:

@try {

    // Select Contact Details From Modules
    NSString *selectSQL = [NSString stringWithFormat:
                           @"SELECT * FROM %@ INNER JOIN %@ ON %@.%@=%@.%@ ;",
                           OffreMarket,
                           Offer,
                           OffreMarket
                           code
                           Offer
                           code];

    //NSLog*(@"Get All Offers select SQL: %@", selectSQL);
    FMResultSet *resultSet = [db executeQuery:selectSQL];
    NSMutableArray *marketOffers = [[NSMutableArray alloc]init];

    while ([resultSet next]) {

        // Create Offers Details Modal
        Offer *offer = [[Offer alloc] init];

         offer.code = [resultSet stringForKey:@"code"];
         offer.name = [resultSet stringForKey:@"name"];

        [marketOffers addObject: offer];
    }
    return (NSArray *)infos;
}
@catch (NSException *exception) {

    //NSLog*(@"%@ : %@",exception.name,exception.reason);
    return nil;
}
return nil;

Try above Code..it will get you all the data in minimum time. FMDB is nice choice for database operation.

Maryettamaryjane

But i would like the offers (Offer) objects and all the corrsponding relations ( i mean for each Offer objet the liste of OffreMarket) and the same thing for all OffreMarket objets. Your code is just fetching the Offer objects and not the corresponding relations. Clear ? – Lines

see my updated answer. Inner join query will help you to find out your question. – Maryettamaryjane

And how you will parse the resultSet ? – Lines

You will get all the data in NSArray and than with Key value coding or From NSObject Modal you can get the data from NSArray. – Maryettamaryjane

My problem is how to get the : @property (nonatomic,copy) NSArray *markets; of each offer ( Offer) objects. – Lines

i think not to worry about it..from the above query all the data is in your array. than use the NSArray through UIViewController to show data and click functionality in List View. just like below example. OfferMarket *offerDetails = ["YOUR ARRAY" objectAtIndex: 0]; – Maryettamaryjane

You have not understand the problem. Sorry for my english. – Lines

ok here is my id [email protected] add text file with steps where you are getting problem.i will reply with answer. – Maryettamaryjane