Help with VF page and Querying OpportunityLineItems

Question

I am doing some work for a client and they have asked if I could make a "child/parent" relationship between OpportunityLineItem (OLI) records.

I have been told by someone they have seen it done before and it has been done by adding a lookup to the Product2 to another Product2 record.

The client then wants the OLI parents to be displayed on a page (I will be using apex:repeat for this due to the table style), they they want a nested table within this table where they display the child OLI's. I have been able to work the VF side so far and it all looks good.

However I have been racking your brains trying to work out how to do this... but I just can not get it to work D: does anyone have any ideas or pointers?

I just need the most basic set of fields from the OLI's to display, it's literally just the Name, Quantity, Discount, ListPrice, UnitPrice and TotalPrice from both the parent and child.

****UPDATE**** I am displaying the Opp at the top of a VF page, then under that I need to display a table of all of the Parent/"Master" OLI's and in each OLD row, I need to display the Child/"Extra" OLI's so it will be kind of like this:

-------- Opp Info --------
| Name        Stage      |
--------------------------
|  OLI 1 Master Details  |
|                        |
|  --------------------- |
|  |OLI 1 Child Details| |
|  --------------------- |
--------------------------
|  OLI 2 Master Details  |
|                        |
|  --------------------- |
|  |OLI 2 Child Details| |
|  --------------------- |
--------------------------

Answer 1

I worked this out in quite a long winded way, but it seems to be displaying okay so far...

Method:

Map<OpportunityLineItem, List<OpportunityLineItem>> masterToExtraMap = new Map<OpportunityLineItem, List<OpportunityLineItem>>();
List<OpportunityLineItem> liList = new List<OpportunityLineItem>();
liList = [SELECT Id, Name, Quantity, ListPrice, Description, ProductCode, TotalPrice
          FROM OpportunityLineItem
          WHERE OpportunityId =: opp.Id
          AND Product2.RecordType.Name =: 'Master'];

for(OpportunityLineItem oli : liList){
    List<OpportunityLineItem> extraLi = new List<OpportunityLineItem>([SELECT Id, Name, Quantity, ListPrice, Description, ProductCode, TotalPrice
                                                                       FROM OpportunityLineItem
                                                                       WHERE Product2.Master_Product__c =: oli.Product2Id
                                                                       AND Product2.RecordType.Name =: 'Extra']);
    masterToExtraMap.put(oli, extraLi);
}

oppLineItems = masterToExtraMap;

Then I am using apex:repeat nested in an apex:outputPanel which is surrounded in an apex:repeat again....

As I said it's long winded but seems to work okay :)

Answer 2

Unless you have a field that shows the child relationship between the Parent OLI, I'm not entirely certain how you can accomplish what you're asking other than perhaps by sorting the returned records by CreatedDate and Product2Id. Assuming you have the Actual OppId, I'd expect the query to look something like this:

list<OpportunityLineItem>OLI = [SELECT ID, Name, Quantity, ListPrice, UnitPrice, 
  TotalPrice, Product2ID, CreatedDate OpportunityId FROM OpportunityLineItem 
  WHERE OpportunityId =: ActualOpp.Id ORDER BY Product2ID ];

You should be able to do an ORDER BY on this without needing to make it an AggregateResult. Optionally, you could also add the CreatedDate after the Product2Id as a secondary sort if you don't have a field that allows you to generate the parent-child relationship you're looking for. I've assumed they'd have the same Product2Id, but I could be wrong on that as only you would know. Whatever the criteria is, use it in your Order By clause, then put the results in a wrapper class.