+ Start a Discussion
Chris MarzellaChris Marzella 

What is wrong with my SOQL query?

What is wrong with my SOQL query? This is the error I get: Didn't understand relationship 'Asset' in field path. 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 MatchProductTrigger on Opportunity (before update) {
    
    for(Opportunity opp : Trigger.new){
        List<Product2> currentProduct = [SELECT Id FROM Product2 WHERE Product2.Asset.Account.Id = :opp.Account.Id];
     }
}

 
Best Answer chosen by Chris Marzella
Neetu_BansalNeetu_Bansal
Hi Chris,

Asset is child of Product and for accessing child while querying parent, you have to query like this:
List<Product2> products = [ SELECT Id, ( Select Id, AccountId From Assets) FROM Product2 ];
For more reference, here is your trigger, as using SOQL in for loops may throw limit exceptions for bulk data:
trigger MatchProductTrigger on Opportunity( before update )
{
	Set<Id> accountIds = new Set<Id>();
	for( Opportunity opp : trigger.new )
	{
		if( opp.AccountId != null )
		{
			accountIds.add( opp.AccountId );
		}
	}
	
	List<Product2> products = [ SELECT Id, ( Select Id, AccountId From Assets where AccountId IN: accountIds ) FROM Product2 ];
	
    for( Opportunity opp : trigger.new )
	{
        for( Product2 prod : products )
		{
			for( Asset a : prod.Assets )
			{
				if( a.AccountId == opp.AccountId )
				{
					// Write your logic
				}
			}
		}
    }
}
Let me know, if you need any other help.

Thanks,
Neetu

All Answers

Balaji BondarBalaji Bondar
Chris,
 Product-Asset is 1:N relationship.You cannt quey like you mentioned.Also you cannt quey inside for loop , your code will break.
Use collection data type(List,Map,Set) to process the records.
List<Product2> productList = [Select p.Id, (Select AccountId From Assets) From Product2 p];
Important :
If this is what you were looking for then please mark it as a "SOLUTION" or You can Click on the "Like" Button if this was beneficial for you.
 
Neetu_BansalNeetu_Bansal
Hi Chris,

Asset is child of Product and for accessing child while querying parent, you have to query like this:
List<Product2> products = [ SELECT Id, ( Select Id, AccountId From Assets) FROM Product2 ];
For more reference, here is your trigger, as using SOQL in for loops may throw limit exceptions for bulk data:
trigger MatchProductTrigger on Opportunity( before update )
{
	Set<Id> accountIds = new Set<Id>();
	for( Opportunity opp : trigger.new )
	{
		if( opp.AccountId != null )
		{
			accountIds.add( opp.AccountId );
		}
	}
	
	List<Product2> products = [ SELECT Id, ( Select Id, AccountId From Assets where AccountId IN: accountIds ) FROM Product2 ];
	
    for( Opportunity opp : trigger.new )
	{
        for( Product2 prod : products )
		{
			for( Asset a : prod.Assets )
			{
				if( a.AccountId == opp.AccountId )
				{
					// Write your logic
				}
			}
		}
    }
}
Let me know, if you need any other help.

Thanks,
Neetu
This was selected as the best answer
Chris MarzellaChris Marzella
Thanks a lot Neetu. Now am I able to modify the product list to only show specific products based on what assets/products appear on the account?

ex: customer has an option to purchase a phone, phone case, tablet and tablet case. If the customer has previously purchased a phone I don't want the sales rep to have the option to add on a tablet case, only a phone case.

Does that make sense?
Neetu_BansalNeetu_Bansal
Hi Chris,

Yes, that's make sense. Now you need to put some cconditions according to your requirement and it will work. If still you faces some issue, you can contact me either on my Email id:  neetu.bansal.5@gmail.com or skype id: neetu.bansal.5

Let me know, if you need any other help.

Thanks,
Neetu