# Development - Models

- General Info
- Working with PDO
- Working with Active Records
  - Defining AR Class
  - Creating Record
  - Updating Record
  - Deleting Record
  - Reading Record
  - Additional Methods

### General Info ¶

Database manipulation classes representes 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.

### 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:

- :id is a named placeholder
- i:id is a key in the array of values, where i denotes the Integer data type (it means that the value retrieved from an array will be converted to an Integer type)
- array('i:id'=>10) is array of values

Available data types for placeholders:

- 's': String/Char
- 'i': Integer
- 'b': Boolean
- 'f': Float/Double
- 'n': SQL NULL data type

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 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.

#### 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 you defined the table's primary key as auto-incremental, the AR instance will contain an updated primary key adter sucessfull insertion. In the example above, the `id` property will reflect the primary key value of the newly inserted post, even though we never change it explicitly.
If a column is defined with some pre-defined default value (e.g. 0000-00-00, 1 or NULL) in the table schema, the corresponding property in the AR instance will automatically get such value after the instance is created.

#### 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 = 'New page name';
    $page->content = 'changed content of page body';
    $page->date_updated = date('Y-m-d');
    $page->save();

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');

#### 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 post whose 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 Record

To read data from a database table, we can use one of the find methods as follows.

    // Finds the first row satisfying the specified conditions
    $page = Pages::model()->find($conditions, $params);
    // Finds the row with the specified primary key
    $page = Pages::model()->findByPk($pk, $conditions, $params);
    // 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.'%'));

#### 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 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();