Laravel eloquent many to many to many model

Question

I'm doing a project in laravel 4, and I have come to a stop.

tasks table:

task_id (PK)
task_title

measurements table:

measure_id (PK)
measure_title

routines table:

routine_id (PK)
date
time
value
emp_id (FK)

emps table:

emp_id (PK)
first_name
last_name
user_name
email
user_type

Now what I am confused about is how I would model these relationships in eloquent, because I can't seem to figure it out. In the phpmyadmin DB, I currently have two tables connecting the tasks and measurements to routines which have task_routine : task_id (PK), routine_id (PK) and measure_routine : measure_id (PK), routine_id(PK).

Hope that was as clear as it sounded in my head when I wrote it, and thanks for your help!

Answer 1

At first in your all tables change the PK field from *_id to just id. If you want to make a relation for a table with another table then the convention is that, you should keep a foreign key in the child table using the parent table's name_id (name should be in singular form), for example, to build a relation between emps and routines table you should use emp_id foreign key in the routines table and your emps table should contain id PK. So, your emps table is parent table and the routines is the child of emps and in both tables records should be like this (custom convention could be used):

Table emps (parent):

id (pk) | first_name | more fields ...
    1   | Mr         | ...
    2   | Miss       | ...  
    3   | Mrs        | ...  

Table routines (child):

id (pk) | emp_id (FK) | more fields ...
    1   |    1        | ...            - Child of emps table's first record
    2   |    3        | ...            - Child of emps table's third record
    3   |    3        | ...            - Child of emps table's third record

Here, each id of emps table used as foreign key in the routines table in emp_id field. So, emps table has three related records in routines table and the first record in emps table relates to first record in the routines table and the third record in the emps table (with id 3) has two related records in routines table (second and third) because both records contains id 3 of emps table's third record in emp_id field.

Now building relation using Laravel:

If your parent table has only one related table in child table then use one-to-one, for example, if the record with id 1 (first record) in emps table has only one related record in routines table using emp_id field with value of 1 and if it can't be in more than one record in the routines table then it's one-to-one relation and in the example records given above is not one-to-one relationship because in the routines table there are two records with same id/pk of emps table available so it goes to one-to-many relationship, so it could be read as emps on record has many routines. To build the one-to-many relation we may use:

class Emp extends Eloquent {
    public function routines()
    {
        return $this->hasMany('Routine');
    }
}

The Routine model:

class Routine extends Eloquent {
    public function emp()
    {
        return $this->belongsTo('Emp');
    }
}

In one-to-many relationship a parent table may contain multiple child records with same id but if a record in child table has many parents as well then it should be a many-to-many relationship. For example, if we want to build a relation in routines table something like this (Not possible to use same primary key twice):

id (pk) | emp_id (FK) | more fields ...
    1   |    1        | ...            
    2   |    2        | ...            - Record 2(id) has parent 2(emp_id)
    2   |    3        | ...            - Record 2(id) has also parent 3(emp_id)

In this case this is a many-to-many relationship and it's not possible to use same id/PK twice in one table so we need to build the many-to-many relationship between emps table and routines table using a third table (known as pivot table) to maintain the many-to-many relationship. So, it'll look something like following table:

Table Name should be emp_routine but there is other way to use a different name but follow this convention.

emp_routine Pivot Table:

id (pk) | emp_id (id/PK of emps) | routine_id (id/PK of routines) | more fields ...
    1   |   1                    |    1                           | ...
    2   |   2                    |    1                           | ...
    3   |   1                    |    2                           | ...

Here, both parent and child has more than one related records in both tables and this pivot table maintains the relationship.

To build the relationship using Laravel we may use:

class Emp extends Eloquent {
    public function routines()
    {
        return $this->belongsToMany('Routine');
    }
}

The Routine model:

class Routine extends Eloquent {
    public function emp()
    {
        return $this->belongsToMany('Emp');
    }
}

In this case, we don't need a foreign key field (emp_id) in the routines table because we are using a pivot table to maintain the relationship but if it's there, it won't be problem.

It should be notice that whenever you insert/update any record in the parent table you also have to insert/update relational data in the pivot table as well and Laravel provides helpful methods for these, so check the manual (relationship).