Apex Code Development You need to sign in to do that
Don't have an account?
David_FB PHP: Limited result sets (queryMore() ?)
Hi all.
I'm trying to achieve pagination in a PHP application where I can query 20 rows at a time per page. The LIMIT keyword in the query is no use because there is no way to specify an offset (as you can with MySQL; LIMIT 0, 20).
I saw a few mentions of queryMore(), though with this, you are limited to a minimum of 200 rows, and you can't jump to a say the 3rd batch of results with this method. You have to iterate through all records, until you've called queryMore() the correct number of times.
$soql = 'SELECT Id, Name FROM Contact'; $sf->setQueryOptions(new QueryOptions(200)); $response = $sf->query($soql); // 1st page of results $queryResult = new QueryResult($response); $response = $sf->queryMore($queryResult->queryLocator); // 2nd page of results $queryResult = new QueryResult($response); $response = $sf->queryMore($queryResult->queryLocator); // 3rd page of results etc etc... $queryResult = new QueryResult($response);
Is there anyway to achieve pagination with results with a sensible limit? Of say 20 records?
Hi David,
There's no way to specifiy an offset in SOQL as you can in SQL. SOQL docs:
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select.htm#i1422156
QueryMore is for retrieving results with more than 2,000 records, which needs to be done in batches.
My solution to this would be to get your whole results set and cache it locally, that way you don't need to call Salesforce every time you need the data. If you cache it in a MySQL DB you can do whatever SQL queries you want. In my implementations, I set a maximum age for the data and check the last time the table was modified using this on the MySQL table:
SHOW TABLE STATUS from table where ...
and when I refresh it, I just truncate the table to clear the whole thing, run the query, and refill it. You can sync it other ways, like keeping your own timestamp values, comparing data in the local copy & the query result, etc. - but I'm pretty sure this is the simplest way to do it.
-paul