Retrieving more than 100.000 records using select statement

Question

I have a custom object called equipment___c with more than 100.000 records, and also related to product object. To get product codes that are in equipment object, i used the following query:

select product.productCode from Equipment__c

Here i'm getting 100000 records as a result.(checked this from dataloader). one productcode can be assigned to multiple equipments... So when i ran this query there will be repeated product codes. But i want distinct product codes.. so that i can display those codes in VF page.

when i'm using the above SOQL query in controller, i'm the getting the following error:

Too many rows 50001.

I also checked the following query:

select count_distinct(product.productcode) from equipment__c

I got the count as 689. so i want to print these 689 product codes in vf page.

I also tried GROUP BY clause. but it throws an error

Aggregate query does not support querymore(), use LIMIT to restrict the results to a single batch.

please help, how can i get distinct product codes..

Answer 1

Use a semi-join, e.g.

select productCode from productCodes where id in (select product from equipment__c)

Answer 2

I think there is only one solution.

Use readonly attribute in your VF page (page1)and fetch all productCode:

select product.productCode from Equipment__c

Then loop through all records and create list of unique productCode.

Then pass these productCode to next page(Page2) if user want to perform and DML operation.

On this (page2) you can skip readonly tag and perform DML operation and you already have list of unique productCode from page1.

If user do not want to perform any DML operation then he can view all productCode on page1 itself.

Note that if productCode size is very large like 100k or so then you may face governor limit related to heap size.

Otherwise it should just work fine for 689 records.

Answer 3

When you need to show more than 1000 record on VF page or want to process on more then 50000 records, you must use AJAX toolkit of salesforce.

include connection.js into your page like below

<script src="/soap/ajax/32.0/connection.js" type="text/javascript"></script>

Now use following code snippet to understand and apply as your requirement

var result = sforce.connection.query("select name, id from account");
var queryMore = true;
while (queryMore) {
var records = result.getArray("records");
for (var i = 0; i < records.length; i++) {
//process records[i]
}
if (result.getBoolean("done")) {
queryMore = false;
} else {
result = sforce.connection.queryMore(result.queryLocator);
}
}

Please use this and let me know if you need any help.

Please mark this as an answer if this solve your problem :)