cakephp let user add custom fields to model

I'm considering using CakePHP (2.x) for a new project I'm working on. The project would have users that have clients. There would be default fields on the client model like name, address, email, etc.. but my client would like users to be able to add custom fields to their clients.

So User1 might want to add a "favorite color" field to their clients and User2 might want to add a "favorite sport" field to their clients. (These are just examples)

Is there a way to do this with CakePHP? What is the best solution for adding fields to models after their controllers, models, and views have already been baked?

My first thought was to set up the following models

User ( id, username, password ) hasMany: Clients

Client ( id, first_name, last_name, user_id ) belongsTo: User hasMany:ClientFields

ClientFieldKey ( id, field_name, user_id ) belongsTo: User hasMany:ClientFieldValues

ClientFieldValue ( id, client_field_key_id, field_value ) belongsTo: ClientFieldKey

ClientField ( id, client_id, client_field_value_id ) belongsTo: Client, ClientFieldValue

then I could have something like the following

Client ( id: 1, username: user1, password: pass1, user_id: 1)

Client ( id: 2, username: user2, password: pass2, user_id: 1)

Client ( id: 3, username: user3, password: pass3, user_id: 2)

Client ( id: 4, username: user4, password: pass4, user_id: 2)

ClientFieldKey ( id: 1, field_name: Color, user_id: 1 )

ClientFieldKey ( id: 1, field_name: Sport, user_id: 2 )

ClientFieldValue ( id: 1, client_field_key_id: 1, field_value: red )

ClientFieldValue ( id: 2, client_field_key_id: 1, field_value: blue )

ClientFieldValue ( id: 3, client_field_key_id: 2, field_value: hockey )

ClientFieldValue ( id: 4, client_field_key_id: 2, field_value: football )

ClientField ( id: 1, client_id: 1, client_field_value_id: 2 )

ClientField ( id: 2, client_id: 2, client_field_value_id: 1 )

ClientField ( id: 3, client_id: 3, client_field_value_id: 3 )

ClientField ( id: 4, client_id: 4, client_field_value_id: 4 )

Which would mean that user 1 created client 1 who likes blue and client 2 who likes red. User 2 created client 3 who likes hockey and client 4 likes football.

Would a model structure like this work in CakePHP? If so, how would I add the fields to the client add and edit forms dynamically and what should I call the tables?

Solution

I actually figured this out.. not sure if it's the best way but it works pretty well.

I created the following models:

User
Client
ClientFieldKey
ClientFieldValue

With the following relationships

User

hasMany: Client, ClientFieldKey

Client

hasMany: ClientFieldValue

belongsTo: User

ClientFieldKey

hasMany: ClientFieldValue

belongsTo: User

ClientFieldValue

belongsTo: Client, ClientFieldKey

Then the user could create the keys for the fields and in the ClientFieldKey afterSave() callback I create a new ClientFieldValue for every one of the users clients whenever a ClientFieldKey is created.

// /Model/ClientFieldKey.php
public function afterSave($created) {
    if($created):
      $clientFieldValue = ClassRegistry::init('ClientFieldValue');
      $users_clients = $this->find('all', array(
        'conditions'=>'ClientFieldKey.id = '.$this->data['ClientFieldKey']['id'], 
        'contain'=>array('User'=>array('Client'=>array('fields'=>'id')))
      ));
      foreach($users_clients[0]['User']['Client'] as $users_client):
        $clientFieldValue->create();
        $clientFieldValue->set('client_field_key_id', $this->data['ClientFieldKey']['id']);
        $clientFieldValue->set('client_id', $users_client['id']);
        $clientFieldValue->save();
      endforeach;
  endif;
}

and then set up the add/edit/view actions on my client

// /Controller/ClientsController.php
public function add() {
      ...
  $this->Client->saveAssociated($this->request->data);
      ...   
  $clientFieldKeys = $this->Client->User->find('all', array('contain' => array('ClientFieldKeys' => array('fields'=>'id,key')), 'conditions' => 'User.id = '.$this->Auth->User('id')));
  $this->set('clientFieldKeys', $clientFieldKeys[0]['ClientFieldKeys']);
}

public function edit($id = null) {
      ...
  $this->Client->saveAll($this->request->data);
      ...
  $options = array('conditions' => array('Client.' . $this->Client->primaryKey => $id), 'contain'=>array('ClientFieldValue'=>array('ClientFieldKey')));
  $this->request->data = $this->Client->find('first', $options);
}

public function view($id = null) {
      ...
  $client = $this->Client->find('first', array(
    'conditions' => array('Client.' . $this->Client->primaryKey => $id),
    'contain' => array('User', 'ClientFieldValue' => array('ClientFieldKey'))
  ));
  $this->set('client', $client);
}

// /View/Clients/add.ctp
<?php if($clientFieldKeys): ?>
  <h2>Custom Fields</h2>
  <?php
    $count=0;
    foreach($clientFieldKeys as $cfk):
      echo $this->Form->hidden('ClientFieldValue.'.$count.'.client_field_key_id', array('value'=>$cfk['id']));
      echo $this->Form->input('ClientFieldValue.'.$count.'.value', array('type'=>'text', 'label'=>$cfk['key']));
      $count++;
    endforeach;
  ?>
<?php endif; ?>

// /View/Clients/edit.ctp
<?php if($this->data['ClientFieldValue']): ?>
  <h2>Custom Fields</h2>
  <?php
    $count=0;
    foreach($this->data['ClientFieldValue'] as $cfv):
      echo $this->Form->hidden('ClientFieldValue.'.$count.'.id');
      echo $this->Form->input('ClientFieldValue.'.$count.'.value', array('type'=>'text','label'=>$cfv['ClientFieldKey']['key']));
      $count++;
    endforeach;
  ?>
<?php endif; ?>

// /View/Clients/view.ctp
<?php if($client['ClientFieldValue']): ?>
  <h2>Custom Fields</h2>
  <?php foreach($client['ClientFieldValue'] as $clientFieldValue): ?>
    <dt><?php echo $clientFieldValue['ClientFieldKey']['key']; ?></dt>
      <dd>
        <?php echo $clientFieldValue['value']; ?>
        &nbsp;
      </dd>
  <?php endforeach; ?>
<?php endif; ?>