Documentation

Active Record

# Introduction
# Create
# Read
# Update
# Delete
# Using Where Clause
# Aggregate Functions
# Joins
# Using Alias
# Collections
# Dynamic Finders
# Skip Columns
# Relations
# Model Events
# Transactions

Introduction

Cygnite provides a simple and beautiful Cyrus ORM, which implements Active Record pattern. Each database table has "Model" class which is used to interact with database table. Model allow you to select, insert a new record or update into the table.

Before digging into Active Record, make sure to configure a database connection in src/Apps/Configs/database.php file. For more information on database configuration read documentation.

Creating Cyrus Model

To get started, you should create a Cyrus model class. Typically model classes are located in src/Apps/Models/ and autoloaded by composer, but you are allowed to place anywhere. Every model class must extends Cygnite\Database\Cyrus\ActiveRecord class.

You may simply create a model class using cygnite console command as below.


    php cygnite model:create your_model_name your_database_name

Cyrus Model Conventions

Let's have a look at example model class, will use to retrieve and save information into database.


    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class User extends ActiveRecord
    {
        //
    }

Model And Table Names

We believe in convention over configuration philosophy, which is easier for developer. Every model class name must be "snake case", it act as table name. For example if your table name is "user_info" then the model class name must be "UserInfo". You may also override table name using "$tableName" property. In the above example, Cyrus model will assume the "User" model stores records into the table "user".


    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class User extends ActiveRecord
    {
        // explicit table name since our table is not "user"
        protected $tableName = 'user_info';
    }

Primary Key

Cyrus active record assume that your table has primary key column named as "id". However you can override the convention by simply defining "$primaryKey" property.


    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class User extends ActiveRecord
    {
       // explicit pk since our pk is not "id"
       protected $primaryKey = 'user_id';
    }

You need to specify the database connection name for the model using the model property as "$database".


    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class User extends ActiveRecord
    {
        // your database connection name
        protected $database = 'cyrus';
    }

Basic CRUD

CRUD Defined By Wikipedia:

CRUD application is the day to day task for every developers. Writing queries and running against the database may be painful at some point of time for developers. Cygnite provides you convenient way of running database queries using Cyrus Active Record. Cygnite makes your Create, Read, Write, Delete operations extremely simple and expressive.

Creating A Record Into Database

Cyrus Model class act as a database table. You may save records into a database table using model object. Here we register a new user for our blog by simply instantiating an object of User model and finally save records into a table.


    use Apps\Models\User;

    $user = new User();
    $user->name = 'Sanjoy Dey';
    $user->email = 'dey.sanjoy0@gmail.com';
    $user->save();

The above code snippet is equivalent of writing.


    #sql => INSERT INTO `users` (name, email) VALUES('Sanjoy Dey', 'sanjoy09@hotmail.com');

The recommended auto incremented primary key field name as 'id', however you can name anything as your wish. You may get the last inserted id using model object "$user->id;";

Setting Attributes To Model

You may also set attributes directly into the cyrus model using setAttributes method.


    use Apps\Models\User;

    $user = new User();
    $data = $input->except('btnSubmit')->post();
    $user->setAttributes($data);
    $user->save();

Getting Last Inserted Id


    echo $user->getId();

Retrieving Multiple Model Records

Once you have created database table, associated model class you are ready to retrieve records. Retrieving rows from a database table is just easier using cyrus model class. Cyrus model allows to use powerful query builder to fetch records.

Let us retrieve all model records.


namespace Apps\Controllers;

use Apps\Models\User;
use Cygnite\Mvc\View\View;
use Cygnite\Foundation\Http\Response;
use Cygnite\Mvc\Controller\AbstractBaseController;

class UserController extends AbstractBaseController
{
    // Plain layout
    protected $layout = 'layouts.base';

    public function __construct()
    {
        parent::__construct();
    }

    public function indexAction()
    {
        $users = User::all();
        $content = View::create('Apps.Views.user.index', ['users' => $users]);

        return Response::make($content);
    }
}

Adding Constraints

In some cases you may wish to add some constraints to your query, instead fetching all records from model. Since cyrus model class make use of query builder, you may also add constraints to your queries as below and finally use findMany() method to retrieve.


    Product::where('id', '=','2')
            ->orderBy('id', 'DESC')
            ->findMany();

You can access column value from cyrus model instance by accessing its property as below.


    $products = Product::where('id', '=','2')
                       ->orderBy('id', 'DESC')
                       ->findMany();

    foreach ($products as $product) {
        echo $product->name;
    }

Collections

The Cyrus findAll() and findMany() method always returns an instance of Cygnite\Foundation\Collection. The Collection class provides various methods to work with result set. Cyrus collection provides you flexibility to modify data as Array, Json, ArrayIterator. Also Collection makes easier to get the count, serialize, unserialize data on the fly. You may also call model functions over the retrieved result set.


    $data->asArray();
    //or
    $data->asJson();
    //or

    $data->count();
    //or

    $data->getIterator();
    //or

    $data->serialize();
    //or
    $data->unserialize();

You may find detailed guide in Collections documentation.

Retrieving Single Model Record

Some case you may like to retrieve single record from the model. You may use find() or findOne() method to retrieve single record, which will return you single model instance instead collections instance.


    //Retrieve using primary key
   $product = Product::find('2');

   $product = Product::where('id', '=','2')->findOne();

You may use various finders methods to retrieve records from the model.

Cyrus: Relationships (Associations)

Introduction

Your database tables may be often related to one another. Cyrus provides you simple api for working with different types relationships (associations) between models as below.

One To One
One To Many
Many To Many
Has Many Through etc.

One To One (Has One)

One-To-One relationship is very basic relation between two model. One-to-one relationship implemented using hasOne() method. For example, each user may have a single profile, so user table associated with profile table. To define relationship, we will use profile() method on User model and it will return the results of hasOne() method which is the instance of collection class. Let us have a look at below example.


    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class User extends ActiveRecord
    {
        //your database connection name
        protected $database = 'cygnite';

        protected $primaryKey = 'id';

        public function __construct()
        {
            parent::__construct();
        }

        /**
        * Get the profile record associated with the user.
        */
        public function profile()
        {
            return $this->hasOne('Apps\Models\Profile');
        }
    }

The first argument passed to hasOne() method must be associated model name. Once you have defined relationships between models you can retrieve results as below.


    //Find user based on user id
    $user = User::find('1');

    // Find the profile associated with the user
    $profile = $user->profile()->findOne();

    show($profile);

By default, Cyrus model assumes the foreign key column name based on the base model name. In the above example the Profile model assume to have a user_id as foreign key. You may override the convention by simply passing second argument to hasOne() method.


    return $this->hasOne('Apps\Models\Profile', 'foreign_key_id');

In addition, Cyrus model assumes that the foreign key column in the base table is the primary key column of the base table and should have matching value in id column of the parent. To override you may pass the third argument to your hasOne() method, passing the name of the column to use as the foreign key column in the base model.


    return $this->hasOne('Apps\Models\Profile', 'foreign_key_id', 'base_key_id');

One To Many (Has Many)

Cyrus ORM implemented One to Many relationship using hasMany() method. For example single user can have multiple facebook accounts. This relationship built on model class as below.


 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class Account extends ActiveRecord
 {
    //your database connection name
    protected $database = 'cygnite';

    protected $primaryKey = 'id';

 }


 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class User extends ActiveRecord
 {
    //your database connection name
    protected $database = 'cygnite';

    protected $primaryKey = 'id';


    public function accounts()
    {
        return $this->hasMany('\Apps\Models\Account');
    }
 }

Here accounts() method will return collection object. Remember Cyrus orm will assume the foreign key on the Account model is user_id. Once you have defined the relationship correctly you can access collection data using accounts() method.



  $user = \Apps\User\User::find(1);

  // Find the account associated with the user
  $accounts = $user->accounts()->findMany();

  foreach ($accounts as $account) {
    //...
     
  }

You may also override the foreign and base keys by passing additional arguments to the hasMany method like hasOne() method.



return $this->hasMany('\Apps\Models\Account', 'foreign_key_id');

return $this->hasMany('\Apps\Models\Account', 'foreign_key', 'base_key_id');

Belongs To (Inverse of the relationship)

To define a inverse of hasMany relationship we can use belongsTo method on child model. This assumes the foreign key is on the current table, not the related table.


 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class Profile extends ActiveRecord
 {
    public function user()
    {
        return $this->belongsTo('\Apps\Models\User');
    }
 }

 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class User extends ActiveRecord
 {
  
 }

This method works as follows:



  // Fetch a single profile from the database
  $profile = \Apps\Models\Profile::find($id);

  // Find the user associated with the profile
  $user = $profile->user()->findOne();

Has many through (Many To Many)

Many to many relationships are implemented using hasManyThrough() method. This method needs only one argument that is the name of the related model. However you can pass further arguments to override default behavior of the method.



 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class Country extends ActiveRecord
 {
    /**
     * Get all of the posts for the country.
     */
    public function posts()
    {
        return $this->hasManyThrough('Apps\Models\Post', 'Apps\Models\User');
    }
 }

 namespace Apps\Models;

 use Cygnite\Database\Cyrus\ActiveRecord;

 class Country extends ActiveRecord
 {
    public function posts()
    {
        return $this->hasManyThrough(
            'Apps\Models\Post', 'Apps\Models\User',
            'country_id', 'user_id', 'id'
        );
    }
 }

The first argument passed to the hasManyThrough() method is the name of the related model we wish to retrieve, it is mandatory. The second argument is the name of the intermediate model, it is optional. The third argument is the name of the foreign key on the intermediate model. Meaning that key to base model which is on intermediate model, it is optional. The fourth argument is the key to associated table on intermediate table - this is optional, and defaults to the name of the associated table with _id appended. The fifth argument is the base key, it is optional and defaults to the name of the primary key column. And the sixth argument is the foreign key column in the associated table - this is optional.

Filter Method

Cyrus model also allows you to write custom callback filter methods to hook into the life cycle of an QueryBuilder object. Meaning that filter method allows you to chained query builder methods and filter collection data.



    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class Book extends ActiveRecord
    {
        //your database connection name
        protected $database = 'cygnite';

        protected $primaryKey = 'id';

        public function __construct()
        {
            parent::__construct();
        }

        
        public function applyTax($query) 
        { 
          return $query->where('tax', '=', '10%'); 
        }

    }

    $book->filter('applyTax')->findMany();

You can also pass arguments to your custom filters. Example below.



    namespace Apps\Models;

    use Cygnite\Database\Cyrus\ActiveRecord;

    class Product extends ActiveRecord
    {
        //your database connection name
        protected $database = 'cygnite';

        protected $primaryKey = 'id';

        public function __construct()
        {
            parent::__construct();
        }

        
        public function applyTax($query, $column, $value) 
        { 
          return $query->where($column, '=', $value); 
        }

    }

    $product = new \Apps\Models\Product();
    $product->filter('applyTax', 'tax', '10%')->findAll();

Skipping Columns

Cygnite Active Record also allow you to fetch data from database except columns. You just need to define a function into your model class as below.



    public function skip()
    {
        return ['created_at', 'updated_at'];
    }

Dynamic Finders

Cygnite actively follows ruby on rails types of Active Record pattern and its dynamic finders made it more expressive. Dynamic finders are fully model based, these provides quick and easy way to access your data from table. Read More: Dynamic Finders

Raw Expressions And Queries

Occasionally, though hopefully rarely, you may need to do specify some queries by hand or Select expressions. Query builder also allow you to write raw queries against your database connection. You can use selectExpr(), rawJoin() etc. method for raw expressions. You can also check Raw Queries for running raw queries.

Model Based Events: Hooks

The following events are available, just define the method of the same name in the model that you want to use them:

- beforeCreate
- afterCreate
- beforeUpdate
- afterUpdate
- beforeSelect
- afterSelect
- beforeDelete
- afterDelete



  namespace Apps\Models;

  use Cygnite\Database\Cyrus\ActiveRecord;

  class User extends ActiveRecord
  {

    protected $database = 'cygnite';

    protected $primaryKey = 'id';

    public function __construct()
    {
        parent::__construct();
    }

    public function beforeCreate()
    {
        //....
    }

    public function afterCreate()
    {
        //....
    }
  }

Result-Set Types

By default Cygnite Active Record will return you the results in the form of Collection object of your model class. You are also allowed to modify results as group, both, assoc, object, column, class etc. You can make use of findAll() method by passing parameter into it to modify result set as below. let

For example



    // get the result set as json collection

    $user->select('*')->findAll('object');

Updating Record In A Table

We access model instance to update the records in a table. It's very simple than you think. It works similar as INSERT/CREATE works.



    use Apps\Models\User;

    $user = User::find(20);

    $user->first_name = 'Shane';
    $user->last_name = 'Watson';
    $user->email_address = 'shanewatson@gmail.com';
    $user->gender = 'Male';
    $user->save();

You may get the last id form the user object: echo $user->id; //20

Deleting Records

Deleting a record from the Table



    use Apps\Models\User;

    $user = new User();
    $user->trash(23);

Deleting multiple records from the Table



    $user->trash([21,22,23,34], true);

Accessing PDO methods Statically

Cygnite model provide you flexibility to access all PDO method statically. So you can use the power of PDO using model class.
For example-



    PDO::prepare() can be called as YourModel::prepare($sql);
    PDO::query() can be called as YourModel::query($sql);
    PDO::beginTransaction() etc.

Using Transactions

Cygnite Framework database manipulation is completely model based. You can use transaction with simple syntax using your model.



    use Apps\Models\User;

    User::beginTransaction();

    try {

        $user = new User();
        $user->name = 'Clinton';
        $user->email = 'oscar@gmail.com';
        $user->save();

        User::commit();

    } catch (Exception $e) {

       User::rollback();
       User::close();
       throw $e;
    }

You may also have a look at Cygnite Finders for retrieval of records from database.

Menus

Cygnite - A Modern Toolkit For Web Developers

The Elegant Way of Building Full-Featured Web Applications

Simple & Clean Framework

Active Record

Documentation

Active Record

Introduction

Creating Cyrus Model

Cyrus Model Conventions

Model And Table Names

Primary Key

Basic CRUD

Creating A Record Into Database

Setting Attributes To Model

Getting Last Inserted Id

Retrieving Multiple Model Records

Adding Constraints

Collections

Collections

Retrieving Single Model Record

Cyrus: Relationships (Associations)

Introduction

One To One (Has One)

One To Many (Has Many)

Belongs To (Inverse of the relationship)

Has many through (Many To Many)

Filter Method

Skipping Columns

Dynamic Finders

Raw Expressions And Queries

Model Based Events: Hooks

Result-Set Types

Updating Record In A Table

Deleting Records

Accessing PDO methods Statically

Using Transactions