Sign Up ›

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

SOQL query on Search field which is a lookup field value in master detail relationship

Hi All,
I have a requirement to search Course Name in Visual force page.
I have to return all Training Deal Object values based on the course name entered in the search field value.
Course Master: Master Table
Training Deal:Detail Table
Training Deal has a master-detail datatype Course__c field
when I query the Training Deal table I get RecordID of the Course__c field instead of the Course Code value.
How to pass this RecordID in the query to Course Master and get the search results.
I have tried many combinations in below apex class....sorry for the messy looking queries..
VF Page
<apex:page controller="searchname1">
  <apex:form >
   <apex:pageblock id="one" >
    <apex:pageblockSection >
    <apex:pageblocksectionitem >
       <apex:outputlabel >Course to Search</apex:outputlabel>
         <apex:inputtext value="{!name}"/>
         </apex:pageblocksectionitem>
         <apex:commandButton value="go" action="{!search}"/>
          </apex:pageblockSection>
           </apex:pageBlock> 
           <apex:pageBlock rendered="{!searched}" >
            <apex:pageblocktable value="{!lstcourse}" var="c">
            <apex:column value="{!c.Name}"/>
             <apex:column value="{!c.Course_Name__c}"/>
            </apex:pageblocktable>
            <apex:pageblockbuttons >
             <apex:commandButton value="edit" action="{!edit}"/>
            </apex:pageblockbuttons>
               </apex:pageblock>
  </apex:form>
</apex:page>

Apex Class:

public with sharing class searchname1 {

    public PageReference edit() {
       
        return null;
    }


   
   public String name { get; set; }
    public list<Training_Deal__c> lstdeal { get; set; }
    public list<Course_Master__c> lstcourse { get; set; }
    
    public boolean searched{get;set;}
    
    //default constructor
    public searchname1(){
    searched=false;
    string namestr=apexpages.currentpage().getparameters().get('name');
    if(null!=namestr){
    name=namestr;
    }
    }
    public PageReference search() {
    searched=true;
     string searchstr1=('%'+name+'%');
      lstdeal=[select Name,Course__c from Training_Deal__c ];
      //lstcourse=[select Name,Course_Name__c from Course_Master__c where ID=:Training_Deal__c.Course__c];
    /* lstcourse= [select Name,Course_Name__c from Course_Master__c 
                 where Course_Name__c like :searchstr1 limit 10 ];
     for(Course_Master__c crs:lstcourse )
     {
         //coursecode=crs.Id
         lstdeal=[select Name,Course__c from Training_Deal__c where Course__c LIKE :crs.Course_Name__c ];
     }
     */
    //lstdeal=[select Name,Course__c from Training_Deal__c where Course__c like searchstr1 limit 10 ];
        return null;
    }


    
}



 
Best Answer chosen by srkSF
OFröhlichOFröhlich
Hi,

have a look at this page:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

It will look something like this:
select id, name, (select id, name from training_deal...) from Course_Master__c  where Course_Name__c like :searchstr1 limit 10
training_deal... is not the object name, it is the relationship name.

If this helps, please mark as Best Answer to help others too.

All Answers

OFröhlichOFröhlich
Hi,

have a look at this page:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

It will look something like this:
select id, name, (select id, name from training_deal...) from Course_Master__c  where Course_Name__c like :searchstr1 limit 10
training_deal... is not the object name, it is the relationship name.

If this helps, please mark as Best Answer to help others too.
This was selected as the best answer
srkSFsrkSF
Hi Oliver, Thank you for replying. I was able to write the sub query and it’s working fine now. I am getting another error now: Attempting to reference a null object. I have used Map object to store the values from Query and want to display on VF page. I am stuck here right now. Please help me. Thanks in advance.
OFröhlichOFröhlich
Hi,

easiest is to store query result in a list and loop threw the lists and do the magic in the second for (store the results in a new list or map and use that list/map on the VF page):

for(Course_Master__c  cm : cmList) { 
     for(training_deal__c td : cm.training_deal...) {
          System.debug(td.name);
     }
}
 
srkSFsrkSF
Thank you Oliver for replying.You are right...I have posted the Apex Code where I got this Error.

I am getting Null Pointer Exception: Attempting to de-reference a null object at this line only-----> DealsMap.put(deal.id,deal)

User-added image
It's not displaying anything from Map Collection in my VF Page also.
Please help me!

Apex Class:

public with sharing class searchname1 {

    public PageReference edit() {
       
        return null;
    }

   public String name { get; set; }
    public list<Training_Deal__c> lstdeal { get; set; }
    public list<Course_Master__c> lstcourse { get; set; }
    public Map<id, Training_Deal__c> DealsMap { get; set; }
   
    
    public boolean searched{get;set;}
    
    //default constructor
    public searchname1(){
    searched=false;
    string namestr=apexpages.currentpage().getparameters().get('name');
    if(null!=namestr){
    name=namestr;
    }
               
    }
    public PageReference search() {
    searched=true;
     string searchstr1=('%'+name+'%');
         
           
        System.debug('-----------before for loop');
        try{
        for ( Course_Master__c crs : [select ID,Name,Course_Name__c,
                                      (select ID,Name,Course__c,Fees_Finalized__c from Training_Deals__r) from Course_Master__c 
            where Course_Name__c LIKE :searchstr1] ) {
                            for ( Training_Deal__c deal : crs.Training_Deals__r ) {
                                  System.debug(deal.Course__c + ' - ' + deal.Name + ' - ' + deal.Fees_Finalized__c );
                                 DealsMap.put(deal.id,deal);
                                 System.debug('Map of Deals-----'+DealsMap);  
                                }
                 
                            }
             System.debug('----------After for loop');   
        }catch(Exception e){
            System.debug('Exception occured'+e);
        }                    
          
              return null;
    }


    
}
VF Page:
<apex:page controller="searchname1" >
  <apex:form >
   <apex:pageblock id="one" >
    <apex:pageblockSection >
    <apex:pageblocksectionitem >
       <apex:outputlabel >Course to Search</apex:outputlabel>
         <apex:inputtext value="{!name}"/>
         </apex:pageblocksectionitem>
         <apex:commandButton value="Search" action="{!search}"/>
          </apex:pageblockSection>
      </apex:pageblock>       
           <apex:pageBlock id="SearchResults" rendered="{!searched}" >
               
           <!-- <apex:pageblocktable value="{!lstcourse}" var="c">
            <apex:column value="{!c.Name}"/>
                
             <apex:column value="{!c.Course_Name__c}"/>
            
               </apex:pageblocktable>
<apex:pageblocktable value="{!DealsMap}" var="d">
           
             <apex:column value="{!d.Name}"/>  
             <apex:column value="{!d.Course__c}"/>
             <apex:column value="{!d.Fees_Finalized__c}"/>     
            </apex:pageblocktable>
--> 
               

    <apex:pageBlock >
        <apex:repeat value="{!DealsMap}" var="d">
       {!d}<br/><br/>
         <apex:repeat value="{!DealsMap[d]}" var="d1">
        {!d1.name} <br/><br/>
         </apex:repeat>

    </apex:repeat>
               </apex:pageBlock>
        
   
     <apex:pageblockbuttons >
             <apex:commandButton value="edit" action="{!edit}"/>
            </apex:pageblockbuttons>
      </apex:pageBlock>
  </apex:form>
</apex:page>