CakePHP 3 Tutorial 16: Table Relationships

Submitted by naidim on Fri, 10/21/2016 - 15:43

When working with CakePHP it is important to remember "Convention over Configuration." If you start from the bottom, or back end, the database, and work your way forward correctly, CakePHP will do much of legwork for you, saving tons of redundant and repetitive work. If you don't, there are almost always ways to make things work the hard way.

An example of this is the relationships between tables. If you name your database tables and fields following CakePHP convention, it becomes trivial to set up relationships between them.

Let's start with an example. Our application contains "users" which will be stored in the users table. Each "user" in our application can have many "phone numbers" and while we could have multiple phone number fields in the users table, this is not proper database normalization, nor is it efficient storage of data. So we'll create a phone_numbers table to store the phone numbers.

1. Create the users table

Use the code from the previous tutorial to create the users table containing basic fields for our users: CakePHP 3 Tutorial 2

2. Create the phone_numbers table

CakePHP convention states to name tables as plural (they hold many records, so while one record is a "phone number", many records are "phone numbers"). Use underscores "_" in place of spaces between words. So our table for phone numbers becomes phone_numbers.

Is any of this "required?" No, you can use whatever table names you want, or what someone else has set up, it just means you need to manually identify everything that does not follow CakePHP convention.

Another convention uses to save time and energy is to identify relationships with a field that ties the tables together. Every user can "have many" phone numbers, every phone number "belongs to" a user. This is typically referred to as a one-to-many relationship. To identify which user each phone number belongs to, the phone number records will contain a foreign key holding the unique identifier, or id, of the user. This field should be named after the table and followed by "_id"

Again, this isn't "required", it just makes your job easier."

Lastly we want a field to store the number, a field to delineate the type of phone (Home, Work, Mobile) and the standard modified and created auto-timestamp fields.

CREATE TABLE `phone_numbers` (
  `id` bigint(11) NOT NULL,
  `user_id` bigint(11) NOT NULL,
  `number` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL
  `modified` datetime NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Set the Table Association

We could now easily bake the models, controllers and views from the console and CakePHP would automatically set up everything including the associations for us. But we won't learn anything from that so let's do it manually.

For the User, we can use the model, controllers, and views we created previously. For the phone_numbers table they will be almost identical, until we get to the associations.

In our phone number table (/src/Model/Tables/PhoneNumbersTable.php) we want to identify the "belongsTo" relationship with the users table.

public function initialize(array $config)
{
    ...
    $this->belongsTo('Users');
}

...and that's it.

Now, if you didn't, or can't, set your foreign key in the phone_numbers table to the assumed default, user_id, you can explicitly declare it.

public function initialize(array $config)
{
    ...
    $this->belongsTo('Users', [
        'foreignKey' => 'not_user_id',
    ]);
}

And for the users model (src/Model/Table/UsersTable.php, we set up the "hasMany" relationship.

public function initialize(array $config)
{
    ...
    $this->hasMany('PhoneNumbers', [
        'foreignKey' => 'not_user_id', // unnecessary if user_id
    ]);
}

4. Create Validation Rules

While editing the /src/Model/Table/PhoneNumbersTable.php file, it is smart to add a validation rule to verify that the id added to the user_id field has to be entered and that it actually exists in the table. While we'll be using a select form field, it may not be an issue, but why trust user input when you shouldn't?

public function validationDefault(Validator $validator)
{
    ...
    $validator
        ->requirePresence('user_id')
        ->notEmpty('user_id');
    return $validator
}
...
public function buildRules(RulesChecker $rules)
{
    ...
    $rules->add($rules->existsIn(['user_id'], 'Users'));
    return $rules;
}

5. Set the Controller Relationship

Now when getting our data for display, we need to ensure our SQL query "contains" the associated data of the phone numbers table.

In /src/Controller/UsersController.php update the view function:

public function view($id = null)
{
    $user = $this->Users->get($id, [
        'contain' => ['PhoneNumbers']
    ]);
    ...
}

Now when the user record is loaded, it will also load any associated phone numbers. Do the same for the phone numbers controller if you want to display the user data (e.g. full_name) in the phone number view, though, since you'll be viewing the phone numbers in the user's display, you may not need or want to.

6. Display the information in the View

Now that you are loading the phone numbers in the user controller, you can display the loaded information in the view.

Update your view (/src/Template/Users/view.ctp) to contain the phone numbers.

...
<div class="related">
    <h4><?php echo __('Phone Numbers'); ?>
    <?php if (!empty($user->phone_numbers)): ?>
    <table>
        <tr>
            <th><?php echo __('Type'); ?></th>
            <th><?php echo __('Number'); ?></th>
            <th class="actions"><?php echo __('Actions'); ?></th>
        </tr> 
        <?php foreach ($user->phone_numbers as $number): ?>
        <tr>
            <td><?php echo $number->type; ?>
            <td><?php echo $number->number; ?>
            <td class="actions">
                <?php echo $this->Html->link(__('Edit'), ['controller' => 'PhoneNumbers', $number->id]); ?>
            </td>
        </tr>
        <?php endforeach; ?>
    </table>
    <?php endif; ?>
</div>

Now the maybe the most complicated part is the add and edit views and controller functions, so I'll cover those next, separately.

Next: Adding Associated Records

Comments

Submitted by Kevin on Fri, 11/03/2017 - 05:27

Permalink

Hi,

Thank you for this example. I noticed a typo into the name of the function: I think this is "initialize" instead of "initilize".

Submitted by Gautier on Sun, 10/28/2018 - 15:43

Permalink

Je vous remercie pour les tutos bien détaillés sur cakephp . Un tutoriel sur l'ORM de cakephp sera bénéfique pour les débutants comme moi.

Add new comment