Compile Error: Didn't understand relationship 'opportunity' in field path

Question

I'm attempting to create a report that maps activity on an account, by signalling whether certain 'capabilities' (services sold) stored as picklist values are selected on all related opportunity records.

To give a 'simple-terms' example, I have a picklist field with the options 'red', 'green' and 'blue' on my opportunity page. If I have 2 opportunities for my account "customer 1", where on Opportunity A I have selected 'red', and opportunity B I have selected 'red' and 'green', I would map that my activity with customer 1 is 2 red, 1 green and 0 blue.

I've tried to explain my (noob) logic below:

public List<Double> getWhichCapabilities(){
        List<SelectOption> Myoptions = new List<SelectOption>();
        List<Double> CapabilityMap = new List<Double>();

        // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
        Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

        List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

        for( Schema.PicklistEntry f : ple){
            Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
        }           

        //I should loop through the possible picklist values, each time checking to see if they have been selected.
        for(Integer a = 0; a < Myoptions.size(); a++){

        //I create a list of all opportunity records related to the account. I filter by each picklist value (currently active in the loop).    
            List<account> OppsWithCapabilities = [
            SELECT id, name, (
                SELECT id, capability__c from Opportunities
                )
            FROM account 
            WHERE ( account.id = :Current_Acc_Id ) AND ( account.opportunity.capability__c includes (:Myoptions.get(a)) )
            ];

            //I then add an item to my final list ('Capability Map') which is the size of the OppsWithCapabilities (basically a count of the records that match the filter)
            CapabilityMap.add(OppsWithCapabilities.count);

            //finally, clear the list ready for the next iteration
            OppsWithCapabilities.clear();
        }
        //output a list of integers
        return CapabilityMap;

    }

Phew!

So I get thrown the error (in the title) which signals to me that my sub-query on the opportunity records for my picklist field is wrong. For the life of me I can't work out why.

I expect to be returned a list of Integers which I can then manipulate on my vf page. I'm confident with my VF code that I can do this, so that isn't an issue.

For now, if you could assist with my compile error, that would be great. Also, for education purposes, if you know a more efficient way of performing this, I would appreciate the heads up (I'm learning as I go).

Thanks for your help and patience with my long post.

Answer 1

This code is not valid:

AND ( account.opportunity.capability__c includes

The subquery returns a List of Opportunities related to the account. You will have to Iterate over the list of opportunities for each account to see if they match your criteria

for(Account a : OppsWithCapabilities){
   for(Opportunity o : a.Opportunities){
      //Check values here and do what you need to
   }
}

Answer 2

Solved.

Working code with VF below for anyone who might find it useful. This is run on a VF page that uses the standard account controller. The output is a table that shows me which services I perform per account. I use an apex:repeat to build the table, and where the count is >0 I use a class to turn the cell green.

VF:

<table class="table3">

  <th class="rotate" style="width:112px;"><div style="transform:translate(45px, 82px) rotate(270deg)"><span>Services</span></div></th>

  <apex:repeat value="{!ListOfCapabilityValues}" var="headers">
    <th class="rotate"><div><span>{!headers}</span></div></th>
  </apex:repeat>


  <tr>
    <td>Performed?</td>

    <apex:repeat value="{!WhichCapabilities}" var="CapabilityCount">

        <apex:repeat value="{!CapabilityCount}" var="countTrue" rendered="{!CapabilityCount>0}">
            <td class="green">{!CapabilityCount}</td>
        </apex:repeat>

        <apex:repeat value="{!CapabilityCount}" var="countFalse" rendered="{!CapabilityCount=0}">
            <td class="capabilitycount">{!CapabilityCount}</td>
        </apex:repeat>

    </apex:repeat>

</tr>
</table>

Extension:

    public List<string> getListOfCapabilityValues(){
    List<SelectOption> Myoptions = new List<SelectOption>();
    List<string> CapabilityValues = new List<string>();

    // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
    Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

    List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

    for( Schema.PicklistEntry f : ple){
        Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
    }
    for(Integer i = 0; i < Myoptions.size(); i++){
        CapabilityValues.add(Myoptions[i].getLabel());
    }
    return CapabilityValues;  
 }  


 public List<integer> getWhichCapabilities(){
    List<SelectOption> Myoptions = new List<SelectOption>();
    List<integer> CapabilityMap = new List<integer>();

    // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
    Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

    List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

    for( Schema.PicklistEntry f : ple){
        Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
    }           

    //I create a list of all opportunity records related to the account. 
        List<account> OppsWithCapabilities = [
        SELECT id, name, (
            SELECT id, capability__c from Opportunities WHERE capability__c != Null AND StageName='Closed Won'
            )
        FROM account
        ];

    //define a variable 'j' for counting
    integer j=0;

    //I should loop through the possible picklist values, each time checking to see if they have been selected on each opportunity.
    for(Integer i = 0; i < Myoptions.size(); i++){               

        //for the accounts identified... 
        for(Account a : OppsWithCapabilities){            

                //scrutinise the opportunities that have been found
                for(Opportunity o : a.Opportunities){
                    String str = o.capability__c;


                //if the current picklist value is selected, increment the counter 'j'+1
                if (str.contains( Myoptions[i].getLabel() ) ) { j++;}

            }

        }
        //I then add an item to my final list ('Capability Map') which is the value of my counting variable, 'j' (basically a count of the records that match the filter)
        CapabilityMap.add(j);

        //finally, clear the count variable ready for the next loop
        j=0;

    }
    //output a list of integers
    return CapabilityMap;
}