4 min read

Database relationship is hard to maintain even for a mid-sized PHP/MySQL application, particularly, when multiple levels of relationships are involved because complicated SQL queries are needed. CakePHP offers a simple yet powerful feature called ‘object relational mapping’ or ORM to handle database relationships with ease.In CakePHP, relations between the database tables are defined through association—a way to represent the database table relationship inside CakePHP. Once the associations are defined in models according to the table relationships, we are ready to use its wonderful functionalities. Using CakePHP’s ORM, we can save, retrieve, and delete related data into and from different database tables with simplicity, in a better way—no need to write complex SQL queries with multiple JOINs anymore!

In this article by Ahsanul Bari and Anupom Syam, we will have a deep look at various types of associations and their uses. In particular, the purpose of this article is to learn:

  • How to figure out association types from database table relations
  • How to define different types of associations in CakePHP models
  • How to utilize the association for fetching related model data
  • How to relate associated data while saving

There are basically 3 types of relationship that can take place between database tables:

  • one-to-one
  • one-to-many
  • many-to-many

The first two of them are simple as they don’t require any additional table to relate the tables in relationship. In this article, we will first see how to define associations in models for one-to-one and one-to-many relations. Then we will look at how to retrieve and delete related data from, and save data into, database tables using model associations for these simple associations.

Defining One-To-Many Relationship in Models

To see how to define a one-to-many relationship in models, we will think of a situation where we need to store information about some authors and their books and the relation between authors and books is one-to-many. This means an author can have multiple books but a book belongs to only one author (which is rather absurd, as in real life scenario a book can also have multiple authors). We are now going to define associations in models for this one-to-many relation, so that our models recognize their relations and can deal with them accordingly.

Time for Action: Defining One-To-Many Relation

  1. Create a new database and put a fresh copy of CakePHP inside the web root. Name the database whatever you like but rename the cake folder to relationship. Configure the database in the new Cake installation.
  2. Execute the following SQL statements in the database to create a table named authors,
    CREATE TABLE `authors` (
    `id` int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` varchar( 127 ) NOT NULL ,
    `email` varchar( 127 ) NOT NULL ,
    `website` varchar( 127 ) NOT NULL
    );
  3. Create a books table in our database by executing the following SQL commands:
    CREATE TABLE `books` (
    `id` int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `isbn` varchar( 13 ) NOT NULL ,
    `title` varchar( 64 ) NOT NULL ,
    `description` text NOT NULL ,
    `author_id` int( 11 ) NOT NULL
    )
  4. Create the Author model using the following code (/app/models/authors.php):
    <?php
    class Author extends AppModel
    {
    var $name = 'Author';
    var $hasMany = 'Book';
    } ?>
  5. Use the following code to create the Book model (/app/models/books.php):
    <?php
    class Book extends AppModel
    { var $name = 'Book';
    var $belongsTo = 'Author';
    }
    ?>
  6. Create a controller for the Author model with the following code: (/app/controllers/authors_controller.php):
    <?php
    class AuthorsController extends AppController {
    var $name = 'Authors';
    var $scaffold;
    }
    ?>  
  7. Use the following code to create a controller for the Book model (/app/controllers/books_controller.php):
    <?php
    class BooksController extends AppController {
       var $name = 'Books';
       var $scaffold;
    }
    ?>
  8. Now, go to the following URLs and add some test data:
    http://localhost/relationship/authors/ and
    http://localhost/relationship/books/

What Just Happened?

We have created two tables: authors and books for storing author and book information.

CakePHP Application Development

A foreign-key named author_id is added to the books table to establish the one-to-many relation between authors and books. Through this foreign-key, an author is related to multiple books, as well as, a book is related to one single author.

By Cake convention, the name of a foreign-key should be underscored, singular name of target model, suffixed with _id.

Once the database tables are created and relations are established between them, we can define associations in models. In both of the model classes, Author and Book, we defined associations to represent the one-to-many relationship between the corresponding two tables. CakePHP provides two types of association: hasMany and belongsTo to define one-to-many relations in models.

These associations are very appropriately named:

  • As an author ‘has many’ books, Author model should have hasMany association to represent its relation with the Book model.
  • As a book ‘belongs to’ one author, Book model should have belongsTo association to denote its relation with the Author model.

In the Author model, an association attribute $hasMany is defined with the value Book to inform the model that every author can be related to many books. We also added a $belongsTo attribute in the Book model and set its value to Author to let the Book model know that every book is related to only one author.

After defining the associations, two controllers were created for both of these models with scaffolding to see how the associations are working.

CakePHP Application Development

LEAVE A REPLY

Please enter your comment!
Please enter your name here