function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Search with in a Search

Hi,

 

This code pulls back via a VF page availability within date range (start and end date).  It list room allocations which as an object has a lookup to a room record on the room object..  

 

I would like to run a second query based on the first.

 

For the rooms that a brought back i.e rooms allocated I would like to bring back a separate list rooms not allocated.  Room__c highlighted below is the lookup to the room__c object.

 

So I think using the list of room allocation from the first query I will know the room__c for each record.  I want to use the room_c name against the room object to show rooms that are not in that list.

 

So on the VF page I will have two list rooms allocated and rooms not allocated within a give date range.

 

Any help would be great.

 

Thanks

 

Ross

 

 

Public class Roommanager {

    public PageReference room() {
        return null;
    }

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){
            List<Room_Allocation__c> listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            return listBd;   
        }
}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Ritesh AswaneyRitesh Aswaney
Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            
             return listBd;
 }

public List<Room__c> getAvailableRooms()
{
if(listBd == null || listBd.size() == 0)
getRooms();

List<Id> allocatedRooms = new List<Id>();

for (Room_Allocation__c roomAlloc : listBd)
allocatedRooms.add(roomAlloc.Room__c);

return ([Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms;

} 
}

All Answers

Ritesh AswaneyRitesh Aswaney

If I've understood that right, then this should do it :

 

List<Id> allocatedRooms = new List<Id>;

 

for(Room_Allocation__c allocRooms : getRooms() )

allocatedRooms.add(allocRooms.Room__c);

 

List<Room_c> unallocatedRooms = [Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms];

 

I've added an inner query for querying Room Allocations for Unallocated Rooms, in case you wanted to access Allocations at dates not in the range.

 

 
laytro1978laytro1978

Thanks for this very new to APEX, although I am web designer / longterm SFDC admin.

 

I get a System.LimitException: Too many SOQL queries: 101  with a lot of errors, code is below probably my fault.

 

Any thoughts welcome.

 

Thanks

 

Ross

 

 

Public class Roommanager {

    public PageReference room() {
        return null;
    }

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){
            List<Room_Allocation__c> listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            
           List<Id> allocatedRooms = new List<Id>();
         
             for(Room_Allocation__c allocRooms : getRooms())
             allocatedRooms.add(allocRooms.Room__c);
             List<Room__c> unallocatedRooms = [Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms]; 
             
             return listBd;
            
            }  
}

 

 

 

 
Ritesh AswaneyRitesh Aswaney

Salesforce has some governor limits - in order to ensure efficient usage of code and/or resources.

 

It seems like your SOQL queries are being executed in a loop, which is causing it to exceed the 100 SOQL query limit.

 

Please check the source of this loop - it could also be the apex:repeat tag in your VF page calling the method in a loop.
laytro1978laytro1978

Thanks I started to read about limits but as mentioned I am very new to APEX.

 

I don't have a repeat in VF Page as you can see code below.

 

 

<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <apex:commandButton action="{!room}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <apex:inputField value="{!room.Search_Start_Date__c}"/>
                    <apex:inputField value="{!room.Search_End_Date__c}"/> 
                </apex:pageBlockSection>

            </apex:pageBlock>  
            <apex:pageBlock title="Room Bookings">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Name}"  />
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
    </apex:form>
</apex:page>

 

I am a bit lost to say the least.

 
Ritesh AswaneyRitesh Aswaney
Try this - it shouldn't really give you that too many SOQL queries error -there don't seem to be any SOQL queries in loops.


Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

       if(listBd == null || listBd.isEmpty())
{
            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            
           List<Id> allocatedRooms = new List<Id>();
         
             for(Room_Allocation__c allocRooms : getRooms())
             allocatedRooms.add(allocRooms.Room__c);

             List<Room__c> unallocatedRooms = [Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms]; 
           }
 
             return listBd;
            
            }  
}
laytro1978laytro1978

Sorry I am probably not understanding.  The code produces no errors however the Booked Room table no longer works all records are returned.

 

I have added to my VF page to display the second table all new code below.  Not sure what is going wrong.

 

 

<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <apex:commandButton action="{!room}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <apex:inputField value="{!room.Search_Start_Date__c}"/>
                    <apex:inputField value="{!room.Search_End_Date__c}"/> 
                </apex:pageBlockSection>
            </apex:pageBlock>  
            
            <apex:pageBlock title="Booked Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Name}"  />
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 

            <apex:pageBlock title="Available Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!rooms}" var="avail">
                        <apex:column value="{!avail.Room__c}"  />
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock>             
            
            
    </apex:form>
</apex:page>

 

 

 

Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

       if(listBd == null || listBd.isEmpty())
{
            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            
           List<Id> allocatedRooms = new List<Id>();
         
             for(Room_Allocation__c allocRooms : getRooms())
             allocatedRooms.add(allocRooms.Room__c);

             List<Room__c> unallocatedRooms = [Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms]; 
           }
 
             return listBd;
            
            }  
}

 

 

 
Ritesh AswaneyRitesh Aswaney
Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Reservation__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)];  
            
             return listBd;
 }

public List<Room__c> getAvailableRooms()
{
if(listBd == null || listBd.size() == 0)
getRooms();

List<Id> allocatedRooms = new List<Id>();

for (Room_Allocation__c roomAlloc : listBd)
allocatedRooms.add(roomAlloc.Room__c);

return ([Select Id, Name, (Select Id, Name, Start_Date__c, End_Date__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms;

} 
}
This was selected as the best answer
laytro1978laytro1978

Thanks so much, I now understand how the inner query works.

 

Brilliant.

 

Cheers

 

R
Ritesh AswaneyRitesh Aswaney

Awesome! Cheers mate :D