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 subdirectoryprotected/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:
- :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
- 's': String/Char
- 'i': Integer
- 'b': Boolean
- 'f': Float/Double
- 'n': SQL NULL data type
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 methodmodel()
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 thesave()
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 samesave()
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();