Check if a .txt file of IDs all exist in Salesforce

Question

I receive a .txt files of type ID, e.g. 003U000000PL9LN, 001U000000PL9LM, a1fU000000PL9LO. I need to check if these IDs correspond to valid objects within Salesforce. I have a List of IDs in Apex.

I don't know beforehand what objects these IDs refer to, though I can always do a Describe to find out. However, if there are over 100 IDs, and they all refer to different objects, then querying a ton of IDs in SOQL goes beyond the limit.

I thought of doing SOSL, something like "FIND ALL RETURNING (dynamic list of objects) WHERE ID in (dynamic list of IDs). However, I believe FIND ALL is not available in SOSL.

I don't need to record any information about failing records, just return true if all the IDs correspond to an object, and false if even one record does not correspond. How can I achieve this?

Answer 1

What if you grouped the IDs by object and put them in a set of IDs. Then query each object WHERE id IN:customObjIDSet. You could do an aggregate query withCOUNT(id) then comparing the result to the # in the set, it should be the same if they all exist. I can't think of a way to get it to be any less than 1 query per object.

You might also want to to consider moving this operation to be asynchronous or even batch depending on your use case. Each batch can run in its own execution context so the limits shouldn't be an issue.

Answer 2

SAMPLE CODE: SYNCHRONOUS

public class verify {
    public static Set<Id> idValues(Id[] values) {
        Map<SObjectType, Set<Id>> valMap = new Map<SObjectType, Set<Id>>();
        Set<Id> missingIds = new Set<Id>(values);
        for(Id value:missingIds)
            valMap.put(value.getSObjectType(), new Set<Id>());
        for(Id value:missingIds)
            valMap.get(value.getSObjectType()).add(value);
        for(SObjectType sType:valMap.keySet()) {
            missingIds.removeAll(new Map<Id, SObject>(
                Database.query('SELECT Id FROM '+String.valueOf(sType)+
                    ' WHERE Id IN (\''+String.join(new List<Id>(valMap.get(sType)),'\',\'')+
                    '\')')
            ).keySet());
        }
        return missingIds;
    }

    public static Set<Id> idValuesFromFile(String content) {
        String[] parts = content.split('\n');
        Id[] values = new Id[0];
        while(!parts.isempty()) {
            try {
                values.add((Id)parts.remove(0).trim().substring(0,15));
            } catch(exception e) {
                System.debug(LoggingLevel.ERROR, e);
            }
        }
        return idValues(values);
    }

    /* Example usage
        Attachment a = [SELECT Id,Body FROM Attachment WHERE Id = :some_id];
        Set<Id> missingIds = verify.idValuesFromFile(a.body.toString());
    */
}

Limits for this code: ~1100 unique ID values per entity (SOQL string length limit), 50,000 unique ID values total (SOQL row limit), and 100 unique entities (SOQL query limit).

SAMPLE CODE: ASYNCHRONOUS

global class verifyBatchIds implements Database.batchable<id>, iterator<id>, iterable<id>, database.stateful {
    id[] ids;
    set<id> missingids;
    global verifyBatchIds(Id[] source) {
        ids = source.clone();
        ids.sort();
        missingids = new set<id>();
    }
    global iterable<id> start(database.batchablecontext bc) {
        return this;
    }
    global boolean hasnext() {
        return !ids.isempty();
    }
    global id next() {
        return ids.remove(0);
    }
    global iterator<id> iterator() {
        return this;
    }
    global void execute(database.batchablecontext bc, id[] scope) {
        map<sobjecttype, id[]> ids = new map<sobjecttype, id[]>();
        for(id value:scope)
            ids.put(value.getsobjecttype(), new id[0]);
        for(id value:scope)
            ids.get(value.getsobjecttype()).add(value);
        missingids.addall(scope);
        for(sobjecttype stype:ids.keyset())
            missingids.removeall(new map<id,sobject>(
                database.query('select id from '+string.valueof(stype)+
                               'where id in (\''+string.join(ids.get(stype),'\',\'')+
                               '\')')
            ).keyset());
    }
    global void finish(database.batchablecontext bc) {
        // Output results here, to a file, email, or whatever.
    }
}

Limits for this code: 100 entities per batch of 200, which should be nearly impossible in normal usage, as they are sorted by type, and probably just short of 500,000 ID values, assuming no heap errors before you reach that point. In practice, I'd expect a practical limit of about 249,500 or so entries, because you still need to parse the file and convert to ID values (as in the first sample).