I have an iOS project written in Objective-C. The data being fetched in the iOS is less compared to web. If I check the data in web it's around 100 but in mobile it's just 4. I know the SQLite does some filtering of data. But how it is done? Is it according to date? But it is not filtering the records according to date as I can see in SQL query. This is my code:
NSString *eNVDQuery = nil;
if (self.filterSegment.selectedSegmentIndex == 0) {
eNVDQuery = [NSString stringWithFormat:@"SELECT NV.NVDId,NV.DateOfDelivery,NV.MovementCommenceDate,NV.TotalNumberOfLivestock AS Livestock, NV.ConsignerPropertyId,NV.DestinationPropertyId,NV.TransporterContactId,NV.NVDType,ND.DestinationName,ND.DestinationPIC,ND.ConsignorSuburb,ND.ConsignorState,ND.ConsignorPostcode,ND.DestinationSuburb,ND.DestinationState,ND.DestinationPostcode, (SELECT M.MobName FROM NVDLivestock NL LEFT JOIN Mob M ON NL.MobId = M.MobId WHERE NL.NVDId = NV.NVDId) AS MobName,(SELECT GROUP_CONCAT(NumberOfLivestock || ' ' ||Description,'\n') FROM NVDLivestockSummary WHERE NVDId = NV.NVDId LIMIT 1) AS Description,NV.IsMobMovement,ND.TransporterDriverName,ND.TransporterBusinessName,ST.StatusName AS NVDstatusName,NV.ResponsibleContactSignDate,NV.NVDStatusId,NV.WaybillNumber,NV.IsAgliveNVD,NV.CentralSerialNumber,NV.MLAWaybillNumber,NV.RecordStatus,SP.SpeciesName,SP.SpeciesCode,SPR.PropertyName AS SourcePropName,DPR.PropertyName AS DestPropName,ND.OriginPIC,ND.TransporterAcknowledgement,ND.SaleAgentName,NV.HasMSAQuestion FROM NVD NV LEFT JOIN NVDDetail ND ON NV.NVDId = ND.NVDId LEFT JOIN Status ST ON NV.NVDStatusId = ST.StatusId LEFT JOIN Species SP on NV.SpeciesId = SP.SpeciesId LEFT JOIN Property SPR ON NV.ConsignerPropertyId = SPR.PropertyId LEFT JOIN Property DPR ON NV.DestinationPropertyId = DPR.PropertyId WHERE NV.IsDeleted IS NULL AND IsAgliveNVD = %d ORDER BY datetime(NV.ResponsibleContactSignDate) DESC",!self.isReceived];
} else if (self.filterSegment.selectedSegmentIndex == 1){
eNVDQuery = [NSString stringWithFormat:@"SELECT NV.NVDId,NV.DateOfDelivery,NV.MovementCommenceDate,NV.TotalNumberOfLivestock AS Livestock, NV.ConsignerPropertyId,NV.DestinationPropertyId,NV.TransporterContactId,NV.NVDType,ND.DestinationName,ND.DestinationPIC,ND.ConsignorSuburb,ND.ConsignorState,ND.ConsignorPostcode,ND.DestinationSuburb,ND.DestinationState,ND.DestinationPostcode, (SELECT M.MobName FROM NVDLivestock NL LEFT JOIN Mob M ON NL.MobId = M.MobId WHERE NL.NVDId = NV.NVDId) AS MobName,(SELECT GROUP_CONCAT(NumberOfLivestock || ' ' ||Description,'\n') FROM NVDLivestockSummary WHERE NVDId = NV.NVDId LIMIT 1) AS Description,NV.IsMobMovement,ND.TransporterDriverName,ND.TransporterBusinessName,ST.StatusName AS NVDstatusName,NV.ResponsibleContactSignDate,NV.NVDStatusId,NV.WaybillNumber,NV.IsAgliveNVD,NV.CentralSerialNumber,NV.MLAWaybillNumber,NV.RecordStatus,SP.SpeciesName,SP.SpeciesCode,SPR.PropertyName AS SourcePropName,DPR.PropertyName AS DestPropName,ND.OriginPIC,ND.TransporterAcknowledgement,ND.SaleAgentName,NV.HasMSAQuestion FROM NVD NV LEFT JOIN NVDDetail ND ON NV.NVDId = ND.NVDId LEFT JOIN Status ST ON NV.NVDStatusId = ST.StatusId LEFT JOIN Species SP on NV.SpeciesId = SP.SpeciesId LEFT JOIN Property SPR ON NV.ConsignerPropertyId = SPR.PropertyId LEFT JOIN Property DPR ON NV.DestinationPropertyId = DPR.PropertyId WHERE NV.IsDeleted IS NULL AND IsAgliveNVD = %d AND (ST.StatusCode = 'Draft' OR ST.StatusCode = 'ConsignerSigned') ORDER BY datetime(NV.ResponsibleContactSignDate) DESC",!self.isReceived];
}
else if (self.filterSegment.selectedSegmentIndex == 2){
eNVDQuery = [NSString stringWithFormat:@"SELECT NV.NVDId,NV.DateOfDelivery,NV.MovementCommenceDate,NV.TotalNumberOfLivestock AS Livestock, NV.ConsignerPropertyId,NV.DestinationPropertyId,NV.TransporterContactId,NV.NVDType,ND.DestinationName,ND.DestinationPIC,ND.ConsignorSuburb,ND.ConsignorState,ND.ConsignorPostcode,ND.DestinationSuburb,ND.DestinationState,ND.DestinationPostcode, (SELECT M.MobName FROM NVDLivestock NL LEFT JOIN Mob M ON NL.MobId = M.MobId WHERE NL.NVDId = NV.NVDId) AS MobName,(SELECT GROUP_CONCAT(NumberOfLivestock || ' ' ||Description,'\n') FROM NVDLivestockSummary WHERE NVDId = NV.NVDId LIMIT 1) AS Description,NV.IsMobMovement,ND.TransporterDriverName,ND.TransporterBusinessName,ST.StatusName AS NVDstatusName,NV.ResponsibleContactSignDate,NV.NVDStatusId,NV.WaybillNumber,NV.IsAgliveNVD,NV.CentralSerialNumber,NV.MLAWaybillNumber,NV.RecordStatus,SP.SpeciesName,SP.SpeciesCode,SPR.PropertyName AS SourcePropName,DPR.PropertyName AS DestPropName,ND.OriginPIC,ND.TransporterAcknowledgement,ND.SaleAgentName,NV.HasMSAQuestion FROM NVD NV LEFT JOIN NVDDetail ND ON NV.NVDId = ND.NVDId LEFT JOIN Status ST ON NV.NVDStatusId = ST.StatusId LEFT JOIN Species SP on NV.SpeciesId = SP.SpeciesId LEFT JOIN Property SPR ON NV.ConsignerPropertyId = SPR.PropertyId LEFT JOIN Property DPR ON NV.DestinationPropertyId = DPR.PropertyId WHERE NV.IsDeleted IS NULL AND IsAgliveNVD = %d AND (ST.StatusCode = 'TransporterSigned' OR ST.StatusCode = 'SaleAgentSigned')ORDER BY datetime(NV.ResponsibleContactSignDate) DESC",!self.isReceived];
}
else if (self.filterSegment.selectedSegmentIndex == 3){
eNVDQuery = [NSString stringWithFormat:@"SELECT NV.NVDId,NV.DateOfDelivery,NV.MovementCommenceDate,NV.TotalNumberOfLivestock AS Livestock, NV.ConsignerPropertyId,NV.DestinationPropertyId,NV.TransporterContactId,NV.NVDType,ND.DestinationName,ND.DestinationPIC,ND.ConsignorSuburb,ND.ConsignorState,ND.ConsignorPostcode,ND.DestinationSuburb,ND.DestinationState,ND.DestinationPostcode, (SELECT M.MobName FROM NVDLivestock NL LEFT JOIN Mob M ON NL.MobId = M.MobId WHERE NL.NVDId = NV.NVDId) AS MobName,(SELECT GROUP_CONCAT(NumberOfLivestock || ' ' ||Description,'\n') FROM NVDLivestockSummary WHERE NVDId = NV.NVDId LIMIT 1) AS Description,NV.IsMobMovement,ND.TransporterDriverName,ND.TransporterBusinessName,ST.StatusName AS NVDstatusName,NV.ResponsibleContactSignDate,NV.NVDStatusId,NV.WaybillNumber,NV.IsAgliveNVD,NV.CentralSerialNumber,NV.MLAWaybillNumber,NV.RecordStatus,SP.SpeciesName,SP.SpeciesCode,SPR.PropertyName AS SourcePropName,DPR.PropertyName AS DestPropName,ND.OriginPIC,ND.TransporterAcknowledgement,ND.SaleAgentName,NV.HasMSAQuestion FROM NVD NV LEFT JOIN NVDDetail ND ON NV.NVDId = ND.NVDId LEFT JOIN Status ST ON NV.NVDStatusId = ST.StatusId LEFT JOIN Species SP on NV.SpeciesId = SP.SpeciesId LEFT JOIN Property SPR ON NV.ConsignerPropertyId = SPR.PropertyId LEFT JOIN Property DPR ON NV.DestinationPropertyId = DPR.PropertyId WHERE NV.IsDeleted IS NULL AND IsAgliveNVD = %d AND (ST.StatusCode = 'Delivered')ORDER BY datetime(NV.ResponsibleContactSignDate) DESC",!self.isReceived];
}
self.envdArr = [[DBHelper getSharedInstance] getRecordsBySQL:eNVDQuery];
self.filteredNVDArr = [self.envdArr copy];
if (self.filteredNVDArr.count > 0) {
self.envdTable.tableFooterView = [[UIView alloc] initWithFrame:CGRectZero];
}
else{
UILabel *lbl = [[UILabel alloc]initWithFrame:CGRectMake(0, 0, 100, 30)];
lbl.backgroundColor = [UIColor colorWithRed:63.0/255.0 green:29.0/255.0 blue:11.0/255.0 alpha:1];
lbl.text = @"No Record Found";
lbl.textAlignment = NSTextAlignmentCenter;
lbl.textColor = [UIColor whiteColor];
[self.envdTable setTableFooterView:lbl];
}
[self searchAutocompleteEntriesWithSubstring:self.searchText.text];
[self.envdTable reloadData];
-(NSArray*)getRecordsBySQL:(NSString*)sql{
__block NSMutableArray *records = nil;
[_dbQueue inDatabase:^(FMDatabase *db) {
FMResultSet *results = [db executeQuery:sql];
while ([results next]) {
if (!records) {
records = [[NSMutableArray alloc] init];
}
[records addObject:[results resultDictionary]];
}
[results close];
}];
return records;
}The
self.envdArreNVDQueryDoes the Sqlite database take limited amount of data? How the data is getting filtered in SQLite database?
