Saving data into multiple tables from one form in CakePHP 2.5.1

Question

I am new in CakePHP, developing a cloud application using CakePHP 2.5.1 and MySQL. i have following tables:

users

  user_id ,
  people_id(FK- people.people_id),
  username,
  password

people

  people_id, 
  manager_id(FK- people.people_id), 
  firsr_name,
  last_name,

roles

  role_id ,
  people_id (FK- people.people_id),
  role

addresses

  address_id,
  people_id (FK- people.people_id),
  street,
  house no,
  post code,
  city

Foreign key people.manager_id refers to primary_key people.people_id. The field roles.role can hold either 'manager' or 'customer'.

Now, in my registration form, there will be following input fields:

-username
-password
-first name
-last name
-street
-house no
-post code
-city
-role     

I have used a User model (User.php), a registration form (add.ctp) and a controller (UsersController.php) to input username and password; and implemented login, logout.

The View add.ctp:

<div class="users form">
<?php echo $this->Form->create('User'); ?>
    <fieldset>
        <legend><?php echo __('Registrierung'); ?></legend>

        <?php echo $this->Form->input('username');

              echo $this->Form->input('password');
              echo $this->Form->input('password_confirm', array('label' => 'Confirm Password', 'maxLength' => 255, 'title' => 'Confirm password', 'type'=>'password'));


        );  
    ?>
    </fieldset>
<?php echo $this->Form->end(__('Register')); ?>
</div>

is working fine. Now I need to include above input fields in the registration form. This means creating models for each table and defining the associations like belogsTo, hasMany, etc. in my User model, I have defined the following association:

class User extends AppModel {

    public $belongsTo = array(
            'Person' => array(
                'className' => 'Person',
                'foreignKey' => 'people_id'

            )
    );
}

However, it throws the following error:

Database Error
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Person.id' in 'on clause' 

Could anyone tell me how to implement these associations? I could not find any example which matches with my requirements.

Answer 1

The MySQL error is being caused by the way you have named your primary keys. In CakePHP, primary keys should be named id.

If you wish to use a different name (not recommendable), you have to tell CakePHP what name you will be using. This is done declaring the $primaryKey property. Example:

class Person extends AppModel {

    public $primaryKey = 'people_id';

}

However, it's preferable that you rename your primary keys and adhere to CakePHP conventions as much as possible.

Also, by the way CakePHP treats plurals, your people_id fields should be renamed to person_id.

EDIT: Adding relationships

Your relationships could be as follows.

Your Person class:

class Person extends AppModel{

    public $belongsTo = array(
        'Manager' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );

    public $hasMany = array(
        'User',
        'Role',
        'Address',
        'Subordinate' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );
}

Your other classes:

class User extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

class Role extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

class Address extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

Remember to change all foreign keys from people_id to person_id.

---

Modification in model Role

I think you may want to move field roles.person_id to people.role_id. IMHO it makes more sense.

Your relationships would change to:

class Person extends AppModel{

    public $belongsTo = array(
        'Manager' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
        'Role'
    );

    public $hasMany = array(
        'User',
        'Address',
        'Subordinate' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );
}

and

class Role extends AppModel{

    public $hasMany = array(
        'Person'
    );
}

See

• Cookbook 2.x: Model and Database Conventions

Answer 2

You can explicitly define the primary key in each of the models, but that's make it a whole lot complicated.

I'd recommend you to stick to the conventions. Please change all the primary key fields to "id". That'd probably resolve a bulk of your problems.

Peace! xD