Sign Up ›

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

Looping through two lists creates too many queries

Here is the issue:

I have two lists that are being created by parsing two separate fields into plot point values. I need to loop through these lists simultaneously to create an X and a matching Y for a scatter graph. puting two for loops together will only create too many queries and not match the points correctly. How can I accomplish this goal?

 

My current code is below. It is producing the following error: Illegal Assignment from LIST<String> to string on Line 52

public with sharing class TestResultGraphConroller {

  
    public TestResultGraphConroller(ApexPages.StandardController stdController){
    }
    
    
    public String getTimeVolume(){
        return getTimeVolume(null);
    }
    
    public String getTimeVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col2','Interval','number'),
            new GoogleViz.Col('col1','Channel Volume','number')
            };
        decimal interval = 0;
        for(Trial__c tv : [Select Id, Channel_Volume__c from Trial__c])
        {
        	Integer v;
        	List<String> channelvolumevalues = tv.Channel_Volume__c.split(' ');
        	for(String cv: channelvolumevalues)
        	{
            GoogleViz.row r = new GoogleViz.row();
            r.cells.add ( new GoogleViz.cell( interval));//interval
            r.cells.add ( new GoogleViz.cell( cv ));//X-Axis
            gv.addRow( r ); 
            interval = interval + .01;
            }
        }
        return gv.toJsonString();
    }  

    public String getFlowVolume(){
        return getFlowVolume(null);
    }
    
    public String getFlowVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col1','Channel Volume','number'),
            new GoogleViz.col('col2','Channel Flow','number')    
            };              
               
        for(Trial__c tfv : [Select Id, Channel_Volume__c, Channel_Flow__c From Trial__c])
        {                                                          
            List<String> channelvolumevalues = tfv.Channel_Volume__c.split(' ');
            List<String> channelflowvalues = tfv.Channel_Flow__c.split(' ');
            for(String cv: channelvolumevalues)
            {
            	String cf = channelflowvalues;
            	GoogleViz.row r = new GoogleViz.row();
            	r.cells.add ( new GoogleViz.cell( cv ));//Y-Axis
            	r.cells.add ( new GoogleViz.cell( cf ));//X-Axis
            	gv.addRow( r ); 
            }
        }

        return gv.toJsonString();
    }  
}

 Can I get some assistance on the best way to do this? With the getTimeVolume it was much easier.

 

Best Answer chosen by Admin (Salesforce Developers) 
Ritesh AswaneyRitesh Aswaney

On line 52, you're assigning a List<String> to a String

String cf = channelflowvalues;

 

Oddly enough, you are passing a List<Id> to both methods, but it never seems to be used - i would imagine it forms part of the WHERE clause in your SOQL Query.

 

A slight tweak in the getFlowVolumes should get you sorted, assumiing getTimeVolume already works.

 

What I'm recommending below assumes that both channelvolumevalues and channelflowvalues have the same number of elements after the split. Use an integer counter to iterate over the elements of the two Lists and grab corresponding elements

public String getFlowVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col1','Channel Volume','number'),
            new GoogleViz.col('col2','Channel Flow','number')    
            };              
               
        for(Trial__c tfv : [Select Id, Channel_Volume__c, Channel_Flow__c From Trial__c])
        {                                                          
            List<String> channelvolumevalues = tfv.Channel_Volume__c.split(' ');
            List<String> channelflowvalues = tfv.Channel_Flow__c.split(' ');
            for(integer i=0; i< channelvolumevalues.size(); i++) //iterate using an integer
            {
                String cv = channelvolumevalues[i]; //get the element at the ith position
            	String cf = channelflowvalues[i]; //get matching element at the ith position
            	GoogleViz.row r = new GoogleViz.row();
            	r.cells.add ( new GoogleViz.cell( cv ));//Y-Axis
            	r.cells.add ( new GoogleViz.cell( cf ));//X-Axis
            	gv.addRow( r ); 
            }
        }

        return gv.toJsonString();
    }

 

All Answers

jblon1973jblon1973

So I know somehow I am going to need to map the values then loop through as I assign them. Still need help in figuring out how to code this

 
Ritesh AswaneyRitesh Aswaney

On line 52, you're assigning a List<String> to a String

String cf = channelflowvalues;

 

Oddly enough, you are passing a List<Id> to both methods, but it never seems to be used - i would imagine it forms part of the WHERE clause in your SOQL Query.

 

A slight tweak in the getFlowVolumes should get you sorted, assumiing getTimeVolume already works.

 

What I'm recommending below assumes that both channelvolumevalues and channelflowvalues have the same number of elements after the split. Use an integer counter to iterate over the elements of the two Lists and grab corresponding elements

public String getFlowVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col1','Channel Volume','number'),
            new GoogleViz.col('col2','Channel Flow','number')    
            };              
               
        for(Trial__c tfv : [Select Id, Channel_Volume__c, Channel_Flow__c From Trial__c])
        {                                                          
            List<String> channelvolumevalues = tfv.Channel_Volume__c.split(' ');
            List<String> channelflowvalues = tfv.Channel_Flow__c.split(' ');
            for(integer i=0; i< channelvolumevalues.size(); i++) //iterate using an integer
            {
                String cv = channelvolumevalues[i]; //get the element at the ith position
            	String cf = channelflowvalues[i]; //get matching element at the ith position
            	GoogleViz.row r = new GoogleViz.row();
            	r.cells.add ( new GoogleViz.cell( cv ));//Y-Axis
            	r.cells.add ( new GoogleViz.cell( cf ));//X-Axis
            	gv.addRow( r ); 
            }
        }

        return gv.toJsonString();
    }

 

This was selected as the best answer
Ritesh AswaneyRitesh Aswaney

Here is an alternate solution where both structures are constructed in one query. It however assumes that the List<Id> that gets passed in each case is the same, as it loads up both when either of timeVolume or flowVolume are retrieved.

 

public with sharing class TestResultGraphConroller {

  
    public TestResultGraphConroller(ApexPages.StandardController stdController){
    }
    
    
    public String getTimeVolume(){
        return getTimeVolume(null);
    }

    GoogleViz timeVolumeGV = null;
    GoogleViz flowVolumeGV = null;

    public void constructMaps(List<Id> ids){

        timeVolumeGV = new GoogleViz();
        flowVolumeGV = new GoogleViz();

         //initialise time voume        
        timeVolumeGV.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col2','Interval','number'),
            new GoogleViz.Col('col1','Channel Volume','number')
            };

        decimal interval = 0;

        //initialise flow volume
        flowVolumeGV.cols = new list<GoogleViz.col> { 
        new GoogleViz.Col('col1','Channel Volume','number'),
        new GoogleViz.col('col2','Channel Flow','number')    
            };  
        
        for(Trial__c tv : [Select Id, Channel_Volume__c, Channel_Flow__c  from Trial__c])
        {
            Integer v;
            List<String> cv = tv.Channel_Volume__c.split(' ');

            List<String> cf = tfv.Channel_Flow__c.split(' ');

            for(integer i = 0; i< cv.size(); i++)
            {
                GoogleViz.row tvr = new GoogleViz.row();
                tvr.cells.add ( new GoogleViz.cell( interval));//interval
                tvr.cells.add ( new GoogleViz.cell( cv[i] ));//X-Axis
                timeVolumeGV.addRow( tvr ); 
                interval = interval + .01;

                GoogleViz.row fvr = new GoogleViz.row();
                fvr.cells.add ( new GoogleViz.cell( cv[i] ));//Y-Axis
                fvr.cells.add ( new GoogleViz.cell( cf[i] ));//X-Axis
                flowVolumeGV.addRow( fvr ); 

            }
        }

    }
    
    public String getTimeVolume(List<Id> ids){

        if(timeVolumeGV == null)
            constructMaps(ids);

        return timeVolumeGV.toJsonString();
    }  

    public String getFlowVolume(){

        return getFlowVolume(null);
    }
    
    public String getFlowVolume(List<Id> ids){
       
        if(flowVolumeGV == null)
            constructMaps(ids);

        return flowVolumeGV.toJsonString();
    }  
}

 
jblon1973jblon1973

Thsi worked great in the Sandbox, but for some reason in production I am getting Attempt to dereference error:

public with sharing class TestResultGraphConroller {

  
    public TestResultGraphConroller(ApexPages.StandardController stdController){
    }
    
    
    public String getTimeVolume(){
        return getTimeVolume(null);
    }
    
    public String getTimeVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col2','Interval','number'),
            new GoogleViz.Col('col1','Channel Volume','number')
            };
        decimal interval = 0;
        for(Trial__c tv : [Select Id, Channel_Volume__c from Trial__c])
        {
          Integer v;
          List<String> channelvolumevalues = tv.Channel_Volume__c.split(' ');
          for(String cv: channelvolumevalues)
          {
            GoogleViz.row r = new GoogleViz.row();
            r.cells.add ( new GoogleViz.cell( interval));//interval
            r.cells.add ( new GoogleViz.cell( cv ));//X-Axis
            gv.addRow( r ); 
            interval = interval + .01;
            }
        }
        return gv.toJsonString();
    }
    
    public String getFlowVolume(){
        return getFlowVolume(null);
    }
    
	public String getFlowVolume(List<Id> ids){
        GoogleViz gv = new GoogleViz();              
        gv.cols = new list<GoogleViz.col> { 
            new GoogleViz.Col('col1','Channel Volume','number'),
            new GoogleViz.col('col2','Channel Flow','number')    
            };              
               
        for(Trial__c tfv : [Select Id, Channel_Volume__c, Channel_Flow__c From Trial__c])
        {                                                          
            List<String> channelvolumevalues = tfv.Channel_Volume__c.split(' ');
            List<String> channelflowvalues = tfv.Channel_Flow__c.split(' ');
            for(integer i=0; i< channelvolumevalues.size(); i++) //iterate using an integer
            {
                String cv = channelvolumevalues[i]; //get the element at the ith position
              String cf = channelflowvalues[i]; //get matching element at the ith position
              GoogleViz.row r = new GoogleViz.row();
              r.cells.add ( new GoogleViz.cell( cv ));//Y-Axis
              r.cells.add ( new GoogleViz.cell( cf ));//X-Axis
              gv.addRow( r ); 
            }
        }

        return gv.toJsonString();
    } 
}

 It is occurring on teh List<String> lines and I am not sure why. Looking through the error log it is this class. I have not written any code coverage for it at this point, could that cause the error when tring to generate the graphs?

 

Here is the VF that displays the graph:

<apex:page standardController="Trial__c" extensions="TestResultGraphConroller">
<html>
    <head>
        <script type="text/javascript" src="http://www.google.com/jsapi"></script>
        <script type="text/javascript">
            google.load("visualization", "1", {packages:["corechart"]});
            google.setOnLoadCallback(drawChart); 
 
            function drawChart() {
                var data = new google.visualization.DataTable( eval( '({!FlowVolume})' ) );
  
                var options = {
                                height: 600,
                                width: 600,
                                title: 'Flow Volume Loop',
                                tooltip: 'true',
                                legend: 'none',
                                hAxis: {title: 'Volume'},
                                vAxis: {title: 'Flow'},
                                pointSize: 0,
                                lineWidth: 2,
                                backgroundColor: '#e1dabe'
                                };
                
                var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
                
                
                chart.draw(data, options);
                }
                </script>
        </head>
        <body>
        <div id="chart_div" style="width: 600px; height: 600px;" align="center"></div>
        </body>
    </html>
</apex:page>

 
Ritesh AswaneyRitesh Aswaney

I'm guessing there are data rows in Production which could have null values, so adding a not null check should help

 

for(Trial__c tfv : [Select Id, Channel_Volume__c, Channel_Flow__c From Trial__c]) {

List<String> channelvolumevalues=tfv.Channel_Volume__c != null ? tfv.Channel_Volume__c.split(' ') : '';

List<String> channelflowvalues = tfv.Channel_Flow__c ! = null ? tfv.Channel_Flow__c.split(' ') : '';