Apex Code Development You need to sign in to do that
Don't have an account?
Li Lee Question about SOQL WHERE LIMIT effect query result's order
Hi all,
We has an object with more 50000 records, in this object Name field is auto number. Display all data on the page no good experience for read, so we create a paging element split these records in serval pages, in our design we show 2000 records in on page.
When we test this page we found duplicated records display in diference page. After research we found in SOQL same record in LIMIT 2000 and LIMIT 4000 will have different index.
This is our test code:
LIMIT 2000
1248
LIMIT 4000
2249
Then we change LIMIT 2000 to LIMIT 6000, the Log result as follow:
LIMIT 6000
2249
LIMIT 4000
2249
Also we found if we remove WHERE seen this issue disappeared. This issue only observed with SOQL query with WHERE. And if we query all records at once, we can't reproduced this issue too.
We been search Salesforce SOQL Help document by serval days, we don't found any detail about how SOQL result order, and how LIMIT and WHERE effect query result's order. I want to ask you about does Salesforce has such document? Is Limit 4000 some kind of special value in Salesforce? Why WHERE effect query's order?
We has an object with more 50000 records, in this object Name field is auto number. Display all data on the page no good experience for read, so we create a paging element split these records in serval pages, in our design we show 2000 records in on page.
When we test this page we found duplicated records display in diference page. After research we found in SOQL same record in LIMIT 2000 and LIMIT 4000 will have different index.
This is our test code:
Integer index1 = 0;
for(Member__c member : [SELECT ID FORM Member__c WHERE Industry__c = 'Bank' OR Industry__c = 'Stock' OR Type__c = 'Futures' LIMIT 2000]) {
if(String.valueOf(member.Id) == 'a0D5e00000EXpOmEAL') {
break;
}
index1++;
}
System.debug('LIMIT 2000');
System.debug(index1);
Integer index2 = 0;
for(Member__c member : [SELECT ID FORM Member__c WHERE Industry__c = 'Bank' OR Industry__c = 'Stock' OR Type__c = 'Futures' LIMIT 4000]) {
if(String.valueOf(member.Id) == 'a0D5e00000EXpOmEAL') {
break;
}
index1++;
}
System.debug('LIMIT 4000');
System.debug(index2);
In Apex Log we get follow result:LIMIT 2000
1248
LIMIT 4000
2249
Then we change LIMIT 2000 to LIMIT 6000, the Log result as follow:
LIMIT 6000
2249
LIMIT 4000
2249
Also we found if we remove WHERE seen this issue disappeared. This issue only observed with SOQL query with WHERE. And if we query all records at once, we can't reproduced this issue too.
We been search Salesforce SOQL Help document by serval days, we don't found any detail about how SOQL result order, and how LIMIT and WHERE effect query result's order. I want to ask you about does Salesforce has such document? Is Limit 4000 some kind of special value in Salesforce? Why WHERE effect query's order?
Best option if you use OFFSET with LIMIT in SOQL.
if you need any assistanse, Please let me know!!
Kindly mark my solution as the best answer if it helps you.
Thanks
Mukesh