Crater ORM

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language

Crater ORM is based on Table Data Gateway pattern and Row Data Gateway pattern

So, we have two main entities:

• Table
• Row

The Table class is an object-oriented interface to database tables. It provides methods for many common operations on tables. The base class is extensible, so you can add custom logic.

Defining the Table

Declare the database table for which this class is defined, using the protected variable $_name. This is a string, and must contain the name of the table spelled as it appears in the database.

Example of declaring a table class:

namespace Models\Table;
use Core\Orm\TableGateway;

class User extends TableGateway
{
    protected $_name = 'user';
    protected $_primary = 'user_id';
    protected $_rowClass = 'Models\Row\User';
}

• $_name is the name of the table
• $_primary is the name of the primary column
• $_rowClass is the name of the row class

NOTE: all variables is required

Inserting Rows to a Table

You can use the Table object to insert rows into the database table on which the Table object is based. Use the insert() method of your Table object. The argument is an associative array, mapping column names to values.

$table = new \Models\Table\Bug();

$data = array(
    'created_on'      => '2015-03-22',
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW'
);

$table->insert($data);

Updating Rows in a Table

You can update rows in a database table using the update() method of a Table class. This method takes two arguments: an associative array of columns to change and new values to assign to these columns; and an SQL expression that is used in a WHERE clause, as criteria for the rows to change in the UPDATE operation.

$table = new \Models\Table\Bug();

$data = array(
    'updated_at'      => '2015-03-23',
    'bug_status'      => 'FIXED'
);

$where = array(
    'bug_id' => 23
);

$table->update($data, $where);

Deleting Rows from a Table

You can delete rows from a database table using the delete() method. This method takes one argument, which is an SQL expression that is used in a WHERE clause, as criteria for the rows to delete.

$table = new \Models\Table\Bug();

$where = array(
    'bug_id' => 21
);

$table->delete($where);

Finding Rows by Primary Key

You can query the database table for rows matching specific values in the primary key, using the find() method. The first argument of this method is a single value to match against the primary key of the table.

$table = new \Models\Table\Bug();
// Returns a Rowset
$rows = $table->find(12);

Querying for a set of Rows and for a single Row

You can use the following fetch methods: fetchRow() that return a row class and fetchAll() that return a collection of rows (rowset)

$table = new \Models\Table\Bug();

$where = array(
    'bug_status' => 'FIXED',
    'standard' => 2
);

$columns = array(
    'bug_status',
    'standard',
    'bug_id',
    'bug_code',
    'bug_description',
);

$order = array(
    'bug_code' => 'ASC'
);

$limit = 10;

$lazy = false;

$rows = $table->fetchAll($where, $columns, $order, $limit, $lazy);
// OR
$row = $table->fetchRow($where, $columns, $order);

If $lazy is true, fetchAll() will return an array. So, you can't use relationship. You can use $lazy = true if you expect to return a lot of data.

Define custom search methods in Table

You can implement custom query methods in your Table class, if you have frequent need to do queries against this table with specific criteria. Most queries can be written using fetchAll()

For example:

namespace Models\Table;
use Core\Orm\TableGateway;

class User extends TableGateway
{
    protected $_name = 'user';
    protected $_primary = 'user_id';
    protected $_rowClass = 'Models\Row\User';

    public function findByStatus($status)
    {
        return $this->fetchAll(array('status' => $status));
    }
}

And in your controller you will use:

$userTable = new \Models\Table\User();
$rows = $userTable->findByStatus("active");

Defining the Row

A Row Data Gateway gives you objects that look exactly like the record in your record structure but can be accessed with the regular mechanisms of your programming language. All details of data source access are hidden behind this interface.

You need to have a row class for each table.

A standard declaration we have below

namespace Models\Row;
use Core\Orm\RowGateway;

class User extends RowGateway
{

}

The return of $table->fetchRow() will be a row class instance. The return of $table->fetchAll() will be an array with several instances of row class

Fetching a Row

Example of fetching a row

$tableUser = new \Models\Table\User();

$row = $tableUser->fetchRow(
    array(
        'first_name' => 'Michael',
        'last_name' => 'Hermen'
    )
);

Changing column values in a Row

You can set individual column values using column accessors, similar to how the columns are read as object properties in the example above.

Using a column accessor to set a value changes the column value of the row object in your application, but it does not commit the change to the database yet. You can do that with the save() method.

$tableUser = new \Models\Table\User();

$row = $tableUser->fetchRow(
    array(
        'first_name' => 'Michael',
        'last_name' => 'Hermen'
    )
);

// Change the value of one or more columns
$row->status = 'AVAILABLE';

// UPDATE the row in the database with new values
$row->save();

Deleting a Row

You can call the delete() method on a Row object. This deletes rows in the database matching the primary key in the Row object.

$tableUser = new \Models\Table\User();

$row = $tableUser->fetchRow(
    array(
        'first_name' => 'Michael',
        'last_name' => 'Hermen'
    )
);

// DELETE this row
$row->delete();

Define custom methods in Row

You can implement custom methods in your Row class

namespace Models\Row;
use Core\Orm\RowGateway;

class User extends RowGateway
{
    public function combinedName()
    {
        return $this->last_name . ' ' . $this->first_name;
    }
}

You can call the combinedName() function as follows:

$tableUser = new \Models\Table\User();

$user = $tableUser->find(1);

echo $user->combinedName();

Table Relationships

Tables have relationships to each other in a relational database. An entity in one table can be linked to one or more entities in another table by using referential integrity constraints defined in the database schema.

Defining Relationships

Define classes for each of your tables, as described in this chapter. Also see this chapter for a description of the example database for which the following example code is designed.

Below are the PHP class definitions for these tables:

// `user` table declaration
class User extends TableGateway
{
    protected $_name = 'user';
    protected $_primary = 'user_id';
    protected $_rowClass = 'Models\Row\User';

    // Also you can have $_has_many which has the same way of declaration
    protected $_has_one = array(
        'UserInfo' => array(
            'refTableClass' => 'Models\Table\UserInfo',
            'fkColumn' => 'user_info_id'
        )
    );
}

// `user_info` table declaration
class UserInfo extends TableGateway
{
    protected $_name = 'user_info';
    protected $_primary = 'user_info_id';
    protected $_rowClass = 'Models\Row\UserInfo';

    protected $_belongs_to = array(
        'User' => array(
            'refTableClass' => 'Models\Table\User',
        )
    );
}

• refTableClass The class name of the parent table.
• fkColumn A string naming the foreign key column names in the dependent table.

How to use in controller:

$tableUser = new \Models\Table\User();

$user = $tableUser->find(1);
$userInfo = $user->UserInfo();

echo 'User city:' . $userInfo->city;

// Also, you can use the inversion method

$user = $userInfo->User();

↑