Test Class Failure due to SOQL Issue

Question

I am working on a test class for a visualforce page/controller that is failing with the error message "System.QueryException: List has no rows for assignment to SObject".

The Controller class code is below.

public class OfferConvertController {
    public Boolean blnDisplayError {get;set;}
    
    public OfferConvertController(ApexPages.StandardController Controller) {
        //Overloaded Constructor using a Controller as the argument
    }
    
    public PageReference  OfferToConvert() {
        PageReference returnPage = null;

        try 
        {
            ID offerId = ApexPages.CurrentPage().getparameters().get('id');
            
            //Get the data from the offer object
            Offer__c offerToConvert = [Select Contact__c, Contact__r.AccountId, Contact__r.OwnerId, Initial_Contribution__c, Name, Product_Name__c, Client_Name__c 
                                                     From Offer__c Where id=:OfferId];
            
            List<Opportunity> offerIdsInOpp = [Select Offer__c From Opportunity Where Offer__c=:offerId];
            
            if(offerIdsInOpp.isEmpty())    
            {
                if (String.isNotBlank(offerToConvert.Client_Name__c) && (offerToConvert.Client_Name__c.split('\\s+').size() > 1))
                {
                    //Store the data in the Opportunity object fields
                    Opportunity o = new Opportunity();
                    o.ContactId = offerToConvert.Contact__c;
                    o.AccountId = offerToConvert.Contact__r.AccountId;
                    o.Amount = offerToConvert.Initial_Contribution__c;
                    o.Name = offerToConvert.Name;
                    o.Opportunity_Product_Family__c = offerToConvert.Product_Name__c;
                    o.Policy_Owner_Full_Name__c = offerToConvert.Client_Name__c;
                    o.Offer__c = offerToConvert.Id;
                    o.RecordTypeId = [SELECT Id from RecordType WHERE SobjectType = 'Opportunity' and DeveloperName = 'Offer' LIMIT 1].Id;
                    o.Source__c = 'New Offer';
                    o.StageName = 'Offer Application';
                    o.CloseDate = Date.today().addDays(30); //need to change later
                    
                    //Insert new opportunity into the database
                    Database.insert(o);
                    
                    //Change returnPage to the new opportunity
                    returnPage = new ApexPages.StandardController(o).view();
                    returnPage.setRedirect(true);
                    
                }
                else
                {
                    blnDisplayError = true;
                    ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Error, 'Client Name Required');
                    ApexPages.addMessage(myMsg);
                }
            }
            else
            {
                blnDisplayError = true;
                ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Error, 'Already an Opportunity for this Offer');
                ApexPages.addMessage(myMsg);
            }
        }
        
        catch(Exception e) 
        {
            blnDisplayError= true;
            ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.Error,e.getMessage());
            ApexPages.addMessage(myMsg);
        }
        
        return returnPage;
    }
}

For the offer id passed from the visualforce page, the code is trying to get the list of opportunities associated with the offer. When there are no opportunities, it is trying to create a new opportunity. In our Salesforce org, we have a before-update flow that assigns the Pricebook2Id of the Opportunity based on the record type of the Opportunity. For the record type of 'Offer' for Opportunity, the pricebook2Id should be the id corresponding to a custom pricebook called 'Investment'. However, this flow is failing with an error message since it cannot access the pricebook. I tried to populate the Pricebook2Id manually in the code with the below statement, but the SOQL is returning 0 rows and hence failing.

o.Pricebook2Id = [SELECT Id from Pricebook2 WHERE Name = 'Investment' and IsActive = true LIMIT 1].Id;

I am not sure why the above statement is returning 0 rows in the method OfferToConvert().

Any thoughts on what am I missing?

Answer 1

Pricebooks in unit tests are in a weird kind of superposition where:

• They do exist, so you can't insert a Pricebook2 with the same name in a test class
• You cannot query them, even if you have the Id, until you perform some DML on them
• They aren't a "Setup Object"
• You cannot get the Id programmatically (except for the standard pricebook Id, via Test.getStandardPricebookId())
• If you have the Id, you can use it to set the Pricebook2Id field on Opportunity

Thankfully, there is a decent way for us to wriggle out of the situation Salesforce puts us into. It leans heavily on the fact that the Name field of Pricebook2 has the "idLookup" property (documentation for reference)

In a nutshell, that allows us to use the Name field as if it were a text field marked as an "external id". We can upsert based on that field.

// In a test class (test setup or a test method)
// Should return 0 rows
List<Pricebook2> pbBeforeList = [SELECT Id, Name FROM Pricebook2];

// The "Name" after the SObject (or list of SObjects) is the field we want Salesforce
//   to look at for it to make the decision about whether it needs to insert or update
upsert new Pricebook2(Name = 'Standard Price Book') Name;

// also possible to upsert a list
/*upsert new List<Pricebook2>{
    new Pricebook2(Name = 'Standard Price Book'),
    new Pricebook2(Name = 'A custom pricebook')
} Name;
*/

// Standard Pricebook should have been "updated", and that's enough for us to be able
//   to get results back from a query (in a test context)
List<Pricebook2> pbAfterList = [SELECT Id, Name FROM Pricebook2];

After upserting, the pricebooks become queryable, and that pretty much resolves all of the weirdness surrounding pricebooks in tests. You can query pricebooks and make something like a Map<String, Id> for other things in your test to use, but at this point it's probably less typing to just keep on using the fact that Name can be used as an identifier.

So instead of

PricebookEntry standardPBE = new PricebookEntry(
    Product2Id = testProduct1.Id,
    Pricebook2Id = pricebookIdByName.get('Standard Price Book'),
    UnitPrice = 1.00,
    IsActive = true
);

we can drop the Id from Pricebook2Id and have Salesforce do the work to relate it to the appropriate pricebook

PricebookEntry standardPBE = new PricebookEntry(
    Product2Id = testProduct1.Id,
    // This is the line that changed
    // Pricebook2Id -> Pricebook2
    // and we set it equal to the appropriate SObject with only one field specified
    Pricebook2 = new Pricebook2(Name = 'Standard Price Book'),
    UnitPrice = 1.00,
    IsActive = true
);