Establishing relationships between tables inside the MY_Model in CodeIgniter (1): has_one and has_many relations

This tutorial turned into a Github repository.
Please give it a try at:
https://github.com/avenirer/CodeIgniter-MY_Model

Well… the previous tutorial (Revisiting MY_Model in CodeIgniter: Copying from Jamie Rumbelow and looking at Eloquent) was a reeeally long one, so I decided to create a new tutorial that will continue that one. This means that the current tutorial is still taking into consideration the Jamie Rumbelow’s base model, but also keeps looking at Laravel’s Eloquent.

Just like the previous one, this tutorial is still a work in progress created at 2015.02.18 and updated at 2015.02.19. It’s tested with CodeIgniter 3.

A database without relations wouldn’t be a database. So there has to be relations between tables. In a blog, the posts, must have an author (or more) and comments. You don’t keep all these relations inside one row in a single table. For this relationship you must have at least three (if not more), tables.

So why not try to make the life easier for the users of our MY_Model and allow them to get results from another table, all in one single chained query.

First, let’s see what relations can be established between tables:

One to One relationship is a very basic relation: for one row in a table there is another row in another table.

One to Many relationship tells us that for one record in a table there are many records in another table

Many to Many relationship can be define with the help of one intermediary table (a pivot table), so that one record in a table can have many records in another table, but also one record in the other table can have many records in the first table.

Has Many Through… is not really a relationship but “a link to a relationship”, best defined in the Laravel’s Eloquent documentation: ‘The “has many through” relation provides a convenient short-cut for accessing distant relations via an intermediate relation. For example, a Country model might have many Post through a User model.’

Now, that we know what the relationships are, let’s create methods that will allow us to work with them.

We will start with the simplest relationship…

One to One relationship

To define a one to one relationship between models, we would have to create a property inside the models that extend the MY_Model and should be accessible to the latter.

Let’s suppose a user has only one phone number which is defined in another table.

That means we have two models:

Now, we must define the relationship between the two models/tables. We will do it in the Eloquent style, but a bit changed… It will be a property that is an array and every element is either:

– a string which names the model that has the relationship with;

– or an array which mentions, besides the model name, a foreign key and the local key through which it establishes the relationship.

Let’s see how this works with a string:

Of course, the User_model can have an additional (or more) one to one relationship with another model:

The model can be in another directory in the models directory… $this->has_one[] = ‘othermodels/User_model’;

Now let’s see how this works with an array:

Now going to our MY_model, we take over these relations and create a single array with all the relations. First, lets define two properties before the constructor:

In the constructor we create the $relations array with all the relations that we receive from the models that extend MY_Model:

We will then create a with() method that will hold all the model’s relations. This method will receive one parameter, and depending on what relationships we want to display, those relationships would be separated by a pipe (just like in the set_rules() method of the form validation library). For example, if we want to show all users with their phone numbers we could do something like this:

As you can see, there is no need to also load the ‘phone_model’, as it will be loaded inside the MY_Model, Also, if we want to get the users with phones and postal address and there were this kind of relationship we would do the call like this:

The with() method for one to one relationship

As you can see, with() method will receive a string which is exploded into an array on the pipe, after that, the resulted array will be used be our method to see what the user requested, based on the relationships established in the constructor. Depending on that, the requests will be dispatched to private methods, in this particular case to the _has_one() method.

The _has_one() method

Considering that it is a one on one relationship there is no point to do separate select statements, so we will do a simple join inside the _has_one() method:

That is a lot of “computation”… To do the join we needed to retrieve the foreign table name, the foreign key, and the local key. We did a lot of code because we’ve allowed the user to define their own keys, but we’ve also let the user the liberty to only mention the model with which the current model has relations.

Now, you might ask “What if we have columns that have the same name on two or more tables?”.

For this you will have to use the fields() method inside the query chain. For example, you can write something like this:

And still… There is something fishy here… Can you tell me what?…

The concerns…

Well… the problem is this: What does a method that should return a simple join (the _has_one() method) have to do with establishing relationships between tables?

That part should be left to another method. So why not make a _set_relationships() private method that will take care of all relationships defined in the constructor, and this method be called when a with() method is called in the method chaining?

We will start by transforming the $relations public property into a private $_relationships property before the constructor, and delete the construction of the array in the constructor. This new private property will store the relationships between the model and other models/tables.

Now, we create the _set_relationships() method:

Now the private property will look somewhat like this:

Much nicer…

Re-constructing the with() method

Returning to our with() method, we will have to make some changes in there too. So, after we call _set_relationships();  we work with the requested “subselects”:

Re-constructing the _has_one() method

And now the _has_one() private method looks much cleaner:

But someone may ask: What if I want the results for the connected table to be in a separate array/object that can be attached to the result? For this, we really have to work harder…

The great re-construction

So why not pass to the with() an optional second parameter which can be a boolean value. If the second parameter is set to FALSE, it won’t make a new select and won’t pass the result to the final result, but simply do a join. If is set to TRUE (which will be the default), it will do a select for every single row. Let’s set a private property named $_separate_subqueries that we will define before the constructor:

Let’s change the with() so that it will take the second parameter into consideration. Also, we will need to append the subselects to our read methods. To do this we should first make sure that the $requested variable will be accessible to the MY_Model class. So let’s tranform it into a private one. Now, the with method will pass the aditional requested table data to this private property. Also, if the $_separate_subqueries is set to TRUE it will leave everything to a method that will be assigned to the the after_get trigger. This way, after the model gets the data this data will be passed iterated and appended the subquery result. So let’s see the with() method again:

We’ll now create a method called join_temporary_results() that will be added to the after_get trigger.

To make it easier for us, we will first make sure we always work with an array, even when passed one row, or passed objects

After this, for each $_requested we will interogate the associated tables, and pass everything to the received $data, either as object or array, depnding on the value of $return_as variable:

As you can see, we’ve done our best not do do a lot of selects… and I sure hope you understand what is going on here, because it took me two days to do this.

Now, this will work also with ‘has_many’ relationships. So let’s do this:

The $has_many property

This one is an easy one, because we only have to define it as property:

…and change the private method _set_relationships(), so that it will accept this kind of relationship too:

How will all this work? Well… Let’s say a user has written some posts. After we’ve created a table called posts and having a user_id as column to identify the users, we can create a model:

Now, we modify the constructor of the Users_model to accomodate the Posts_model:

Simple… Now, if in a controller we write something like this:

We will get, besides the obvious phone and address, another key, named posts that will show all the user’s posts:

How does the finished MY_Model looks like?

 

But wait… this is not over yet…

0 thoughts on “Establishing relationships between tables inside the MY_Model in CodeIgniter (1): has_one and has_many relations

  • nearly desperate searching tutorial for how working on establishing between tables.
    having look at several ORM like Propel, Datamapper, Doctrine, Gas ORM but none of them provide a satisfied tutorial.
    After several days doing painful searching on Google, it directs me again to this wonderful site.
    Sir, please, i think you need to add this (and other non listed ones) to the site menu.

  • i am copying your model on Github to webserver.
    route set to ‘test’ = done
    setting database.php = done
    migrating database = done
    and when i try to run it, an error appears, as below:
    ——————————————————————————————
    A PHP Error was encountered
    Severity: Notice
    Message: Undefined index: foreign_model
    Filename: core/MY_Model.php
    Line Number: 1247
    ——————————————————————————————

    where the code in line 1247 is = $foreign_model = $relation[‘foreign_model’];

    is there something that i missed?

  • Sir,
    I just wanting to make sure,
    is it true that on the Data tier (sql level), every created table has no relation at all to each other, but the relationship task is handled by Logic tier (CI model) ?

    • Well… actually you can at any time create relations between tables inside MySQL type of databases. These are actually called relational databases because the have relations. On the other hand there are also NoSQL type of databases (redis, mongodb) which are not relational databases (although they can emulate relations…)

  • My question is not for general cases, but specifically for your MY_Model repository on Github.
    As far as what I understand, from the SQL database structure (5 tables) created by your code, there is no relationship among those tables, but it is established by your Model. Is it true?

  • currently i am creating dummy application using your model by working with two tables that relates to each other (ONE TO ONE).
    Let’s say:
    1. table users (user_id , username)
    2. table groups(group_id, groupname)

    on every user creation, i wanna associate every user entry with one group (ex: user ‘Avenir’ is a member of group ‘Expert’).

    from what i have learned from your tutorial (about ION AUTH), we just pass our ‘groups’ table to register function
    $this->ion_auth->register($username, $group_ids); (a line taken your Users.php controller, where $username is belong to table ‘users’ and $group_ids is belong to table ‘groups’)
    On the Users.php controller, there is no a clear info about what is the Model used to handle communication with database ( I assume it is automatically taken care by ION AUTH Model).

    But from your MY_Model, my best understanding is everytime we wanna work with a tabel, we need to load its model.
    This is a part that i am still confused.
    My question is, based on this tutorial part (about MY_Model), how to work with creation (entry) user profile from table ‘users’ and also making relation with table ‘groups’ by adding group for this user?

    What i have done so far is:
    $data = array(‘username’ => ‘avenirer’);
    $this->user_model->insert($data);

    above lines only work with one table (‘users’).
    But i don’t know how to associate user ‘Avenir’ with group let’s say ‘Expert’, because ‘Expert’ is a data from other table (groups)

    From the pattern, what i supposed to do was by adding another parameter into:
    $data = array(‘username’ => ‘avenirer’,’groups’=>’Expert’);
    but this line is not working, i assume the parameter ‘groups’ is belong to another model (group_model).

    From your Github MY_Model repository, the available example for inserting data is just working with one table.

    I need your very help Sir, what should I do on insert data to tables that has relationship.?
    A small example would be fantastic if you do not mind.

    • Wooow… sorry. I totally forgot about this comment. When I created the User model, I did it in order to work with the users table without tempering with the Ion_auth. If I were to make changes to the Ion_auth, then any updates done to the library on Github wouldn’t be made inside my application unless I made the same changes after importing the library (which also has a model called Ion_auth, model that deals with more than one table: users, users_groups, groups). Usually when you work with the things that the library deals with (user authentication, registration, administration), you should use the methods that the library gives you. Only if you find yourself needing something new that can’t be handled by the library, you should use your own models. Hope I made myself understood.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

No spam? * Time limit is exhausted. Please reload CAPTCHA.