In CakePHP, how to have a Table belongsToMany OtherTable hasMany AnotherTable

Question

I am creating a tool that allows to generate dynamic forms. There are several tables in question:

• Form [the Form master table]
• FormField [JoinTable to Field]
• Field [the fields available for inclusion in Form]
• FieldValidation [The table containing relation data between the FormField and the Validation option]
• Validation [The available Validation options]

For the FieldValidation - this could in effect be a hasMany from Field, but I am unsure of whether I need to set up this relation from the Field table, or from the join table FieldValidation. The Validation table literally just includes the definitions for the validation options. This does not actually need to be a belongsToMany relation from the FormField/Field table. A hasMany is fine if that simplifies things.

Is this even possible?

Form -> [FormField] -> Field -> [FieldValidation] -> Validation

I have never done this before - so if there is a better way to approach this, I am all ears. My main concern is being able to select Form, contain Field's, and then contain the Validation for each field selected. Obviously, multiple validation rules can be selected per field.

Answer 1

A little late, but I did resolve this issue.

ERD Diagram of Physical Relationship in DB

Model: UsersTable

class UsersTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('users');
        $this->displayField('username');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsToMany('Projects', [
            'foreignKey' => 'user_id',
            'targetForeignKey' => 'project_id',
            'through' => 'ProjectsUsers'
        ]);

        $this->hasMany('ProjectsUsers', [
            'foreignKey' => 'user_id'
        ]);

    }
}

Model: ProjectsTable

class ProjectsTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('projects');
        $this->displayField('name');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsToMany('Users', [
            'foreignKey' => 'project_id',
            'targetForeignKey' => 'user_id',
            'through' => 'ProjectsUsers'
        ]);

        $this->hasMany('ProjectsUsers', [
            'foreignKey' => 'project_id'
        ]);

    }
}

Model: ProjectsUsersTable - this is the model for the JOIN table (through)

class ProjectsUsersTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('projects_users');
        $this->displayField('id');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsTo('Users', [
            'foreignKey' => 'user_id'
        ]);

        $this->belongsTo('Projects', [
            'foreignKey' => 'project_id'
        ]);

        $this->hasMany('ProjectsUsersPermissions', [
            'foreignKey' => 'projects_users_id'
        ]);

    }
}

Model: ProjectsUsersPermissions - this is the relation to the join table

class ProjectsUsersPermissionsTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('projects_users_permissions');
        $this->displayField('role');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsTo('ProjectsUsers', [
            'foreignKey' => 'projects_users_id'
        ]);

    }
}

Then the controller find action

$this->Projects->find()
        ->where(
            [
                'Projects.id' => $projectId
            ]
        )
        ->contain(
            [
                'Users',   // through belongsToMany
                'ProjectsUsers' => [  // through hasMany [joinTableModel]
                    'ProjectsUsersPermissions'  // through hasMany 
                ]
            ]
        )
        ->first();

This may be overkill for this scenario, and it is not my exact implementation - so don't think I am just doing unnecessary joins/contains. In my real life scenario, this works perfectly.

Hope this helps someone!