Sunday, December 13, 2015

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.

No comments:

Post a Comment