+ Start a Discussion
HARSHIL U PARIKHHARSHIL U PARIKH 

How to query Products with OpportunityLineItems? Or Can we query opportunityLineItems in Product2 Query?

Hello Developers!

I am trying to count number of product sold by won opportunities but I am getting error in line 21 query as:


Id, Opportunity_Stage__c FROM OpportunityLineItems WHERE Opportunity_Stage__c
                              ^
ERROR at Row:2:Column:69
Didn't understand relationship 'OpportunityLineItems' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Trigger Code:
Trigger CountingNUmberOfProductSold on OpportunityLineItem (After Insert, After Update, After Delete, After UnDelete) {
	List<Id> ProductIds = New List<Id>();
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
        For(OpportunityLineItem OppLineItem : Trigger.New){
            If(OppLineItem.Product2Id != null && OppLineItem.Opportunity_Stage__c == 'Closed Won'){
                 ProductIds.add(OppLineItem.Product2Id);
            }
        }      
    }
    If(Trigger.IsDelete){
        For(OpportunityLineItem OppLineItem : Trigger.Old){
            If(OppLineItem.Product2Id != null && OppLineItem.Opportunity_Stage__c == 'Closed Won'){
                ProductIds.add(OpplineItem.Product2Id);
            }
        }
    }
    
    List<Product2> finalProduct2ListToUpdate = New List<Product2>();
    
    For(Product2 Product : [Select Id, Number_Of_Quantity_Sold__c,
                            		(Select Id, Opportunity_Stage__c FROM OpportunityLineItems WHERE Opportunity_Stage__c = 'Closed Won')
                            			FROM Product2 WHERE Id =:ProductIds])
    {
    	Product.Number_Of_Quantity_Sold__c = Product.OpportunityLineItems.size();
        finalProduct2ListToUpdate.add(Product);
    }
    
    try{
        If(!finalProduct2ListToUpdate.IsEmpty()){
            update finalProduct2ListToUpdate;
        }
    }
    catch(Exception e){
        system.debug('Thown Exception Name For CountingNUmberOfProductSold Trigger Is - ' + e.getTypeName() + ' ' + 'And Message Is - ' + e.getMessage());
    }   
}

 
HARSHIL U PARIKHHARSHIL U PARIKH
Or simply in other words,

when I write below query it saves and doesn't throw an error:
List<Opportunity> OppsWithLineItems = [Select Id, (Select Id FROM OpportunityLineItems) FROM Opportunity];
But however, the query below does throws an error:
List<Product2> ProductsWithLineItems = [Select Id, (Select Id FROM OpportunityLineItems) FROM Product2];
Error:
Didn't understand relationship 'OpportunityLineItems' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name

Thank You!