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.

A DBMS Side Trip

A little break from the direction of my last post; in finishing up the Custom Finder Methods tutorial I realized that I really wanted to work with a database that was configured for CakePHP's naming conventions.  Plus I decided to make a couple of other modifications, such as dropping the dept_emp linking table (instead, employees are owned by departments) and adding a users table for subsequent authentication.

Here's the modified database, from which I'll be doing several future tutorials.

An Employee Database

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';

DROP TABLE IF EXISTS dept_emp_links,
                     dept_managers,
                     titles,
                     salaries, 
                     employees, 
                     departments;

   set storage_engine = InnoDB;

select CONCAT('storage engine: ', @@storage_engine) as INFO;

CREATE TABLE users (
    id          INT             NOT NULL AUTO_INCREMENT,
    username    VARCHAR(50)     NOT NULL,
    password    VARCHAR(255)    NOT NULL,
    role        ENUM ('administrator','editor', 'contibutor', 'viewer')  NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE departments (
    id          INT             NOT NULL AUTO_INCREMENT,
    code        CHAR(4)         NOT NULL,
    name        VARCHAR(40)     NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE  KEY (code)
);

CREATE TABLE employees (
    id          INT             NOT NULL AUTO_INCREMENT,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(20)     NOT NULL,
    last_name   VARCHAR(30)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    department_id   INT         NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (department_id),
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE dept_managers (
    id           INT             NOT NULL AUTO_INCREMENT,
    department_id    INT         NOT NULL,
    employee_id      INT         NOT NULL,
    from_date    DATE            NOT NULL,
    to_date      DATE            NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    KEY         (department_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
); 


CREATE TABLE positions (
    id          INT             NOT NULL AUTO_INCREMENT,
    employee_id INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    PRIMARY KEY (id)
); 
        
CREATE TABLE salaries (
    id          INT             NOT NULL AUTO_INCREMENT,
    employee_id INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    PRIMARY KEY (id)
);

For convenience, you can copy the above into a file and run the whole thing from the command line.  For example, if you store the script as "employees.sql" you could use:

mysql -u root -p < employees.sql

An App for the Tutorials

Create a CakePHP skeleton app:

composer create-project --prefer-dist cakephp/app cakehrms

Then edit your config/app.php file with the correct parameters needed for your new database:

    'Datasources' => [
        'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            //'port' => 'nonstandard_port_number',
            'username' => 'root',
            'password' => 'yourpasswordhere',
            'database' => 'employees',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'log' => false,

Next, bake:

bin/cake bake all --everything

Now you have a CRUD app on the new database from which you can work through several upcoming tutorials from this site.

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.