0

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 ?

2 Answers 2

2

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.

5
  • I need an extract of this query and need not update it somewhere else in SF, We just need to get the details in a csv format. Is it possible using the above query. Want to use data loader or workbench for this purpose. Pls advise May 15, 2018 at 19:00
  • 2
    This query works in apex. You may not write a query that long in any of the tools you describe. There are other solutions, like creating a new temporary object that links to the relevant contacts, and query 100% of those rows (and the related contacts) via Data Loader, for example. May 15, 2018 at 19:02
  • Pankaj, you can use https://workbench.developerforce.com to query and export the result in CSV format. May 15, 2018 at 19:09
  • @HemendraSinghRajawat He is already aware of workbench. May 15, 2018 at 19:10
  • 1
    @Sebastian : Thanks. Will check the object option. May 15, 2018 at 19:10
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .