cakephp3根据类型将一个表链接到多个表

Question

So here's my problem. I need to link an insurance policy to the insured property/item. Now the details vary greatly from car policy to a house or business one. So what I want to do is have something like this on the policies table

Policies
item_id
item_type

and that links to different tables depending on the value of the field "item_type" for example:

item_type = car then link to the cars table
item_type = house then link to the houses table
item_type = business then link to the businesses table
and so on...

I can do that on my own with php and mysql but I want to know the proper way to do it using CakePHP's table relationships and linking. I tried using the through option and a relationship table but it's not the same. Any ideas? or if a relationship table is the only way to do it then tell me how please.

Answer 1

This is actually a lot simpler than it first appears. I've done this a few times so I'll detail the technique that I use.

The first thing to do is create a behavior. This will allow you to enable any Table class in your application to have a policy attached to it.

The behavior is very simple. I've changed it to match your example, as I understand it. I'll talk through it after the code.

namespace App\Model\Behavior;

use Cake\Event\Event;
use Cake\ORM\Behavior;
use Cake\ORM\Query;

class PolicyBehavior extends Behavior
{
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->_table->hasMany('Policies', [
            'className' => 'Policies',
            'foreignKey' => 'table_foreign_key',
            'bindingKey' => 'id',
            'conditions' => ['table_class' => $this->_table->registryAlias()],
            'propertyName' => 'policies'
        ]);
    }

    public function beforeFind(Event $event, Query $query, \ArrayObject $options, $primary)
    {
        $query->contain(['Policies']);
        return $query;
    }
}

So the in the initialize method we need to create a relationship to the table we attached the behaviour to. This will create a Table hasMany Policies relationship, meaning that any item in your system can have many policies. You can update this relationship to match how you're working.

You can see that there are a number of options defined in the relationship. These are important, as they link the tables items together. So the table_foreign_key is a field in your policies db table used to store the primaryKey of the related item. So if you're attaching a Policy to a Car, this would be the Car.id. The bindingKey is the key used in the Policy table to join on.

In order to filter the different types of attachments, you need the table_class field in your policies db table. This will be the name of the attached table class. So Cars, Cats, Houses etc. Then we can use this in the conditions, so anything pulling the primary table class will automatically filter the related Policies to match.

I've also configured the propertyName, this means that any item you look for which contains Policies will have an entity property called policies with the related data inside.

The last function in the behaviour is the beforeFind, this just ensures that whenever you look for the primary table class, you always return the related policies, you don't have to use this if you don't want to, but I found it handy to always have the related data in my use-case.

So then, how do we use this new behaviour? Just attach it like you would any other behaviour, and that's it. $this->addBehavior('Policy').

Be aware
This just reads data, you'll need to ensure that you save the table alias, and the foreignKey into the related table when creating new items.

Just for clarity, your policies table schema will need, at a minimum.

policies.id
policies.table_class VARCHAR(255)
policies.table_foreign_key INT(11)