SOQL Query or a way to query records from a file in Salesforce

Question

I have a field (wallet number) on contact object for which i want to get results using a SOQL query.
The list contains more than 20k records for which i want to get related field values from contact object.
example

Select id, name, email 
from contact 
where wallet_number__c in ('a123','a114','b143','b121','b1124'...and so on till 20k values)

I ran the query but it will give error if i put more than 200/300 values.

Is this something i can achieve using SOQL. Can i query the column from the file directly to get results ? Or some other way to achieve this ?

Answer 1

Pankaj, the right way to query a list is the following

List<String> walletNumbers = new List<String>();
//populate list 
List<Contact> myRecs = [select Id from Contact where Wallet_Number__c in :walletNumbers];

Keep in mind that SoQL will not let you return more than 10000 records on a query, so you may have to either mark your VF page as read-only or use batch apex (or something else) for this solution if you truly need 20000 records at once.

Another thing to consider is that if Wallet_Number__c is not an indexed field then you may experience severe performance issues with the query, perhaps leading to an Apex CPU Time governor limit exception.

Answer 2

If you're comfortable with Python (or some other scripting language with a Salesforce binding library), it's pretty easy to iterate over a collection of identifiers and build queries that fall just under the relevant limits to extract this data directly (without writing any Apex or modifying your org metadata).

Here's a lightly-modified example from a past project I worked on (note that this uses the simple_salesforce library):

# Perform a series of queries against `field_name` with the supplied `value_set` as possible values.
def perform_field_queries(self, sobject_name, field_name, value_set):
    query = 'SELECT {} FROM {} WHERE {} IN ({})'

    ids = value_set.copy()

    id_list = ''

    while len(ids) > 0:
        id_list = '\'' + ids.pop() + '\''

        # The maximum length of the WHERE clause is 4,000 characters
        while len(id_list) < 3980 and len(ids) > 0:
            id_list += ', \'' + ids.pop() + '\''

        # `self.context.connection` here is the `simple_salesforce.Salesforce` object representing the connection.
        # The first format parameter is the fields to return.
        results = self.context.connection.query_all(
            query.format('Id', sobject_name, field_name, id_list)
        )

        # Error handling supplied by the reader

        for rec in results.get('records'):
            # Do something with this object, like write it to a `csv.DictWriter` instance.