The examples on this site are currently tested to work on Phalcon V3.4 and Phalcon Devtools V3.2 Some issues may arise when using later versions.

Please get in touch or post a comment below the post if you encounter a problem.

There are a range of techniques available for controlling and refining the database search results.

Where possible its a good idea to use techniques that allow the query to be performed on the database and limit the search results in the resultset as far as possible. Applications which become succesful on the web can become very large very quickly and where initially a search would have yielded dozens of results this very quickly become hundreds or thousands. When this happens performance of the system will be hit.

It's a good idea to imagine the system becoming an overnight success and imagine yourself having to scramble to scale it very quickly. Use of some good practice early on may at least stop the system falling over in the event that you go viral. This will buy you some valuable time to scale the system to cope with the extra traffic. This could be the difference between becoming an overnight success or an overnight failure.

With this in mind, it is a good idea  to avoid using Model::find() where possible. If you want to refine search results and there is some complexity involved the temptation will be to return a all the records in the database and loop through the results using PHP to refine the search. There may be times when this is necessary but probably not many. If you can put this responsibility on the database engine it will make for a more efficient overall use of the systems scarce resources.

Consider the following example. We want to narrow the list of Members of the tennis club returned to only those over a certain age. The age itself is not stored in the database but we do have a getAge() function in the Member model. The temptation is to return all Members, loop through the array and include the Members that are over the specified age in another array which can then be displayed. This would look like this:

$members = Member::find()
foreach ($members as $member) {
    if ($member->getAge()>$age) {
        $memberReaults[] = $member;
    }
}
$this->view->members = $memberResults;

At first it seems straightforward but if the Membership becomes large there will be a performance hit. A better way to approach this is to figure out the date before which members in this set will have been born and then use that to return the resultset thereby letting the database do the work. This is good practice should lead to better overall performance of the system.

To do this we need to add code to the Member model which will return all Members over a certain age. Edit /app/models/member and add the following code.

public static function findOverage($age)
{
    $today = new \DateTime();
    $bornBeforeDate = $today->sub(new \DateInterval("P" . $age . "Y"));
    return parent::find(['conditions' => 'dateofbirth < :dob:', 'bind' => ['dob' => $bornBeforeDate->format('Y-m-d')]]);
}

The DateInterval class describes the amount of time between two dates. Passing, for example P10Y to the constructor will return an interval of 10 years. This interval is then substracted from the current date and used to return a resultset with everyone with a date of birth which is before this date.

While we are altering the model we may as well add a function which returns members that are under a certain age.

public static function findUnderage($age)
{
    $today = new \DateTime();
    $bornAfterDate = $today->sub(new \DateInterval("P" . $age . "Y"));
    return parent::find(['conditions' => 'dateofbirth > :dob:', 'bind' => ['dob' => $bornAfterDate->format('Y-m-d')]]);

}

The next step is to use these functions to retrieve and display the results. To do this we will borrow some code from the searchAction function which was created by the scaffolder. Consider the following code:

The section in red is concerned with setting up search criteria and retrieving results. The section in green is concerned with giving a message in the event that there are no search results and setting up the paginator and view variables.

We can now replace the section in red with one simple line which will return all Members over a certain age.

$member = Member::findOverage($age);

The section in green can remain and we can add an additional line which will allow us to use the existing view file /app/views/member/search.phtml to display our results. To use the existing view in this way we will add the following line.

$this->view->pick("member/search");

Given that we can now find Members either over or under a certain age it would be good if our function could do either. To do this we can add an argument to the function which will determine whether we're searching for members over or under a certain age. This argument will be the name of the function to be called on the member model - either findOverage($age) or findUnderage($age). By passing the name of the function to be called we can use a PHP variable function to determine which we want to call. This saves us having to use an if statement or a switch statement and neatens up the code. The final findAge($overOrUnder,$age) function now looks as follows - add this to /app/controllers/MemberController.php

public function findAgeAction($overUnder,$age)
{
    $member = Member::$overUnder($age);
    $numberPage = 1;
    
    
    if (count($member) == 0) {
        $this->flash->notice("The search did not find any Member");

        $this->dispatcher->forward([
            "controller" => "Member",
            "action" => "index"
        ]);

        return;
    }
    $this->view->pick("member/search");
    $paginator = new Paginator([
        'data' => $member,
        'limit'=> 10,
        'page' => $numberPage
    ]);

    $this->view->page = $paginator->getPaginate();
    
}

Now you can access the access the information by visiting

http://localhost/tennisClub/member/findAge/findUnderage/21

This will throw up the same search view with only those members under 21.


The only problem now is that the URL is becoming unweildy and confusing. If only we had some way of having a different URL which mapped to this one...