NSFNanoSearch, how to make paging of data?

[ohdonpiano]

Dear Tito,

I'm trying to achieve paging of data with NSFNanoSearch used with

a) searchObjectsWithReturnType:error:

or, as an alternative

b) searchObjectsAdded:date:returnType:error:

none seams to work with correct results.

Brief explanation of what I mean by paging:
be able to query on a large set of ordered data of same class, page by page. a page is usually of length LIMIT and index OFFSET.

These are my tests:

1. NSFNanoSearch with LIMIT, OFFSET, sortDescriptor with my own attribute 'creationDate' DESC, function a)

RESULT: the data returned is limited correctly, but is the oldest page, not the recent one! In other words, LIMIT 1 OFFSET 0 return the oldest model saved, not the most recent one.

Looking at the code, if I'm not wrong you decided to sort results after the query. If so, the returned array is effectively sorted, but only within the set of query data, not over the whole database data. This should by why I LIMIT 1 and get the oldest, that is the first data inserted.

2. NSFNanoSearch with function b)

I LIMIT 10, but obtain 35 models, LIMIT is therefore not injected with this method.

Finally, the question:
did I miss something and paging is around the corner? 🍰

For now, I guess that it's possible to COUNT all the models for specific class, than use technique 1) and OFFSET with COUNT - LIMIT * numPage, obtaining a sort of "inversion" of the mechanism.

Thanks in advance and sorry to bother you...

[ohdonpiano]

ADDENDUM:
just now I tried the method LIMIT 10, OFFSET countAll - 10, SORT BY 'myCreationDate' DESC
and got right results.

this could be the final solution, OFFSETting from last to 0, where normally one would OFFSET from 0 to last page.
If so, could be good to explain about paging in the readme ;)

[tciuro]

Hello Daniele,

The problem with OFFSET is that SQLite reads everything up to that point and then it discards the results, so it's not efficient. A better technique is to use the indexes with an inner query to exclude the items up to the specified OFFSET. For example, take the following table called FOOD and a column named NAME:

NAME
=========
Appetizer
Cheese
Dessert
Entrée
Fish
Meat
Salad
Soup

Using NAME as the target column we want to base paging, we'll quickly select Dessert, Entrée and Fish (an OFFSET of 2 with a LIMIT of 3) by executing the following query:

SELECT * FROM FOOD
    WHERE rowid NOT IN ( SELECT rowid FROM FOOD
                         ORDER BY NAME ASC
                         LIMIT 2 )
    ORDER BY NAME ASC
    LIMIT 3

The inner query deals with the OFFSET and the exterior one with the LIMIT, which are the rows you're after. I would use NSFNanoSearch to execute SQL directly via:

- (id)executeSQL:(NSString *)theSQLStatement returnType:(NSFReturnType)theReturnType error:(NSError * __autoreleasing *)outError;

I'll add this trick to the documentation. I hope it helps!

[tciuro]

Hello Daniele, is this working for you?