1

I am trying to add a search box to a Visual force page that renders a table. The page worked fine before adding the search. And now it works fine so long as I don't enter a search term.

When I leave the search box blank and press the "Search" button I get:

Return type of an Apex action method must be a PageReference. Found: visualforce.el.VisualforceArrayList

If I enter a term and press "search" I get:

System.QueryException: Didn't understand relationship 'res' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Error is in expression '{!getReservations}' in component in page reservation_list: Class.ReservationListController.getReservations: line 20, column 1 Class.ReservationListController.getReservations: line 20, column 1

The problem seems to be that I don't know how to tell the page to expect a list to be returned. I can't seem to declare the variables or return types properly?

I'm ignorant enough that I don't know what I am missing. The line number it refers to is the line in the controller that populates the list: res = database.query(queryString);

When I check the debug statements the list is populated correctly and like I said the list displays fine on initial page load, just not from the search button.

I don't know if it is the length or what, but no matter what I do I can't seem to paste the entire VF page into the editor and get it to show up. The two excerpts contain the problematic areas though.

My VF Page Excerpt:

<apex:pageBlock title="Search" >
    <apex:inputText value="{!searchString}" id="theSearchString" maxlength="100" size="110" /> &nbsp;
    <apex:CommandButton Id="btnSearch" action="{!getReservations}" rerender="ReservationsBlock" value="Search" /></apex:pageBlock>
<apex:pageBlock id="ReservationsBlock" title="Current Reservations">
    <apex:pageBlockTable value="{! reservations }" var="res">
        <apex:column headerValue="Reservation Name" value="{! res.Name }" /> 

My Controller:

public class ReservationListController {
    private String profileId;
    private String queryString;
    private String userId;        
    public String searchString {get; set; }

    public List<Reservation__c> getReservations() {
        profileId = userinfo.getProfileId();
        userId = userinfo.getUserId();
        if(searchString == null) {searchString = '';}
        if(searchString.length() == 0){        
            queryString = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id '+'FROM Reservation__c ';
        }
        else{
            queryString = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id '+'FROM Reservation__c  Where res.Copy_del1__r.Book__r.Name Like :' +  searchString;   

        }
        List<Reservation__c> res = new List<Reservation__c>();
        System.debug(queryString);
        res = database.query(queryString); 
        System.debug(res);
        //searchString = '';
        return(res);

    }
}
1

1) As the error says you cannot have the return type of an action method as anything but pagereference, so change it as it expects

    public pagereference getReservations() {
     return null;
     }

2) Since you changed your action function now create a reservation variable to be used in the vf page, instead of returning it in the action method

public List<account> reservations {get;set;}

3) Your like operator will not work so change it

queryString = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id '+'FROM Reservation__c  Where res.Copy_del1__r.Book__r.Name Like Like \'%' + searchString+ '%\'';   

Page:

<apex:page controller="ReservationListController" >
  <apex:form >
  <apex:pageBlock title="Current User Information">
        Hello {! $Profile.Name + " " + $User.FirstName + " " + $User.LastName } <br />
        Hello ID {! $User.Id } <br />
        <apex:outputLink value="~/apex/library_user_edit?id={! $User.Id }" id="editUserLink">Edit My User Information</apex:outputLink>
    </apex:pageBlock>
  <apex:pageBlock title="Search" >
      <apex:inputText value="{!searchString}" id="theSearchString" maxlength="100" size="110" /> &nbsp;
      <apex:CommandButton Id="btnSearch" action="{!getReservations}" rerender="ReservationsBlock" value="Search" />
  </apex:pageBlock>
  <apex:pageBlock id="ReservationsBlock" title="Current Reservations">
        <apex:pageBlockTable value="{! reservations }" var="res">
            <apex:column headerValue="Reservation Name" value="{! res.Name }" />            
            <apex:column headerValue="Title">
            <apex:outputLink value="/{!res.id}"></apex:outputLink>
            </apex:column>
            <apex:column headerValue="Author" value="{! res.Name}">
            </apex:column>

        </apex:pageBlockTable>

    </apex:pageBlock>
  </apex:form>
</apex:page>

controller:

   public class ReservationListController {
    private String profileId;
            private String queryString;
            private String userId;        
            public String searchString {get; set; }
            public List<Reservation__c> reservations {get;set;}
            public ReservationListController (){
            getReservations();
            }
            public pagereference getReservations() {
            profileId = userinfo.getProfileId();
            userId = userinfo.getUserId();
            system.debug('######'+searchString);
                if(searchString == null) {searchString = '';}
                if(searchString.length() == 0){        
                    queryString = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id '+'FROM Reservation__c ';
                }
                else{
                queryString = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id FROM Reservation__c  Where res.Copy_del1__r.Book__r.Name Like \'%' + searchString+ '%\'';   

                }
            List<Reservation__c> res = new List<Reservation__c>();
            reservations = database.query(queryString); 
        return null;
        }
    }
|improve this answer|||||
  • 2
    Almost, but you have a SOQL injection attack. – sfdcfox Feb 26 '16 at 22:50
  • 2
    "...if you give a damn about..." - a bit harsh? – cropredy Feb 26 '16 at 23:25
  • @crop1645 Lol tht was in a funny friday mood. Sorry for the language, removing it right away – Rao Feb 27 '16 at 5:01
4

You have several problems here, so let's go over them.

First, you're trying to use the same function to render the list (via a getter) and use it as an action function. You can't do this, it simply won't work. For that matter, you really should avoid querying in a getter, because it can have unintended side effects. So, starting from the beginning, you will most likely want either two functions or a variable and a function. Let's go with the latter, since it's easier:

public class ReservationListController {
    public String searchString { get; set; }
    public Reservation__c[] reservations { get; set; }
    public void doSearch() {
        // We'll get to this in a moment
    }
}

And, of course, your page will change:

<apex:CommandButton Id="btnSearch" action="{!doSearch}"
    rerender="ReservationsBlock" value="Search" />

Next, your doSearch function needs to perform a query. Here's how you'd search a "null" term:

public void doSearch() {
    String soqlQuery = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id FROM Reservation__c';
    if(String.isNotBlank(searchTerm)) {
        // We'll do this in a second
    }
    reservations = Database.query(soqlQuery);
}

After that, the problem with your query was trying to "bind" incorrectly. You don't want to construct your filter as follows: ' ... field like :'+searchTerm, because that will create an invalid query when it tries to resolve the part after the colon. Instead, simply bind to it directly:

String tempTerm;
if(String.isNotBlank(searchTerm)) {
    tempTerm = '%'+searchTerm+'%';
    soqlQuery += ' WHERE Copy_del1__r.Book__r.Name LIKE :tempTerm';
}

This will protect against SOQL injection attacks, and give you the proper results.

Putting it all together, we now have a complete solution:

public class ReservationListController {
    public String searchString { get; set; }
    public Reservation__c[] reservations { get; set; }
    public void doSearch() {
        String soqlQuery = 'SELECT id, Name, copy_del1__c, UserIdCreatedBy__c, Copy_del1__r.Book__r.Author_Name__c, Copy_del1__r.Book__r.Name, Copy_del1__r.Book__r.Id FROM Reservation__c';
        String tempTerm;
        if(String.isNotBlank(searchTerm)) {
            tempTerm = '%'+searchTerm+'%';
            soqlQuery += ' WHERE Copy_del1__r.Book__r.Name LIKE :tempTerm';
        }
        reservations = Database.query(soqlQuery);
    }
}
|improve this answer|||||
  • Don't we need to use String.escapeSingleQuotes() method as well? Just curious. – javanoob Feb 26 '16 at 23:09
  • 3
    @javanoob No, query interpolation does it magically for you. – sfdcfox Feb 26 '16 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.