[SalesForce] Retrieving more than 100.000 records using select statement

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..

Best Answer

Use a semi-join, e.g.

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

Related Solutions

[SalesForce] How to run SOQL for an Object having more than 100K records

Per the indexing cheat sheet PJC posted, adding and Contact__c != null to your query automatically makes it non-selective. You should remove this.

Since foreign-key fields, like a lookup to contact, are automatically indexed as long as you don't have massive numbers of time records for each contact your query should then be selective.

You're already only adding contact IDs to the conIDs if the record's contact__c field is not null, so the exta check in your query shouldn't be needed.

While it seems like a good idea to explicitly filter out nulls to code defensively this practice actually causes your query to perform considerably worse (causing a full table scan under the hood), and if in a trigger, hit this error.

[SalesForce] SOQL Query help with more than one level of relationships

You say that your RS object is in a Master-Detail relationship with Case. Assuming that RS is the detail side of the relationship, you simply need to loop over your RS records and gather the related Case Ids.

With that, you should be able to run your query with one small change.

// Declare either a set or a list to hold the Ids.
// It doesn't really matter which one, my preference is for a Set though.
Set<Id> caseIdsSet = new Set<Id>();
for(RS__c rsRec :myRsRecordList){
    // I'm assuming the relationship field is Case__c
    // Even though the relationship field will show you the name of the related
    //   record in Visualforce (guessing the same is true for Lightning), the field, in
    //   reality, holds the Id of the related record.
    caseIdsSet.add(rsRec.Case__c);
}

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN (SELECT AccountId
                                                  FROM Case
                                                  // This is the small change to your query
                                                  WHERE Id IN :caseIdsSet
                                                 )];

If this code is part of a trigger on your RS object, then this is the only query that you'll need. If you're not doing this as part of a trigger (or otherwise don't have access to the Trigger context variables), then you will need another query to grab the required field(s) from your object.

+edit:

So you're outside of a trigger context. In that case, there's only one way I can think of the avoid the second query; use a formula field.

Update to edit:

formula fields can't be used in semijoins. There is still merit in the example below, but to use it in your situation, you'd still likely need to query RS__c records to obtain the value of the formula field (and then you'd be using code based on my first example).

If you have a formula field that pulls down the AccountId from the Case your RS record is related to, you could use that in your semi-join like so...

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN
                                  (SELECT Account_Id_Formula__c 
                                   FROM RS__c
                                   WHERE Id IN :myRSRecordsList
                                  )
                          ];

While the most common use of a semijoin is where you'd query the object whose Ids you're trying to obtain (i.e. Account in this case), it'll work on any object provided that you have a field that holds the appropriate Id on that object (I don't believe you're allowed any hierarchy traversal, up or down, in a semijoin).