Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
fourfourfunfourfourfun 

The left operand 'account.ownerid' cannot have more than one level of relationships

Trying to alter some SOQL in my environment to filter on Account Owner rather than Contact Owner. Now I'm used to SQL and find SOQL maddening as I am not fully to grips with the syntax to do things I would find matter of fact in SQL. Here is what I have:

allcontacts=[select id, firstname, lastname, speciality__c, account.name, pcm_contact_type__c, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in (select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId())
                           and firstname like :'%' + search.firstname + '%'
                           and lastname like :'%' + search.lastname + '%'
                           and (pcm_contact_type__c like :search.pcm_contact_type__c+ '%' )
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           and account.ownerid not in (select account.ownerid from account where account.ownerid=:search.profession__c)
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname, firstname
                           limit 50];
It is the bolded line that is the issue. 

If anyone can point out the logic to me so I can learn from this, that would be great!

Best Answer chosen by fourfourfun
Vinit_KumarVinit_Kumar
Ohk my bad !! 

Try something like below :-

// Query the related Account OwnerIds and store it in a list
List<Account> ownerAccs = [select ownerid from account where ownerid=:search.profession__c];

List<Id> ownerIds = new List<Id>();
// Populating it to a list
for(Account acc : ownerAccs)
{
	ownerIds.add(acc.OwnerId);
}

//Now,use that list in your where filter
  allcontacts=[select id, firstname, lastname, speciality__c, account.name, pcm_contact_type__c, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in (select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId())
                           and firstname like :'%' + search.firstname + '%'
                           and lastname like :'%' + search.lastname + '%'
                           and (pcm_contact_type__c like :search.pcm_contact_type__c+ '%' )
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           <strong>and account.ownerid not in :OwnerIds </strong>
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname, firstname
                           limit 50];

If this helps,please mark it as best answer to help others :)

All Answers

Vinit_KumarVinit_Kumar
Try something like below :-

// Query the related Account OwnerIds and store it in a list
List<Id> ownerIds = [select ownerid from account where ownerid=:search.profession__c];

//Now,use that list in your where filter
  allcontacts=[select id, firstname, lastname, speciality__c, account.name, pcm_contact_type__c, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in (select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId())
                           and firstname like :'%' + search.firstname + '%'
                           and lastname like :'%' + search.lastname + '%'
                           and (pcm_contact_type__c like :search.pcm_contact_type__c+ '%' )
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           <strong>and account.ownerid not in :OwnerIds </strong>
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname, firstname
                           limit 50];

The reason why I did this is because SOQL doesn't allow you to make more than one level deep of relationships in where filters.

If this helps,please mark it as best answer to help others :)
fourfourfunfourfourfun
I will test it and return promptly! You've helped me quite a bit recently!
fourfourfunfourfourfun
Ok, I then get this as an error. 

Save error: Illegal assignment from LIST<Account> to LIST<Id>
Vinit_KumarVinit_Kumar
Ohk my bad !! 

Try something like below :-

// Query the related Account OwnerIds and store it in a list
List<Account> ownerAccs = [select ownerid from account where ownerid=:search.profession__c];

List<Id> ownerIds = new List<Id>();
// Populating it to a list
for(Account acc : ownerAccs)
{
	ownerIds.add(acc.OwnerId);
}

//Now,use that list in your where filter
  allcontacts=[select id, firstname, lastname, speciality__c, account.name, pcm_contact_type__c, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in (select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId())
                           and firstname like :'%' + search.firstname + '%'
                           and lastname like :'%' + search.lastname + '%'
                           and (pcm_contact_type__c like :search.pcm_contact_type__c+ '%' )
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           <strong>and account.ownerid not in :OwnerIds </strong>
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname, firstname
                           limit 50];

If this helps,please mark it as best answer to help others :)
This was selected as the best answer
fourfourfunfourfourfun
Yeah, not quite working for me. It saves but the search just isn't doing what I want. I've streamlined a touch:

public PageReference searchCon()
    {
    	
		// Query the related Account OwnerIds and store it in a list
		List<Account> ownerAccs = [select ownerid from account where ownerid=:search.profession__c];
		
		List<Id> ownerIds = new List<Id>();
		// Populating it to a list
		for(Account acc : ownerAccs)
		{
			ownerIds.add(acc.OwnerId);
		}

		for(Account acc : ownerAccs)
		{
			ownerIds.add(acc.Id);
		}
 		   allcontacts=[select id, firstname, lastname, Contact.PCM_Contact_Type__c , account.name, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in (select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId())
                           and lastname like :'%' + search.lastname + '%'
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           and account.ownerid not in :OwnerIds
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname, firstname
                           limit 50];
        return null;
    }

It sees that the name on my VF page is having no impact on the search whatsoever, it returns records for all users regardless.
Vinit_KumarVinit_Kumar
The way I would approach is put some debug statements and see what's there in the list by enabling the debug logs for the user.

I am sure something not going googd with the serach keyword.

Hope this helps !!
fourfourfunfourfourfun
I have this buttoned down. I'm doing some further changes but that can be for another thread. Thanks for the help!