Custom Finder Methods, Part Two

Our next custom finder is very similar to the first, only this one executes a where with a SQL join.  In the last tutorial, we modified our table and controller to allow for finding all employees by last name; this time we'll show all employees for a given department.  Rather than use the department's name field, which may contain spaces, I've opted to select using code, a 1-4 character short-code from the departments table.

Model

Since we're still displaying employees, we've chosen the employees model and controller for the new code.  First, the custom finder:

// src/Model/Table/EmployeesTable.php
...
    public function findByDepartment(Query $query, array $options)
    {
        $departments = TableRegistry::get('Departments');
        
        $query
            ->where(['1' => '1'])
            ->join([
                'table' => 'departments',
                'alias' => 'd',
           'type' => 'INNER',
           'conditions' => 
              ['d.id = Employees.department_id',
              'd.code' => $options['dcode']]
        ]);
        return $query;
    }

Because we're in the model for the employees table but will be accessing department information as well, we need to load departments with TableRegistry.

In our query, you'll see the very strange "where 1=1" reference.  Even though this where doesn't need a "where" clause, I included it here for reference purposes; "1" will always equal "1" so it doesn't affect the result set.

As the example shows, we've passed the name of the table to which we're joining, the type of join (in this case, "INNER"), and the conditions of the join.  The id key field of departments should match the foreign key department_id of employees, and the code should match what was passed to us in the URL.

Controller

In the employees controller, I've added a method called "indept" so that one can access "employees/indept/XYZ":

    public function indept($dcode = null)
    {
        $this->set('employees', $this->paginate(
            $this->Employees->find(
                'byDepartment', 
                ['dcode' => $dcode])
                ->contain(['Departments'])
                ));
        $this->set('_serialize', ['employees']);
        
        if (is_null($dcode)) {
            $this->Flash->error(__('No value passed for department code.'));
        }       

        $this->render('index');
    }

What should immediately jump out to us is that the code here is virtually identical to what we used in the previously-created named method.  Not very DRY, huh?  Go ahead and put your code in as shown above and make sure that everything works; then we'll clean it up.

What we've done do far should allow both of the following URLs to produce the desired results:

• /employees/named/Smith
• /employees/indept/DIT

But we probably want to give our end-users a little more direct method of accessing these queries.

View (Template)

I thought it would be cool to use Google's Material Icons for the view changes, and this requires a quick side-trip into our default template.  Edit default.ctp and put in a line to include the Google Material Icons style sheet after cake.css:

// src/Template/Layout/default.ctp
...
    <?= $this->Html->css('base.css') ?>
    <?= $this->Html->css('cake.css') ?>
    <?= $this->Html->css('https://fonts.googleapis.com/icon?family=Material+Icons') ?>

    <?= $this->fetch('meta') ?>
    <?= $this->fetch('css') ?>
...

Now we have access all of the icon set and we can use them in our other templates.

Edit index.ctp in the employees folder and make the following changes:

// src/Template/Employees/index.ctp
...
<thead>
    <tr>
        <th><?= $this->Paginator->sort('first_name') ?></th>
        <th><?= $this->Paginator->sort('last_name') ?></th>
        <th><?= __(' ') ?></th>
        <th><?= $this->Paginator->sort('hire_date') ?></th>
        <th><?= $this->Paginator->sort('department_id') ?></th>
        <th><?= __(' ') ?></th>
        <th><?= $this->Paginator->sort('birth_date') ?></th>
        <th class="actions"><?= __('Actions') ?></th>
    </tr>
</thead>
<tbody>
    <?php foreach ($employees as $employee): ?>
    <tr>
        <td><?= h($employee->first_name) ?></td>
        <td><?= h($employee->last_name) ?></td>
        <td><i class="material-icons"><?= $this->Html->link(__('search'), ['action' => 'named', $employee->last_name]) ?></i></td>
        <td><?= h($employee->hire_date) ?></td>
        <td><?= $employee->has('department') ? $this->Html->link($employee->department->name, ['controller' => 'Departments', 'action' => 'view', $employee->department->id]) : '' ?></td>
        <td><i class="material-icons"><?= $this->Html->link(__('search'), ['action' => 'indept', $employee->department->code]) ?></i></td>
        <td><?= h($employee->birth_date) ?></td>
        <td class="actions">
            <?= $this->Html->link(__('View'), ['action' => 'view', $employee->id]) ?>
            <?= $this->Html->link(__('Edit'), ['action' => 'edit', $employee->id]) ?>
            <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $employee->id], ['confirm' => __('Are you sure you want to delete # {0}?', $employee->id)]) ?>
        </td>
    </tr>
    <?php endforeach; ?>
</tbody>
...

I ditched the "id" field to make more room, moved birth_date to where "created" was, then added blank headings and the material icon for "search".  The search icon after last_name links into the named method, passing the last name, and the search icon after the department name links to the indept method with the department's short code.

It should all look something like this:

Clicking on the search icons will now take you to another screen, showing the fruits of our labors... and more search icons (because we're using index.ctp for our search results).

This, too, is something we can clean up in part three.

Custom Finder Methods, Part One

Custom finder methods in CakePHP "are the ideal way to package up commonly used queries, allowing you to abstract query details into a simple to use method."

The examples below use the MySQL employee demo database; after running the scripts to install the database, bake up some basic CRUD with:

bin/cake bake all --everything

This will provide the basis upon which to follow many of the tutorials on this site.

A Simple Finder on Name

Let's start with a finder method, defined in the model for the employees table, that will return employees based on their last name:

/src/Model/Table/EmployeesTable.php
...
    public function findByName(Query $query, array $options)
    {
        $query->where([
            'Employees.last_name' => $options['lname']
        ]);
        return $query;
    }

We've created a new function, "findByName" that compares last_name in the Employees table to a passed value ('lname') from the $options array.

In our employees controller, we need a corresponding method:

/src/Controller/EmployeesController.php
...
    public function named($lname = null)
    {
        $this->set('employees', $this->paginate(
            $this->Employees->find(
                'byName', 
                ['lname' => $lname])));
        $this->set('_serialize', ['employees']);
        
        if (is_null($lname)) {
            $this->Flash->error(__('No value passed for last name.'));
        }       

        $this->render('index');
    }

You'll notice that this is almost identical to the index method with the exception of the finder method.  We could have put this in index and executed it conditionally, but I thought it cleaner to have a separate method.

Following CakePHP's idea of "convention over configuration," our finder method - named "findByName," is evoked by calling "find('byName')".  We're passing the last name which will become part of the $options array.

If no name was passed, we're flashing an error message.

We'll need a view for this, but the view in this case is identical to the existing "index.ctp" for employees; for now, let's just use it.

You should now be able to see a list of employees with a given last name by passing the last name in the URL, such as:

http://localhost:8765/employees/named/Facello

If you call "named" without passing a value, you'll see our error message:

More Complex Finders

So far we've walked through the logic of a finder method and we've created a method that can be called from anywhere to return a list of employees by last name.

In part two of this tutorial, we'll produce a list of all employees by their department number.