user2872856 user2872856 - 1 month ago 11
SQL Question

Enable UISearchDisplayController to search text contains string

This is my

UISearchDisplayController
that works if I perform search and return the result with matching starting alphabet.
However, what I what to do is to enable searching of text contains string.
For example, if I have data
chocolate chip
and
dark chocolate
, when I type
chocolate
on the search bar, I wish to return both result.

The following code only return
chocolate chip
but not
dark chocolate
:

self.searchDisplayController = [[[UISearchDisplayController alloc] initWithSearchBar:self.searchBar contentsController:self] autorelease];
self.searchDisplayController.searchResultsDelegate = self;
self.searchDisplayController.searchResultsDataSource = self;
self.searchDisplayController.searchResultsTitle = NSLocalizedString(@"Matches", @"");

- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText
{
NSString *text = [searchBar.text stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
if ([text length]) {
[self.db fetchCompletionListWithString:text callback:^(NSArray *list) {
self.prefixArray = list;
[self.searchDisplayController.searchResultsTableView reloadData];
}];
}
}


This is the
fetchCompletionListWithString


- (void)_fetchCompletionListWithString:(NSString *)inStr callback:(void(^)(NSArray *))inCallback
{
if (!inStr || ![inStr length]) {
return;
}

const char *SQL = "SELECT id,title FROM entries WHERE title LIKE ? || '%';";
ObjSqliteStatement *statement = [[ObjSqliteStatement alloc] initWithSQL:SQL db:db];
[statement bindText:inStr toColumn:1];
NSMutableArray *list = [NSMutableArray array];
while ([statement step]) {
NSInteger entryID = [statement intFromColumn:0];
NSString *title = [statement textFromColumn:1];
if (entryID) {
[list addObject:@{kMDIdentifierKey:@(entryID), kMDTitleKey:title}];
}
}
[statement release];
dispatch_async(dispatch_get_main_queue(), ^{
inCallback(list);
});
}


I tried to change the
*SQL
to
"SELECT id,title FROM entries WHERE title LIKE ? '%' || '%';"
. But this do not work.

Any suggestion will be appreciated.
Thanks in advance.

Answer

Depend on where you query the result.

If you query on an Array, use NSPredicate to filter the result like this.

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"name CONTAINS[cd] %@",searchToken];

If you query on Database, use SQL query like that:

SELECT * FROM mytable
WHERE name LIKE '%chocolate%'

Hope this help.

Base on your edit, you can try this way as a tricky (Because your library you use is so old). If you want to handle SQL, i recommend you use Realm

- (void)_fetchCompletionListWithString:(NSString *)inStr callback:(void(^)(NSArray *))inCallback
{
    if (!inStr || ![inStr length]) {
        return;
    }
inStr = [NSString stringWithFormat:@"%%%@%%",inStr];
    const char *SQL = "SELECT id,title FROM entries WHERE title LIKE ? ;";
    ObjSqliteStatement *statement = [[ObjSqliteStatement alloc] initWithSQL:SQL db:db];
    [statement bindText:inStr toColumn:1];
    NSMutableArray *list = [NSMutableArray array];
    while ([statement step]) {
        NSInteger entryID = [statement intFromColumn:0];
        NSString *title = [statement textFromColumn:1];
        if (entryID) {
            [list addObject:@{kMDIdentifierKey:@(entryID), kMDTitleKey:title}];
        }
    }
    [statement release];
    dispatch_async(dispatch_get_main_queue(), ^{
        inCallback(list);
    });
}