Creating list from custom objects with relationship [closed]

Question

I want to create a list in order to use it in a vf page with the tag apex:pageblocktable. The schema below is just for learning purposes, it has 3 custom objects with look up relationships.

I had create the following queries but I think is far way from been efficient, specially the section were I am bringing the brand name as I loop through all the rows until i find an Id match.

    string brandName;
string carType;    
list<list<string>> Result = new list<list<string>>();
public list<list<string>> getResult(){
    list<Model__c> cars1 = [SELECT Name, Brand__c, (Select Name FROM cars1__r) FROM Model__c];
    list<Brand__c> brand = [SELECT Name,Country__c FROM Brand__c];
    for (Model__c c:cars1 ){
        for(Car__c details:c.cars1__r){
            carType = details.name;
            //GET THE BRAND OF THE CAR
            for(Brand__c b:brand){
                if (c.Brand__c == b.Id){
                    brandName = b.name;
                }
            }
            //system.debug(brandName + ' ' + c.Name + ' ' + carType);
            list<string> subresult = new List<string>();
            subresult.add(brandName);
            subresult.add(c.Name);
            subresult.add(carType);
            result.add(subresult);
        }
    }     
    return Result;
}

Also the problem is when I had my list, that is a group of lists inside the list I havent been able to find in the docs how to name each list, in order to use the apex tags to display in the vf page.

Answer 1

Your solution is almost ok, but it's better to have a inner class to maintain lists like this. Then we can maintain any kind of data structure that we need to have in client side(VF page) instead of having list of list, take the benefit of list collection with your own structure(below is a how to, but not tested).

public with sharing class myController{
public List<UIWrapper> uiWrapperList{get;set;}

    public List<UIWrapper> getuiWrapperList(){
    uiWrapperList = new List<UIWrapper>();
    //your data retrieving goes here
    list<Model__c> cars1 = [SELECT Name, Brand__c, (Select Name FROM cars1__r) FROM Model__c];
        list<Brand__c> brand = [SELECT Name,Country__c FROM Brand__c];
        for (Model__c c:cars1 ){
            for(Car__c details:c.cars1__r){
                carType = details.name;
                //GET THE BRAND OF THE CAR
                for(Brand__c b:brand){
                    if (c.Brand__c == b.Id){
                        brandName = b.name;
                    }
                }

                UIWrapper wrapper = new UIWrapper();
                wrapper.setBrandName(brandName);
                wrapper.setCarName(c.Name);
                wrapper.setCarType(carType);
                uiWrapperList.add(wrapper);
            }
        }    
    }

    public class UIWrapper{
        public string brandName{get;set;}
        public string carName{get;set;}
        public string carType{get;set;}
    }
}

Then in your VF page, simply use the wrapper list as follows

<apex:pageBlockTable value="{!uiWrapperList}" var="item">
    <apex:column value="{!item.brandName}"/>
    <apex:column value="{!item.modelName}"/>
    <apex:column value="{!item.carName}"/>
</apex:pageBlockTable>

Answer 2

For me there is no point of such query....

If I am understand you correctly you just want to display list of cars records ordered first by brand and then by model?

In such case I strongly advice using wrapper class and comparable interface.

First you should create query and list of wrapper objects:

List<SortIt> myList = new List<SortIt>();
for(Car__c car : [SELECT Id, Model__r.Brand__r.number, name /** other fields you need **/
                  FROM Car__c
                  WHERE /** your condition **/]){
    myList.add(new SortIt(car));    
}



class SortIt implements Comparable(){
    /** Here are atributes you wanted to be in this class **/
    example:
    public integer value{get; private set;}
    public Car__c cartype{get; private set;}

    public SortIt(Car__c car){
        /** here assign atributes **/
        value = car.Model__r.Brand__r.number;
        cartype = car;
    }

    public integer compareTo(object compareTo){
       SortIt compareTo = (SortIt)compareTo;
       if(value = 1) return 0;
       if(value < 1) return -1;
       return 1;
    }
}

This is just simple example how to implement it, not working code becaue I simply don't have enought information...

Answer 3

Instead of List of List it is better to use Wrapper Class (Inner class). Wrapper classes are easy to understand and implement.

Ex:

            // Store Model Name and Cars associated with it
            List<Brand> carsWithTheirModels = new List<Brand>();
            class Brand
            {
              String brandName {get; set;}
              List<Model__c> model {get; set;}

              public brandName(String str, List<Model__c> model)
              { 
                  this.brandName = str;
                  this.model = model;
              }
            }

After that you can iterate this list on <apex:page> using <apex:repeat>

Also you can create a Map like:

// Key: Brand Name , Value: Brand Detail
Map<String, Brand> modelsMap = new Map<String, Brand> brandsMap();

Answer 4

Here is an (untested) implementation that leverages SOQL's ability to query graphs of objects via Relationship Queries taking them a little further than in the code posted in the question:

public with sharing class MyController {
    public Car__c[] cars {
        get {
            if (cars == null) {
                cars = new Car__c[] {};
                for (Model__c m : readModels()) {
                    for (Car__c c : m.Cars__r) {
                        cars.add(c);
                    }
                }
            }
            return cars;
        }
        private set;
    }
    private Model__c[] readModels() {
        String brandName = 'Ferrari';
        return [
                select
                        Brand__r.BrandName__c,
                        Brand__r.Country__c,
                        ModelName__c,
                        (
                                select CarName__c, Colour__c
                                from Cars__r
                                order by CarName__c
                        )
                from Model__c
                where Brand__r.BrandName__c = :brandName
                order by ModelName__c
                ];
    }
}

SOQL can return a graph of objects, with multiple levels of parent object (just one level Brand in this case) and one level of child object (Car in this case). Using this facility saves you having to write code to connect objects of one level with objects of another level - Apex code or Visualforce tags can use the references between the levels of object. Generally using fewer SOQL calls improves performance too. The cleanest place to order data is also in the SOQL.

Relationship queries clearly have their limitations, but where they fit the problem they are a big help.

Visualforce is optimised to display SObject fields directly rather than primitive data types. Tags like apex:column and apex:outputField correctly render the various data types and automatically present the field labels. So the Visualforce for the table would be:

<apex:pageBlockTable value="{!cars}" var="car">
    <apex:column value="{!car.Model__r.Brand__r.BrandName__c}"/>
    <apex:column value="{!car.Model__r.ModelName__c}"/>
    <apex:column value="{!car.CarName__c}"/>
</apex:pageBlockTable>

PS Re-reading the answer provided by Birthus, making the query on the Car and referencing the Model as a parent and the Brand as a grand-parent (but still using order by) as he does is the better way to go. Where this makes a difference is for example if you were presenting a list of Models and Cars within that Model. If you had a Model with no Cars and based the query on Cars the Model would not be presented at all; if you based the query on Model the Model would be presented with no Cars which is what you would want.