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

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.

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)