Development - Models
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 the array of value
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.
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
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',
'fields'=>array(),
'parent_key'=>'profile_id'),
'2' => array(self::HAS_ONE, 'profiles_addresses', 'id',
'condition'=>'',
'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);
// delete 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:
// delete the rows matching specified condition
Pages::model()->deleteAll($condition, $params);
// delete 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.
// find the first row satisfying the specified conditions
$page = Pages::model()->find($conditions, $params);
// find the row with the specified primary key
$page = Pages::model()->findByPk($pk, $conditions, $params);
// find the row with the specified attribute values
$page = Pages::model()->findByAttributes($attributes, $conditions, $params);
// find all rows satisfying the specified conditions
$page = Pages::model()->findAll($conditions, $params);
// find 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);
// check 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();