CODEWITHSUNDEEP
phpcakephpmodel
Alexandre
Alexandre

Reputation: 759

Manage multiple tables relationed in CakePHP

So, i have three tables

Table: Users

 _________________
|  id    |  user  |
-------------------
|   1    |  Roy   |
|   2    |  Ben   |
|________|________|

Table: Hability_lists // Where i set the list with all habilities available

 ___________________________________
|  id    |  category  | subcategory | 
------------------------------------
|   1    | Programmer |     PHP     |
|   2    | Programmer |     ASP     |
|________|____________|_____________|

Table: Habilities // Where i set the habilities from all users

 ________________________________________
|  id    |  user_id   | hability_list_id | 
-----------------------------------------
|   1    |     2      |         1        |
|   2    |     1      |         2        |
|________|____________|__________________|

By this, we can see that:

Roy are a ASP Programmer and Ben are a PHP Programmer

But, how to set relative models like this using CakePHP? I know how by using two models but not using three models.

There is some way to do this? Or maybe a better way to do?

Thanks in advance.

Upvotes: 0

Views: 176

Answers (2)

davidmh
davidmh

Reputation: 1378

When working with an MVC framework it's highly recommended to follow its conventions. So a few changes may be beneficial for you.

What you are looking for its the HABTM (Has And Belongs To Many) association between the "users" table and the "habilities" table *. I'm guessing, by the design of your table, that a user can have multiple habilities, otherwise you should check the hasMany association.

It should be something like this:

Table habilities: select * from habilities;

+----+------------+----------------------+----------+
| id | category   | subcategoy | created | modified |
+----+------------+------------+---------+----------+
|  1 | Programmer | ASP        |         |          |
|  2 | Programmer | PHP        |         |          |
|  3 | Musician   | Classical  |         |          |
+----+------------+------------+---------+----------+

Table users: select * from users;

+----+-------+---------------------+---------------------+
| id | name  | created             | modified            | **
+----+-------+---------------------+---------------------+
|  1 | Roy   | 2012-08-15 02:52:18 | 2013-01-17 03:25:28 |
|  2 | Ben   | 2012-11-10 03:36:12 | 2012-11-10 03:36:12 |
+----+-------+---------------------+---------------------+

Relational table for HABTM: select * from habilities_users;

+----+-------------+-------------------+----------+
| id | hability_id | user_id | created | modified |
+----+-------------+---------+---------+----------+
|  1 | 1           | 2       |         |          |
|  2 | 2           | 1       |         |          |
+----+-------------+---------+---------+----------+

Look the reference columns in habilities_users, they're singular with a _id suffix to work with CakePHP.

Defining the models classes it's also important, since it's where you define all their associations.

app/Model/User.php

<?php
class User extends AppModel {
  public $hasAndBelongsToMany = array('Hability');
}

app/Model/Hability.php

<?php
class Hability extends AppModel {
  public $hasAndBelongsToMany = array('User');
}

The table habilities_users doesn't need a model file, its behaviours and properties are implicit in the declaration of its associated models.

* using those names it's also the CakePHP way. [link]

** adding "created" and "modified" in each table will store those events for each record automatically.

Upvotes: 3

Dave
Dave

Reputation: 29141

You'll want to use HasAndBelongsToMany (HABTM).

This allows you to have two models - User and Hability that are joined by a "tweener" table.

Upvotes: 1

Related Questions