+ Start a Discussion
SFmaverickSFmaverick 

SOQL Queries

Currently I have a visualforce page that takes a Date as user input and returns a table of records. Each column of the table represents a list of Shifts, the first list being the list of shifts that occurs on the date the user input - and then each column after incrementing that date by one.

 

Here's a quick visual of what I described without the actual records showing (confidential information):

 

 

Below each day, the records are output in an HTML Table. Each day is currently a seperate query that looks like this:

 

 

    public List<Shift__c> getTheList2() {
        Date NewDate = IncrementDayDate(1);
        If (Positions == 'Tech'){
            List<Shift__c> TheList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c = :NewDate AND (Position__C = 'Tech' OR Position__C = 'Pharmacy Support' OR Position__C = 'Material Handler')  ORDER BY Shift_Summary__c, Shift_Summary_2__c];
            return TheList2;
        } else {
            List<Shift__c> TheList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c = :NewDate AND Position__C = 'RPh'  ORDER BY Shift_Summary__c, Shift_Summary_2__c];
            return TheList2;
        }
    }

 

 

This ends up meaning 7 queries to the server for the lists, although the only difference in the filter is the Date. Ideally I'd only pull this list once and then seperate out each of the 7 days. Any ideas on how to do this?

 

Can you build a list, from a list? If Master_List exists as a List of all of the records for each day, could I pull each individual list from it like this? I want to avoid making 7 calls to the server to improve performance and code efficiency.

 

 

            List<Shift__c> TheList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM :Master_List WHERE Date__c = :NewDate AND Position__C = 'RPh'  ORDER BY Shift_Summary__c, Shift_Summary_2__c];
return TheList2;
}
}

 It would be even better if I could pull the Master List, and use something in my Visualforce code to spit out each day. The shorter my Apex code becomes - the easier future modification comes. I'm getting close to reaching governor limits on this page due to other queries, so I wanted to find room to improve the code.

 

Thanks for your help!

 

Damien_Damien_

This will return your list with every days of the week at once, with it sorted by the day initially.

 

 

public List<Shift__c> getTheList2() {
List<Date> newDates = new List<Date>();
newDAtes.add(IncrementDayDate(1));
List<Shift__c> theList2;
If (Positions == 'Tech'){
theList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND (Position__C = 'Tech' OR Position__C = 'Pharmacy Support' OR Position__C = 'Material Handler') ORDER BY Date__c, Position__c, Shift_Summary__c, Shift_Summary_2__c];
} else {
theList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND Position__C = 'RPh' ORDER BY Date__c, Shift_Summary__c, Shift_Summary_2__c];
}
return theList2;
}

 

Damien_Damien_

Actually this is slightly better...

 

public List<Shift__c> getTheList2() {
List<Date> newDates = new List<Date>();
newDAtes.add(IncrementDayDate(1));

If (Positions == 'Tech')
return [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND (Position__C = 'Tech' OR Position__C = 'Pharmacy Support' OR Position__C = 'Material Handler') ORDER BY Date__c, Position__c, Shift_Summary__c, Shift_Summary_2__c];
else
return [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND Position__C = 'RPh' ORDER BY Date__c, Shift_Summary__c, Shift_Summary_2__c];
}
SFmaverickSFmaverick

The problem is that still makes 7 individual calls to the server - it might actually be even slower because I'd have to do 'refreshes' of each section where this function was called in order to generate and display the next day.

 

Ideally my new server call would echo something like this and would pull all 7 days in one request from the server:

 

 

        If (Positions == 'Tech')
            return [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE (Date__c > DayDate) AND (Date__c < DayDate +7) AND (Position__C = 'Tech' OR Position__C = 'Pharmacy Support' OR Position__C = 'Material Handler')  ORDER BY Date__c, Position__c, Shift_Summary__c, Shift_Summary_2__c];
        else 
            return [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE (Date__c > DayDate) AND (Date__c < DayDate + 7) AND Position__C = 'RPh'  ORDER BY Date__c, Shift_Summary__c, Shift_Summary_2__c];
}

 

What I'm trying to figure out is how I can logically seperate each day out of this list to iterate over it on my visualforce page so it can be output into the HTML Table. Can you build a list from a list?

 

Damien_Damien_

Well, I built a list from a list in what I gave you before.  If it is taking you 7 queries still then its because you did not adjust your current code to fit what I gave you.

 

The code I gave you puts the next 7 days of a week into a list, and then returns everything for the next 7 days.  It does pretty much what you are asking, except that other code needs adjusted to accomadate for it.

Damien_Damien_

 


SFmaverick wrote:

Ideally my new server call would echo something like this and would pull all 7 days in one request from the server:

 


and yes, the code I gave you does this.

 

SFmaverickSFmaverick

I'm sorry maybe you can make me understand then - how this will output correctly without reconstructing that list 7 times, which would require seven seperate pings to the server. Here is how I'm calling the lists I currently have from my visualforce page - Each one iterates through the list providing one column in the table - each column represents one day (one date). If I make this same call 7 times, am I going to get one day at a time from your code?

 

 

        <apex:repeat value="{!TheList2}" var="item"> <!-- Gets the list -->
            <tr>
                <td bgcolor="{!item.BGCOLOR__c}" onClick="window.open('https://na7.salesforce.com/{!item.ID}');" style="cursor:pointer;" title="{!item.Notes__c}">

<apex:outputPanel rendered="{!item.Shift_Cancelled__c}"> <Strike>{!item.Shift_Summary__c} <br></br>{!item.Shift_Summary_2__c} <br></br>{!item.Shift_Summary_3__c}</Strike> </apex:outputPanel>
<apex:outputPanel rendered="{!!item.Shift_Cancelled__c}"> {!item.Shift_Summary__c} <br></br>{!item.Shift_Summary_2__c} <br></br>{!item.Shift_Summary_3__c} </apex:outputPanel> </td> </tr> </apex:repeat>

 

 

Damien_Damien_

I think I understand your meaning a little bit better.  I have not tested this, but with all syntax correct it should either give you what you want or enough for you to slightly modify to give what you want.

 

public List<List<Shift__c>> getTheList2() {
List<Date> newDates = new List<Date>();
newDates.add(IncrementDayDate(1));
List<Shift__c> theList2;
If (Positions == 'Tech'){
theList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND (Position__C = 'Tech' OR Position__C = 'Pharmacy Support' OR Position__C = 'Material Handler') ORDER BY Date__c, Position__c, Shift_Summary__c, Shift_Summary_2__c];
} else {
theList2 = [SELECT Shift_Cancelled__c, Notes__c, ID, BGCOLOR__c, Shift_Summary__c, Shift_Summary_2__c, Shift_Summary_3__c, Day_of_week__c, Date__c FROM Shift__c WHERE Date__c IN: newDates AND Position__C = 'RPh' ORDER BY Date__c, Shift_Summary__c, Shift_Summary_2__c];
}
List<List<Shift__c>> dayList = new List<List<Shift__c>();
List<Shift__c> temp = new List<Shift__c>();
Date__c aDate = null;
for (Shift__c curr: theList2)
{
if (aDate == curr.Date__c)
{
temp.add(curr);
}else
{
if (temp.size() > 0)
dayList.add(temp);
temp.clear();
temp.add(curr);
aDate = curr.Date__c;
}
}
if (temp.size() > 0)
nestedList.add(temp);
return nestedList;
}

 

Nested repeats to bring out the different days.

 

<apex:repeat value="{!TheList2}" var="day"> <!-- Gets the list -->
<apex:repeat value="{!day}" var="item"
<tr>
<td bgcolor="{!item.BGCOLOR__c}" onClick="window.open('https://na7.salesforce.com/{!item.ID}');" style="cursor:pointer;" title="{!item.Notes__c}">

<apex:outputPanel rendered="{!item.Shift_Cancelled__c}">
<Strike>{!item.Shift_Summary__c}
<br></br>{!item.Shift_Summary_2__c}
<br></br>{!item.Shift_Summary_3__c}</Strike>
</apex:outputPanel>

<apex:outputPanel rendered="{!!item.Shift_Cancelled__c}">
{!item.Shift_Summary__c}
<br></br>{!item.Shift_Summary_2__c}
<br></br>{!item.Shift_Summary_3__c}
</apex:outputPanel>
</td>
</tr>
</apex:repeat>
</apex:repeat>