Lecture 1.8: Database, Models and Relationships

Database

This framework is agnostic and does not engage in creating databases and database tables. The database for your application should be created using the language of your choice. This framework uses a PDO adapter to interface with your database tables. We recommend using MySQL given its tight integration with PHP but other databases can be used at your own discretion.

Activating Databases

In order to start using the database, you need to to activate it. The .env file is where the database credentials need to be entered. Note that the DB_ACTIVATE variable in the .env file needs to be set to true to activate the connection.

DB_ACTIVATE=true
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your-db-name
DB_USERNAME=your-db-username
DB_PASSWORD=your-db-password

Relational Databases

Creating databases and database tables is achieved through the database language that you normally use. This framework does not dictate how your database should be created. The examples in this documentation are all using MySQL. MySQL is the SQL language that we recommend using.

• Your database needs to be created with all its tables before creating the Models in this framework:

/* Example of a MySQL script without relationships */

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

• The One-to-One and One-to-Many relationships need to have the appropriate foreign keys created in the database:

/* Example of mySQL script with relationships */

CREATE TABLE IF NOT EXISTS `books` (
    `id` INT(25) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `author` varchar(255) NOT NULL,
    `publisher_id` INT(25) NOT NULL, 
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`), 
    CONSTRAINT fk1 FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`id`) ON UPDATE CASCADE ON DELETE 
    CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

• Many-to-Many relationships that require creating a pivot table in relational databases DO NOT need to be created in the database. The framework will automatically do that upon the definition of the relationship in the model.
  
  

Learn more about creating MySQL relational databases here

Models

The Model is the entity that interfaces directly with the database tables. Every table from your database that represents an object or an entity in your application that a user could act on needs to have a Model in the framework. For example, the table books needs to be represented by a Book Model in the framework. This framework dictates some conventions around the naming of the primary and foreign keys.

Learn more about naming conventions here

• All the models that you create should be in the /application/models folder. In this folder, you can organize the models to your convenience. If you prefer to have your models in a different folder, you can always do that for as long as it is within the /application folder.
• There is no formal naming convention for the Model. We do recommend that you remain consistent. If you choose to name the models in singular or plural then make sure to do that for all your models.
• All the models that you create should extend the Model class

use Caligrafy\Model;

class Book extends Model {
    // your code goes here
}

• The models that you create need to have all the equivalent table attributes represented as properties

use Caligrafy\Model;

class Book extends Model {
    // properties
    public $id;
    public $title;
    public $author;
    public $publisher_id;
    public $created_at;
}

Interfacing with the Model

The Model class exists in the context of a Controller. In other words, you can interface with a model from within a Controller.

Instantiating a Model

In order to instantiate the model that was created in the previous section, it needs to be done from the controller by associating the controller with the model and its respective table.

/* Notice that this needs to be done in the Controller */

use Caligrafy\Controller;

class BookController extends Controller {
   public function index()
   {
      $this->associate('Book', 'books'); // associate the controller with a model and a table 

      // the controller could act on the model using the model property
      dump($this->model);

   }
}

Model Methods

There are several built-in methods and properties that can be used from the moment you instantiate a model

/* Associations */
public $table; // retrieves the table name associated with the model
public function associate($table); // returns the Model with a database table associated to it

/* CRUD methods */
public function all($args = null); // Returns all unless an array of condition, order and limit are specified to complete the query
public function find($id); // returns a Model of the specific entry if available
public function search(String $scope, Array $keywords); // searches a table column (scope) for a collection of keywords (array)
public function create($input); // takes an array of values or an object and adds an entry to the respective table in the database
public function update($inputs, $id); // updates an entry in the database with the new values from array or Model
public function delete($id); // deletes a specific entry from the database
public function save(); // saves a the model into the database. If the model has an id defined then it will update otherwise it will create a new entry in the table

/* Relationships definition */

// One-to-One relationship 
public function hasOne($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// One-to-Many relationship
public function hasMany($modelName, $joinedTableName, $foreignKey = null, $localKey = null)
public function belongsTo($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// Many-to-Many relationship in both directions
public function belongsToMany($modelName, $joinedTableName, $foreignKeyLocal = null, $foreignKeyJoin = null)

In relational databases, tables are related to one another. There are several types of relationships that can be defined. This framework makes it easy to define and use relationships without having to write extensive code. There are 3 types of relationships defined in this framework: One-to-One, One-to-Many and Many-to-Many.

Relationships

In this video, we will learn how to define one-to-one, one-to-many and many-to-many relationships among models in Caligrafy.

One-To-One Relationship

A One-to-One relationship is the simplest relationship between two entities. For example, we can have an authorization module where every User has one Account and reciprocally every Account has one User. This is a typical One-to-One relationship.

Declaration

In order to create such a relationship, you first need to make sure that the database tables that you create for the two entities both have foreign keys that link to one another.

Learn more about Relational Databases here

Once your database tables are created, one of the models User, for example, will add a new method that invokes the hasOne() method to define that relationship.

use Caligrafy\Model;

class User extends Model {

    // properties go here
    //...

    //new method that defines a One-to-One relationship
    public function account()
    {
       return $this->hasOne('Account','accounts');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Inverse Relationship

In the Account model, you will need to do the same using the hasOne method.

use Caligrafy\Model;

class Account extends Model {

    // properties go here
    //...

    //new method that defines the inverse of a One-to-One relationship
    public function user()
    {
       return $this->hasOne('User','users');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Notice: In both the examples, there is no restriction as to the name of the method used to define the relationships. We recommend using a name that is representative enough of the type of relationship.

Usage

After the relationship has been defined in the respective models, making use of that relationship takes place in the context of the Controller. For example, in order to get the Account associated with a User, you would do the following in the UserController:

use Caligrafy\Controller;

class UserController extends Controller {
    
    public function getAccount() 
    {
        $this->associate('User', 'users'); // create the association
        $account = $this->find(1)->account(); // returns account for user with id 1
    }
}

One-To-Many Relationship

A One-to-Many relationship is a common type of relationship between two entities. Taking the same example as before, you could allow one User to have multiple Account but an Account to belong to one user. For that you would want to use a One-to-Many relationship.

Declaration

In order to create such a relationship, you first need to make sure that the entity that could exist many times has a reference in the table of the other entity. In our example, that means that there should be a foreign_key in the accounts table that references the users table.

Learn more about Relational Databases here

Once your database tables are created, the User model needs a new method that invokes the hasMany() method to define that relationship.

use Caligrafy\Model;

class User extends Model {

    // properties go here
    //...

    //new method that defines a One-to-Many relationship
    public function accounts()
    {
       return $this->hasMany('Account','accounts');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Inverse Relationship

In the Account model, the account belongs to one User. You will need to define a belongsTo or the hasOne relationship.

use Caligrafy\Model;

class Account extends Model {

    // properties go here
    //...

    //new method that defines the One to relationship
    public function user()
    {
       return $this->hasOne('User','users');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Notice: In both the examples, there is no restriction as to the name of the method used to define the relationships. We recommend using a name that is representative enough of the type of relationship.

Usage

After the relationship has been defined in the respective models, making use of that relationship takes place in the context of the Controller. For example, in order to get all the accounts associated with a User, you would do the following in the UserController:

use Caligrafy\Controller;

class UserController extends Controller {

    public function getAccounts() 
    {
        $this->associate('User', 'users'); // create the association
        $accounts = $this->find(1)->accounts(); // returns accounts for user with id 1
    }
}

Many-to-Many Relationship

A Many-to-Many relationship ensures that two entities could have multiple instances of each other. Taking the same example as before, you could allow one User to have multiple Account and one Account to have multiple User. For that you would want to use a Many-to-Many relationship.

Declaration

In order to create such a relationship, you do not need to do anything in the database other than making sure to follow the naming conventions.

Learn more about Naming Conventions here

The User model needs a new method that invokes the belongsToMany() method to define that relationship.

use Caligrafy\Model;

class User extends Model {

    // properties go here
    //...

    //new method that defines a Many-to-Many relationship
    public function accounts()
    {
       return $this->belongsToMany('Account','accounts');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Inverse Relationship

Similarly, the Account model needs to define a belongsToMany relationship.

use Caligrafy\Model;

class Account extends Model {

    // properties go here
    //...

    //new method that defines a Many-to-Many relationship
    public function users()
    {
       return $this->belongsToMany('User','users');
       // the first argument is the model name to link to
       // the second argument is the database table name of to link to

    }
}

Notice: In both the examples, there is no restriction as to the name of the method used to define the relationships. We recommend using a name that is representative enough of the type of relationship.

Usage

After the relationship has been defined in the respective models, making use of that relationship takes place in the context of the Controller. For example, in order to get all the accounts associated with a User, you would do the following in the UserController:

use Caligrafy\Controller;

class UserController extends Controller {

    public function getAccounts() 
    {
        $this->associate('User', 'users'); // create the association
        $user = $this->find(1);
        $accounts = $user->accounts(); // returns all accounts for user with id 1

        // Alternatively, you can use the pivot property to access all the pivots associated with a user
        $accounts = $user->pivot->accounts->all; // returns all accounts for user with id 1
    }
}

Overriding Naming Convention

Relationships work under the premise that all tables have and 'id' primary key. This convention cannot be overridden. Also, the relationship methods assume that all foreign keys have the format modelname_id with the model name being in small caps. Taking the User and Account example again, the foreign keys are by default assumed to be user_id (in the accounts table) and account_id (in the users table).

This framework provides the flexibility to override the foreign keys naming.

// One-to-One relationship 
public function hasOne($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// One-to-Many relationship
public function hasMany($modelName, $joinedTableName, $foreignKey = null, $localKey = null)
public function belongsTo($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// Many-to-Many relationship in both directions
public function belongsToMany($modelName, $joinedTableName, $foreignKeyLocal = null, $foreignKeyJoin = null)

Model Methods

The core Model class comes prepackaged with a set of properties and methods that help establish the relationships and interfacing with joined tables.

// One-to-One relationship 
public function hasOne($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// One-to-Many relationship
public function hasMany($modelName, $joinedTableName, $foreignKey = null, $localKey = null)
public function belongsTo($modelName, $joinedTableName, $foreignKey = null, $localKey = null)

// Many-to-Many relationship in both directions
public function belongsToMany($modelName, $joinedTableName, $foreignKeyLocal = null, $foreignKeyJoin = null)
public function attach($joinedTableName, $id) // attaches an ID to a joined many-to-many table
public function detach($joinedTableName, $id) // detaches and ID from a joined many-to-many table