CakePHP access indirectly related model - beginner's question

Question

Hi everyone,

I am writing a CakePHP application to log the work I do for various clients, but after trying for days I seem unable to get it to do what I want. I have read most of the book CakePHP's website.

and googled for all I'm worth, so I presume I am missing something obvious!

Every 'log item' belongs to a 'sub-project, which in turn belongs to a 'project', which in turn belongs to a 'sub-client' which finally belongs to a client. These are the 5 MySQL tables I am using:

mysql> DESCRIBE log_items;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| date            | date         | NO   |     | NULL    |                |
| time            | time         | NO   |     | NULL    |                |
| time_spent      | int(11)      | NO   |     | NULL    |                |
| sub_projects_id | int(11)      | NO   | MUL | NULL    |                |
| title           | varchar(100) | NO   |     | NULL    |                |
| description     | text         | YES  |     | NULL    |                |
| created         | datetime     | YES  |     | NULL    |                |
| modified        | datetime     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+


mysql> DESCRIBE sub_projects;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| projects_id | int(11)      | NO   | MUL | NULL    |                |
| created     | datetime     | YES  |     | NULL    |                |
| modified    | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+


mysql> DESCRIBE projects;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(100) | NO   |     | NULL    |                |
| sub_clients_id | int(11)      | NO   | MUL | NULL    |                |
| created        | datetime     | YES  |     | NULL    |                |
| modified       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+


mysql> DESCRIBE sub_clients;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(100) | NO   |     | NULL    |                |
| clients_id | int(11)      | NO   | MUL | NULL    |                |
| created    | datetime     | YES  |     | NULL    |                |
| modified   | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+


mysql> DESCRIBE clients;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| created  | datetime     | YES  |     | NULL    |                |
| modified | datetime     | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

I have set up the following associations in CakePHP:

LogItem belongsTo SubProjects
SubProject belongsTo Projects
Project belongsTo SubClients
SubClient belongsTo Clients

Client hasMany SubClients
SubClient hasMany Projects
Project hasMany SubProjects
SubProject hasMany LogItems

Using 'cake bake' I have created the models, controllers (index, view add, edit and delete) and views, and things seem to function - as in I am able to perform simple CRUD operations successfully.

The Question

When editing a 'log item' at www.mydomain/log_items/edit I am presented with the view you would all suspect; namely the columns of the log_items table with the appropriate textfields/select boxes etc. I would also like to incorporate select boxes to choose the client, sub-client, project and sub-project in the 'log_items' edit view.

Ideally the 'sub-client' select box should populate itself depending upon the 'client' chosen, the 'project' select box should also populate itself depending on the 'sub-client' selected etc, etc.

I guess the way to go about populating the select boxes with relevant options is Ajax, but I am unsure of how to go about actually accessing a model from the child view of a indirectly related model, for example how to create a 'sub-client' select box in the 'log_items' edit view.

I have have found this example:

http://forum.phpsitesolutions.com/php-frameworks/cakephp/ajax-cakephp-dynamically-populate-html-select-dropdown-box-t29.html

where someone achieves something similar for US states, counties and cities. However, I noticed in the database schema - which is downloadable from the site above link - that the database tables don't have any foreign keys, so now I'm wondering if I'm going about things in the correct manner.

Any pointers and advice would be very much appreciated.

Kind regards,

Chris

Answer 1

Your foreign key names should be singular. So projects_id should be project_id and sub_projects_id should be sub_project_id and so forth. If you are using cake bake or scaffolding, you should not be able to edit associated data in each model edit page. As a side note, make sure all you created model classes are singular (in the /models/folder).

To edit multiple levels of association, it maybe as easy as setting the $recursive class member to 2 in each of the models where you want the edit page to have multiple levels of association. Let me know how this works out for you.

In response to your second issue.

Make sure your models have all the proper association. If you baked them they should include them but given your error it looks like they somehow weren't included. So in log_item.php you should have something like

var $belongsTo = array('SubProject');

Answer 2

Many thanks for the reply.

I've changed all my foreign key names to be singular, and have ensured my models are also singular; for example the sub_clients table has a model called SubClient and resides in /models/sub_project.php.

I have added the line 'var $recursive = 2;' to each of my models and rebuilt all my controllers and views using 'cake bake' so as to ensure I caught every instance of the new singular foreign key names, and also to account for the addition of the $recursive variable.

I am able to view each model's index page (eg /log_items), but find I get the following error when attempting to edit a particular model entry (eg /log_items/edit/1):

Notice (8): Undefined property: LogItem::$SubProject [APP/controllers/log_items_controller.php, line 50]

Fatal error: Call to a member function find() on a non-object in /Library/WebServer/Documents/worklog.homeln/cake_1_2/app/controllers/log_items_controller.php on line 50

The offending line 50 is:

50: subProjects = $this->LogItem->SubProject->find('list');
51: $this->set(compact('subProjects'));

Again, any pointers would be very much appreciated, and once again many thanks for taking the time to reply.

Chris