1

I'm new to iOS development and I'm trying to understand how to use a subquery with NSPredicate so that I can limit rows to only the most recent entry for a particular user. See the table below. This all works with sqlite.

Here is a sample table and data that represents the problem well.

BEGIN TRANSACTION;
CREATE TABLE `user_orders` (
`timestamp` INTEGER,
`username`  TEXT,
`items_ordered` TEXT
);

INSERT INTO `user_orders` (timestamp,username,items_ordered) VALUES (0,'joe','pencil');
INSERT INTO `user_orders` (timestamp,username,items_ordered) VALUES (1,'joe','coffee');
INSERT INTO `user_orders` (timestamp,username,items_ordered) VALUES (2,'joe','book, gum, coffee');
INSERT INTO `user_orders` (timestamp,username,items_ordered) VALUES (1,'sam','tea');
INSERT INTO `user_orders` (timestamp,username,items_ordered) VALUES (5,'sam','book');
COMMIT;

I need to translate this statement into a NSFetchRequest.

SELECT t1.* FROM user_orders AS t1 
JOIN (SELECT username, MAX(timestamp) AS timestamp FROM user_orders GROUP BY username) AS t2 
WHERE t1.username = t2.username AND t1.timestamp = t2.timestamp

Here is what should be returned.

timestamp, username, items
"2"        "joe"    "book, gum, coffee"
"5"        "sam"    "book"

I've dug into the documentation, but I'm struggling with finding examples. I'm not sure how to reference the outer table from the subquery.

Also, is there an easy way to test queries as you program inside XCode?

TALE
  • 960
  • 13
  • 22
  • Trying to approach and query a core data store as if it were a SQL database is going to give you lots of grief. I would rewrite this question to show your data model and what information you'd like to retrieve, rather than showing a table and how to translate SQL statements. While there is a sqlite database underneath the hood, Core Data is a different beast altogether. – JAB Nov 11 '16 at 01:28
  • Some of the discussion in this answer feels relevant: http://stackoverflow.com/questions/6890079/subquery-in-core-data?rq=1 – JAB Nov 11 '16 at 01:29
  • I have control over my schema at this point. I guess I could create recent_order object that would contain the most recent order for each user. My concern with this is that it would be possible for it to get out of sync in the event of an error. Is this the way you guys would do it and if so, how would you ensure that the two objects collections stayed in sync? – TALE Nov 14 '16 at 14:26
  • I didn't find an easy way to do what I wanted, so I gave up on trying to use Core Data and instead pulled in sqlite. I decided to use Sqlite.Swift. So far so good. – TALE Nov 16 '16 at 19:02

0 Answers0