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);

    }
}

2 Answers 2

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;
        }
    }
3
  • 2
    Almost, but you have a SOQL injection attack.
    – sfdcfox
    Commented Feb 26, 2016 at 22:50
  • 2
    "...if you give a damn about..." - a bit harsh?
    – cropredy
    Commented Feb 26, 2016 at 23:25
  • @crop1645 Lol tht was in a funny friday mood. Sorry for the language, removing it right away
    – Rao
    Commented Feb 27, 2016 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);
    }
}
2
  • Don't we need to use String.escapeSingleQuotes() method as well? Just curious.
    – javanoob
    Commented Feb 26, 2016 at 23:09
  • 3
    @javanoob No, query interpolation does it magically for you.
    – sfdcfox
    Commented Feb 26, 2016 at 23:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .