CODEWITHSUNDEEP
iphonemysqlsqlitecore-data
Kyle Decot
Kyle Decot

Reputation: 20815

How to do this MySQL Query in Core Data?

How would I do the equivalent of this statement in Core Data? Basically I want to get a list of the 10 most occurrences of foo along w/ how many times it occurs.

Basically I'm recording all of my users searches as individual entries in Core Data. I want to be able to select their 10 most searched items and display them in order starting w/ the most popular. So basically I want something like:

etc.....

Thanks for any help you can provide. I'm still new to Core Data.

  SELECT foo,
         COUNT(foo) AS occurances 
    FROM bar 
GROUP BY foo 
ORDER BY occurances DESC 
   LIMIT 10;

Upvotes: 4

Views: 968

Answers (2)

elcool
elcool

Reputation: 6141

First, create a Core Data Entity that has: userSearchTerm and userSearchDate. And create a record everytime a search is done.

Start the code by declaring the fetch request. And use NSPredicate to filter by date:

NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
NSEntityDescription *entity = [NSEntityDescription entityForName:@"UserSearches" inManagedObjectContext:managedObjectContext];
[fetchRequest setEntity:entity];

NSPredicate *nameFilter = [NSPredicate predicateWithFormat:@"(userSearchDate like[cd] %@) AND (userSearchTerm like[cd] %@)",weekName,termToFind];
[fetchRequest setPredicate:nameFilter];

The [cd] in the like filter specifies it's not case-sensitive. You may also need to change this filter depending on the date and format you want to use

Then, set the fetchResultType as NSDictionaryResultType:

[fetchRequest setResultType:NSDictionaryResultType];

This will return a dictionary which you can use.

To use aggregate functions you need to use NSExpressionDescription

This is an extensive code and I'm not able to test it correctly for you, but you can read it here. On section Fetching Specific Values, it describes how to do the aggregate functions: http://developer.apple.com/mac/library/documentation/Cocoa/Conceptual/CoreData/Articles/cdFetching.html

For a reference of all the available aggregate functions for NSExpression go to function expressionForFunction:arguments: where the list is located.

Upvotes: 1

falconcreek
falconcreek

Reputation: 4170

Core Data does not support aggregation expressions. I suggest that you change your model. Your entity say "Search" will have two attributes, searchTerm and occurrences. When performing a new search, check if the given searchTerm exists already, if so increment the occurrences if not insert a new entity. When you want to show the top ten, you can sort the objects by occurrences and display the first 10 items.

UPDATE: Based on your need to present top searches for today/month/year instead of "all time" given in the question, as per the documentation you will have to do this in two steps. Assuming your Entity, SearchEvent, has attributes searchTerm and searchDate:

  1. Use NSPredicate to return all SearchEvents after a given date
  2. Iterate over the results to aggregate the number of occurrences of searchTerm. Use NSMutableDictionary for the grouping

This is potentially an expensive operation, so it may make sense for you to keep the results in a cache that you update in the background

Upvotes: 0

Related Questions