Development - Models


General Info

Database manipulation classes represents the "Model" layer as it defined in MVC. ApPHP framework contains core class called Model for the implementation of database models. Description classes for entities used in applications must extend this class. Model class files must be located in subdirectory protected/models/ inside the application directory. There are some table names that were reserved for system needs: table names that end with _translations, _description or site_info.

Working with PDO

A model description may contain the application's table name as a value of the $_table variable (optional) and a class constructor, that calls to the base (parent) class constructor.

Example of a model that extends from CModel class that uses PDO extension: 
class Accounts extends CModel
{
    /** @var object */    
    private static $_instance;

    protected $_table = CConfig::get('db.prefix').'accounts';
    
    public function __construct()
    {
        parent::__construct();
    }
    
    /**
     * Returns the static model of the current class
    */
    public static function model()
    {
        if(self::$_instance == null){
            self::$_instance = new self();
        }

        return self::$_instance;
    }

    // Returns some data
    public function getInfo()
    {
        $result = $this->_db->select('
            SELECT id, role
            FROM '.$this->_table.'
            WHERE is_active = 1'
        );

        return result;
    }
}

// Example of usage
Accounts::model()->getInfo();
CModel query methods: 
// Performs select query and returns an array containing all of the result set rows
$this->_db->select('SELECT * FROM '.$this->_table.' WHERE id = :id', array('id' => $id));
   
// Performs insert query and returns true of false depending on result
$this->_db->insert($this->_table, array('name'=>'John Smith', 'email'=>'j.smith@email.me'));
    
// Performs update query and returns true of false depending on result
$this->_db->update($this->_table, array('address'=>'New address', 'status'=>2), 'is_active = :is_active', array(':is_active'=>1));
$this->_db->update($this->_table, array('address'=>'New address', 'status'=>2), 'is_active = 1');

// Performs delete query and returns a number of affected rows
$this->_db->delete($this->_table, 'id = :id', array('i:id'=>10));
$this->_db->delete($this->_table, 'id = 11');

// Performs a custom query
$this->_db->customQuery('SELECT * FROM '.$this->_table.' WHERE id = '.(int)$id);

// Performs a custom exec query
$this->_db->customExec("INSERT INTO ".$this->_table."(id, name) VALUES (NULL, 'John Smith')");
Notes: Available data types for placeholders: If no data type has been specified for a placeholder (e.g., if you use only :id in an SQL query), then the String type is used by default. Using of placeholders makes the creating of SQL queries easier and, what is more important, eliminates the possibility of writing code prone to SQL injection vulnerabilities.
If you use $conditionParams['i:min_mileage'] = $minMileage; - it's not enough. You have to pass integer or to make a cast: $conditionParams['i:min_mileage'] = (int)$minMileage;

Direct usage of CDatabase:

In some cases you may need to access database directly from controllers, models or even views. Such practise may be useful when you have no model for specific database table or you simply want to perform a separate query without using of models: 
// Example of direct usage of CDatabase
$result = CDatabase::init()->select('SELECT COUNT(*) as cnt FROM '.CConfig::get('db.prefix').'sessions');


Working with Query Builder

CDbCommand represents an SQL statement to execute against a database.
To execute a non-query SQL (such as insert, delete, update), use PDO methods (see previous paragraph).
To execute an SQL statement that returns result data set (such as SELECT), use query or its convenient versions queryRow, queryColumn, or queryScalar. 
$accounts = CDatabase::init()->createCommand()
    ->select('*')
    ->from(CConfig::get('db.prefix').'accounts')
    ->order('id ASC')
    ->queryAll();
     
$account = CDatabase::init()->createCommand()
    ->select('id, username, password')
    ->from(CConfig::get('db.prefix').'accounts')
    ->where('id=:id', array(':id'=>1))
    ->queryRow();


Working with Active Records

Although PDO can handle virtually any database-related task, chances are that we would spend more than 90% of our time in writing some SQL statements which perform the common CRUD (create, read, update and delete) operations. It is also difficult to maintain a code, when it mixed with SQL statements. To solve these problems, we can use Active Records. As a part of Active Record design pattern, we also use Entities.

Defining AR Class

Below an example of a model class that extends from CActiveRecord class. When you create such class you have to re-define static method model() as it's described below, also relations mey be defined for each model according to your needs. Also you may use _customFields() method to define special fields in our query. 
class Accounts extends CActiveRecord
{
    /** @var string */    
    protected $_table = 'accounts';
    
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Returns the static model of the specified AR class
     */
    public static function model()
    {
       return parent::model(__CLASS__);
    }
    
    /**
     * Used to define relations between different tables in database and current $_table
     * @return array 
     */
    protected function _relations()
    {
        // way #1 - standard way, self::LEFT_OUTER_JOIN is a default JOIN type
        return array(
            'country_id' => array(self::BELONGS_TO, 'countries', 'id', 'joinType'=>self::LEFT_OUTER_JOIN),
            'profile_id' => array(self::HAS_ONE, 'profiles', 'id', 'condition'=>'', 'fields'=>array('name'=>'')),
        );
        // way #2 - used when you need to define multiple relations from the same key
        /* return array(
            '0' => array(self::BELONGS_TO, 'countries', 'id',
                'joinType'=>self::LEFT_OUTER_JOIN,
                'parent_key'=>'country_id'),
            '1' => array(self::HAS_ONE, 'profiles', 'id',
                'condition'=>CConfig::get('db.prefix').'.is_active = 1',
                'joinType'=>self::INNER_JOIN,
                'fields'=>array(),
                'parent_key'=>'profile_id'),
            '2' => array(self::HAS_ONE, 'profiles_addresses', 'id',
                'condition'=>'',
                'fields'=>array('name'=>''),
                'parent_key'=>'profile_id'),
            '3' => array(self::MANY_MANY, 'profiles_projects', 'id',
                'condition'=>'',
                'joinType'=>self::INNER_JOIN,
                'fields'=>array('name'=>''),
                'parent_key'=>'profile_id'),
        ); */        
    }
    
    /**
     * Used to define custom fields
     * This method should be overridden
     */
    protected function _customFields()
    {
        // sample 1:
        /* return array(
           'CONCAT(first_name, " ", last_name)' => 'fullname'
        ); */
        // sample 2:
        /* return array(
           'CONCAT(first_name, " ", last_name)' => 'fullname',
           'CONCAT(phone, ", ", fax)' => 'contacts'
        ); */        
        /* sample 3:
        return array('
           (SELECT COUNT(*) FROM '.CConfig::get('db.prefix').$this->_tableTranslation.')' => 'records_count'
        ); */
        
        return array();
    }    
}
Active Records (AR) is a popular Object-Relational Mapping (ORM) technique. Each AR class represents a database table (or view) whose attributes are represented as the AR class properties, and an AR instance represents a row in that table. Common CRUD operations are implemented as AR methods. As a result, we can access our data in a more object-oriented way.

As it described before the column values of a table row can be accessed as properties of the corresponding AR instance. For example, in the following we set the first_name column (attribute): 
$account = new Accounts();
$account->first_name = 'John Smith';
Although we never explicitly declare the first_name property in the Accounts class, we can still access it in the code. This is because first_name is a column in the accounts table, and CActiveRecord makes it accessible as a property with the help of the PHP __get() magic method. An error will be shown in debug mode if we attempt to access a non-existing column in the same way.

AR relies on well defined primary keys of tables, so you must define primary key (numeric and auto-incremented) for your tables. Model names must be plural (in case of working with a spesific database table) or singular (e.g Login model). View folder must be with the same name as a controller class (e.g. Customers -> derived from CustomersController)

Creating Record

If you need to insert a new row into a database table, you have to create a new instance of the corresponding AR class, then set its properties associated with the table columns, and call the save() method to finish the insertion operation. Here the example: 
$page = new Pages();
$page->title = 'New page';
$page->content = 'page body content';
$page->date_created = date('Y-m-d');
$page->save();
If entity is used you have to create it firstly and then pass as argument to the save() method: 
// Create new entity
$page = new PageEntity();
// Fill all data
$page->header = 'Something';
// other changes...
// Create model, false - used to avoid filling all fields in ORM
$pages = Pages::model(false);
$page->save($page)
If you defined the table's primary key as auto-incremental, the AR instance will contain an updated primary key adter successfull insertion. In the example above, the id property will reflect the primary key value of the newly inserted page, even though we never change it explicitly.

If a column is defined with some pre-defined default value (e.g. 'pending', 1 or NULL) in the table schema, the corresponding property in the AR instance will automatically get such value after the instance is created.

Another way to insert new rows is to use create method. Here the example: 
$page = new Pages();
$data = array(
    'title' => 'New page',
    'content' => 'page body content',
    'date_created' => date('Y-m-d')
);
$page->create($data);


Updating Record

After an AR instance is populated with retrieved column values, we can change their values and then save them back to the original database table. As you may see in following example, we use the same save() method to perform both insertion and updating operations. If an AR instance is created using the new operator, calling save() would insert a new row into the database table and if the AR instance is the result of findByPk() method call, calling save() would update the existing row in the table.

Here the example: 
$page = Pages::model()->findByPk(12);
$page->title = 'Updated page name';
$page->content = 'changed content of page body';
$page->date_updated = date('Y-m-d');
$page->save();
If entity is used you have to create it firstly and then pass as argument to the save() method: 
// Create new entity with specified ID
$page = new PageEntity($this->pageId);
// Change data
$page->header = 'Something updated';
// other changes...
// $page->setGuarded(array('author_id', 'page_datetime'));
// Create model, false - used to avoid filling all fields in ORM
$pages = Pages::model(false);
$pages->save($page);
You may also use following methods: 
// Updates records with the specified primary key
$result = Accounts::model()->updateByPk($id, array('password'=>$password));
// Updates all records matching the specified condition
$result = Accounts::model()->updateAll(array('password'=>$password), 'is_active = 1');

Another way to update rows is to use update method. Here the example: 
$page = new Pages();
$id = 12;
$data = array(
    'title' => 'Updated page name',
    'content' => 'changed content of page body',
    'date_updated' => date('Y-m-d')
);
$page->update($id, $data);


Deleting Record

We can delete a row of data if an AR instance has been populated with this row. After the deletion, the AR instance remains unchanged, but the corresponding row in the database table is removed. For example: 
// Assuming there is a page those ID is 12
$page = Pages::model()->findByPk(12);
// Deletes this row from the database table
$page->delete();
Also, there are some other methods that allow to delete rows without the need of loading them first: 
// Deletes the rows matching specified condition
Pages::model()->deleteAll($condition, $params);
// Deletes the rows matching specified condition and primary key
Pages::model()->deleteByPk($pk, $condition, $params);

Reading Records

To read data from a database table, we can use one of the find methods as follows. 
// Finds the first row satisfying specific conditions 
$page = Pages::model()->find($conditions, $params);                 /* returns object */
$page = Pages::model()->find($conditions, $params)->resultArray();  /* returns array */
// Finds the row with the specified primary key 
$page = Pages::model()->findByPk($pk, $conditions, $params);                 /* returns object */ 
$page = Pages::model()->findByPk($pk, $conditions, $params)->resultArray();  /* returns array */
// Finds the row with the specified attribute values
$page = Pages::model()->findByAttributes($attributes, $conditions, $params);
// Finds all rows satisfying the specified conditions
$page = Pages::model()->findAll($conditions, $params);
// Finds all rows satisfying the specified conditions with %LIKE%
$news = News:model()->findAll(CConfig::get('db.prefix').$this->_tableTranslation.'.news_text LIKE :keywords', array(':keywords'=>'%'.$keywords.'%'));
Reading records with chunks. The chunk method breaks the record set into small parts of a given size 
$posts = [];
// 1st parameter - conditions
// 2nd parameter - condition parameters
// 3rd parameter - chunk size
// 4th parameter - callback function
Posts::model()->chunk($conditions, [], 10, function ($records) use(&$posts){
    foreach ($records as $key => $record) {
        $posts[] = $record;
    }
});

Additional Methods

There are some additional methods which helps you to work with AR. 
// Returns recordset with distinct values of the given field
$page = Pages::model()->distinct($field);
// Checks if there is at least one row satisfying the specified condition
$page = Pages::model()->exists($conditions, $params);
// Finds the number of rows satisfying the specified query condition
$page = Pages::model()->count($conditions, $params);
// Finds a maximum value of the specified column
$page = Pages::model()->max($columnName, $conditions, $params);
// Finds a minimum value of the specified column
$page = Pages::model()->min($columnName, $conditions, $params);
// Finds a sum value of the specified column
$page = Pages::model()->sum($columnName, $conditions, $params);
// Reloads model data according to current primary key
$page = Pages::model()->findByPk(1);
$page = $page->refresh();