Full Text Search (Part 6)

Heretofore, I’ve been working with an “OR” implementation of Full-Text Search (FTS). This sort of implementation takes one or more keywords, and returns all the objects which match one or more of them. I’d been avoiding an “AND” implementation (which only returns objects which contain all the specified keywords) because I suspected that the Core Data/SQLite combination would make a hash of the necessary query plans, but this turns out not to be the case. So today: “AND” style FTS!

NSPredicate

The only difference between “AND” and “OR” FTS is the predicate we use to filter the results. Here’s the NSPredicate construction code we’ve been using for the “OR” implementation:

if ([self.keywords count])
{
	fetchRequest.predicate = [NSPredicate predicateWithFormat:@"ANY words IN %@",self.keywords];
}

To build an “AND” predicate, the code is just a bit more complicated:

if ([self.keywords count])
{
	NSMutableString* s = [NSMutableString stringWithString:@"(ANY words = %@)"];
	for (NSInteger i = [self.keywords count]-2; i >= 0; i--)
	{
		[s appendString:@" AND (ANY words = %@)"];
	}
	fetchRequest.predicate = [NSPredicate predicateWithFormat:s argumentArray:[self.keywords allObjects]];
}

SQL

If you’re interested, Core Data turns this predicate into the following query (assuming 3 search terms, and prettying it up a bit for printing):

Select Distinct 0, t0.Z_PK
From ZTEXT t0
Left Outer Join Z_1HITS t1
On t0.Z_PK = t1.Z_2HITS
Left Outer Join Z_1HITS t3
On t0.Z_PK = t3.Z_2HITS
Left Outer Join Z_1HITS t5
On t0.Z_PK = t5.Z_2HITS
Where (t1.Z_1WORDS = ? And t3.Z_1WORDS = ? And t5.Z_1WORDS = ?)
Order By t0.ZSORTPREFIX Collate NSCollateLocaleSensitive

This runs in about 0.13s on my iPhone 3G; that’s not great, but not horrible either. Considering that I didn’t expect the query to stay on the indices at all, I’m pretty happy. Which proves once again, I suppose, that low expectations are the key to happiness.