Active Record
Available from Prado versions 3.1a onwards.
Active Records are objects that wrap a row in a database table or view,
encapsulate the database access and add domain logic on that data.
The basics of an Active Record are business classes, e.g., a
Products class, that match very closely the record structure
of an underlying database table. Each Active Record will be responsible for
saving and loading data to and from the database.
Info:
The data structure of an Active Record should match that of a table
in the database.
Each column of a table should have a corresponding member variable or property in the
Active Record class the represents the table.
When to Use It
Active Record is a good choice for domain logic that isn't too complex,
such as creates, reads, updates, and deletes. Derivations and validations
based on a single record work well in this structure. Active Record has the
primary advantage of simplicity. It's easy to build
Active Records, and they are easy to understand.
However, as your business logic grows in complexity, you'll soon want
to use your object's direct relationships, collections, inheritance, and so
forth. These don't map easily onto Active Record, and adding them piecemeal
gets very messy.
Another argument against Active Record is the fact that it couples the object
design to the database design. This makes it more difficult to refactor as a project goes forward.
The alternative is to use a Data Mapper that separates the roles of the
business object and how these objects are stored.
Prado provides a complimentary choice between Active Record and
SqlMap Data Mapper.
A SqlMap Data Mapper can be used to load Active Record objects, in turn; these
Active Record objects can be used to update the database.
The "relationship" between Active Records and SqlMap is illustrated in the
following diagram. More details regarding the SqlMap Data Mapper can be found in
the SqlMap Manual.
The Active Record class has functionality to perform the following tasks.
- Create, Retrieve, Update and Delete records.
- Finder methods to wrap commonly used SQL queries and return Active Record objects.
- Fetch relationships (related foreign objects) such as "has many", "has one", "belongs to" and "many to many" via association table.
- Lazy loading of relationships.
Design Implications
Prado's implementation of Active Record does not maintain referential identity. Each object obtained using
Active Record is a copy of the data in the database. For example, If you ask for
a particular customer and get back a Customer object, the next time you ask for
that customer you get back another instance of a Customer object. This implies that a strict
comparison (i.e., using ===) will return false, while loose comparison (i.e., using ==) will
return true if the object values are equal by loose comparison.
This is design implication related to the following question.
"Do you think of the customer as an object, of which there's only one,
or do you think of the objects you operate on as copies of the database?"
Other O/R mappings will imply that there is only one Customer object with custID 100, and it literally is that customer.
If you get the customer and change a field on it, then you have now changed that customer.
"That constrasts with: you have changed this copy of the customer, but not that copy.
And if two people update the customer on two copies of the object, whoever updates first,
or maybe last, wins." [A. Hejlsberg 2003]
Database Supported
The Active Record implementation utilizes the Prado DAO classes for data access.
The current Active Record implementation supports the following database.
Support for other databases can be provided when there are sufficient demands.
Defining an Active Record
Let us
consider the following "users" table that contains two columns named "username" and "email",
where "username" is also the primary key.
CREATE TABLE users
(
username VARCHAR( 20 ) NOT NULL ,
email VARCHAR( 200 ) ,
PRIMARY KEY ( username )
);
Next we define our Active Record class that corresponds to the "users" table.
class UserRecord extends TActiveRecord
{
const TABLE='users'; //table name
public $username; //the column named "username" in the "users" table
public $email;
/**
* @return TActiveRecord active record finder instance
*/
public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}
Each column of the "users" table must have corresponding
property of the same name as the column name in the UserRecord class.
Of course, you also define additional member variables or properties that does not exist in the table structure.
The class constant
TABLE is optional when the class name is the same as
the table name in the database, otherwise TABLE must
specify the table name that corresponds to your Active Record class.
Tip:
You may specify qualified table names. E.g. for MySQL, TABLE = "`database1`.`table1`".
Note:
Since version
3.1.3 you can also use a method
table() to define the table name.
This allows you to dynamically specify which table should be used by the ActiveRecord.
class TeamRecord extends TActiveRecord
{
public function table() {
return 'Teams';
}
}
Since TActiveRecord extends TComponent, setter and
getter methods can be defined to allow control over how variables
are set and returned. For example, adding a $level property to the UserRecord class:
class UserRecord extends TActiveRecord {
... //existing definitions as above
private $_level;
public function setLevel($value) {
$this->_level=TPropertyValue::ensureInteger($value,0);
}
public function getLevel($value){
return $this->_level;
}
}
More details regarding TComponent can be found in the Components documentation.
Later we shall use the getter/setters to allow for lazy loading of relationship objects.
Info:
TActiveRecord can also work with database views by specifying the constant TABLE
corresponding to the view name. However, objects returned
from views are read-only, calling the save() or delete() method
will raise an exception.
The static method finder() returns an UserRecord instance
that can be used to load records from the database. The loading of records
using the finder methods is discussed a little later. The TActiveRecord::finder()
static method takes the name of an Active Record class as parameter.
Setting up a database connection
A default database connection for Active Record can be set as follows.
See Establishing Database Connection for
further details regarding creation of database connection in general.
//create a connection and give it to the Active Record manager.
$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL
$conn = new TDbConnection($dsn, 'dbuser','dbpass');
TActiveRecordManager::getInstance()->setDbConnection($conn);
Alternatively, you can create a base class and override the getDbConnection()
method to return a database connection. This is a simple way to permit multiple
connections and multiple databases. The following code demonstrates defining
the database connection in a base class (not need to set the DB connection anywhere else).
class MyDb1Record extends TActiveRecord
{
public function getDbConnection()
{
static $conn;
if($conn===null)
$conn = new TDbConnection('xxx','yyy','zzz');
return $conn;
}
}
class MyDb2Record extends TActiveRecord
{
public function getDbConnection()
{
static $conn;
if($conn===null)
$conn = new TDbConnection('aaa','bbb','ccc');
return $conn;
}
}
Using application.xml within the Prado Framework
The default database connection can also be configured using a <module>
tag in the application.xml
or config.xml as follows.
<modules>
<module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true">
<database ConnectionString="pgsql:host=localhost;dbname=test"
Username="dbuser" Password="dbpass" />
</module>
</modules>
Tip:
The
EnableCache attribute when set to "true" will cache the table
meta data, that is, the table columns names, indexes and constraints are
saved in the cache and reused. You must clear or disable the cache if you
wish to see changes made to your table definitions. A
cache
module must also be defined for the cache to function.
A ConnectionID property can be specified with value corresponding
to another TDataSourceConfig module configuration's ID value. This allows
the same database connection to be used in other modules such as SqlMap.
<modules>
<module class="System.Data.TDataSourceConfig" id="db1">
<database ConnectionString="pgsql:host=localhost;dbname=test"
Username="dbuser" Password="dbpass" />
</module>
<module class="System.Data.ActiveRecord.TActiveRecordConfig"
ConnectionID="db1" EnableCache="true" />
<module class="System.Data.SqlMap.TSqlMapConfig"
ConnectionID="db1" ... />
</modules>
Loading data from the database
The TActiveRecord class provides many convenient methods to find
records from the database. The simplest is finding one record by matching a primary key or a
composite key (primary keys that consists of multiple columns).
See the API Manual for
more details.
Info:
All finder methods that may return 1 record only will return null if no matching data
is found. All finder methods that return an array of records will return an empty array if no
matching data is found.
findByPk()
Finds one record using only a primary key or a composite key.
$finder = UserRecord::finder();
$user = $finder->findByPk($primaryKey);
//when the table uses a composite key
$record = $finder->findByPk($key1, $key2, ...);
$record = $finder->findByPk(array($key1, $key2,...));
findAllByPks()
Finds multiple records using a list of primary keys or composite keys.
The following are equivalent for primary keys (primary key consisting of only one column/field).
$finder = UserRecord::finder();
$users = $finder->findAllByPks($key1, $key2, ...);
$users = $finder->findAllByPks(array($key1, $key2, ...));
The following are equivalent for composite keys.
//when the table uses composite keys
$record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...);
$keys = array( array($key1, $key2), array($key3, $key4), ... );
$record = $finder->findAllByPks($keys);
find()
Finds one single record that matches the criteria. The criteria
can be a partial SQL string or a TActiveRecordCriteria object.
$finder = UserRecord::finder();
//:name and :pass are place holders for specific values of $name and $pass
$finder->find('username = :name AND password = :pass',
array(':name'=>$name, ':pass'=>$pass));
//using position place holders
$finder->find('username = ? AND password = ?', array($name, $pass));
//same as above
$finder->find('username = ? AND password = ?', $name, $pass);
//$criteria is of TActiveRecordCriteria
$finder->find($criteria); //the 2nd parameter for find() is ignored.
The TActiveRecordCriteria class has the following properties:
- Parameters -- name value parameter pairs.
- OrdersBy -- column name and ordering pairs.
- Condition -- parts of the WHERE SQL conditions.
- Limit -- maximum number of records to return.
- Offset -- record offset in the table.
$criteria = new TActiveRecordCriteria;
$criteria->Condition = 'username = :name AND password = :pass';
$criteria->Parameters[':name'] = 'admin';
$criteria->Parameters[':pass'] = 'prado';
$criteria->OrdersBy['level'] = 'desc';
$criteria->OrdersBy['name'] = 'asc';
$criteria->Limit = 10;
$criteria->Offset = 20;
findAll()
Same as find() but returns an array of objects.
findBy*() and findAllBy*()
Dynamic find method using parts of the method name as search criteria.
Method names starting with findBy return 1 record only
and method names starting with findAllBy return an array of records.
The condition is taken as part of the method name after findBy or findAllBy.
The following blocks of code are equivalent:
$finder->findByName($name)
$finder->find('Name = ?', $name);
$finder->findByUsernameAndPassword($name,$pass);
$finder->findBy_Username_And_Password($name,$pass);
$finder->find('Username = ? AND Password = ?', $name, $pass);
$finder->findAllByAge($age);
$finder->findAll('Age = ?', $age);
Tip:
You may also use a combination of AND and OR as a condition in the dynamic methods.
findBySql() and findAllBySql()
Finds records using full SQL where findBySql()
return an Active Record and findAllBySql()returns an array of record objects.
For each column returned, the corresponding Active Record class must define a member variable or
property for each corresponding column name.
class UserRecord2 extends UserRecord
{
public $another_value;
}
$sql = "SELECT users.*, 'hello' as another_value FROM users";
$users = TActiveRecord::finder('UserRecord2')->findAllBySql($sql);
count()
Find the number of matchings records, accepts same parameters as the findAll() method.
Inserting and updating records
Add a new record using TActiveRecord is very simple, just create a new Active
Record object and call the save() method. E.g.
$user1 = new UserRecord();
$user1->username = "admin";
$user1->email = "admin@example.com";
$user1->save(); //insert a new record
$data = array('username'=>'admin', 'email'=>'admin@example.com');
$user2 = new UserRecord($data); //create by passing some existing data
$user2->save(); //insert a new record
Tip:
If you insert a new record into a MySQL table that has columns
defined with "autoincrement", the Active Record objects will be updated with the new
incremented value.
To update a record in the database, just change one or more properties of
the Active Record object that has been loaded from the database and then
call the save() method.
$user = UserRecord::finder()->findByName('admin');
$user->email="test@example.com"; //change property
$user->save(); //update it.
Active Record objects have a simple life-cycle illustrated in the following diagram.
We see that new TActiveRecord objects are created by either using one of the find*()
methods or using creating a new instance by using PHP's new keyword. Objects
created by a find*() method starts with clean state. New instance of
TActiveRecord created other than by a find*() method starts with new state.
Whenever you
call the save() method on the TActiveRecord object, the object enters the clean
state. Objects in the clean becomes dirty whenever one of more of its
internal states are changed. Calling the delete() method on the object
ends the object life-cycle, no further actions can be performed on the object.
Deleting existing records
To delete an existing record that is already loaded, just call the delete() method.
You can also delete records in the database by primary keys without
loading any records using the deleteByPk() method (and equivalently the deleteAllByPks() method).
For example, to delete one or several records with tables using one or more primary keys.
$finder->deleteByPk($primaryKey); //delete 1 record
$finder->deleteAllByPks($key1,$key2,...); //delete multiple records
$finder->deleteAllByPks(array($key1,$key2,...)); //delete multiple records
For composite keys (determined automatically from the table definitions):
$finder->deleteByPk(array($key1,$key2)); //delete 1 record
//delete multiple records
$finder->deleteAllByPks(array($key1,$key2), array($key3,$key4),...);
//delete multiple records
$finder->deleteAllByPks(array( array($key1,$key2), array($key3,$key4), .. ));
deleteAll() and deleteBy*()
To delete by a criteria, use deleteAll($criteria) and deleteBy*()
with similar syntax to findAll($criteria) and findAllBy*() as
described above.
//delete all records with matching Name
$finder->deleteAll('Name = ?', $name);
$finder->deleteByName($name);
//delete by username and password
$finder->deleteBy_Username_And_Password($name,$pass);
Transactions
All Active Record objects contain the property DbConnection
that can be used to obtain a transaction object.
$finder = UserRecord::finder();
$finder->DbConnection->Active=true; //open if necessary
$transaction = $finder->DbConnection->beginTransaction();
try
{
$user = $finder->findByPk('admin');
$user->email = 'test@example.com'; //alter the $user object
$user->save();
$transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
$transaction->rollBack();
}
Events
The TActiveRecord offers two events, OnCreateCommand and OnExecuteCommand.
The OnCreateCommand event is raised when a command is prepared and
parameter binding is completed. The parameter object is TDataGatewayEventParameter of which the
Command property can be inspected to obtain the SQL query to be executed.
The OnExecuteCommand event is raised when a command is executed and the
result from the database was returned. The parameter object is TDataGatewayResultEventParameter
of which the Result property contains the data return from the database.
The data returned can be changed by setting the Result property.
Logging Example
Using the OnExecuteCommand we can attach an event handler to log
the entire SQL query executed for a given TActiveRecord class or instance. For example, we define
a base class and override either the getDbConnection() or the constructor.
class MyDb1Record extends TActiveRecord
{
public function getDbConnection()
{
static $conn;
if($conn===null)
{
$conn = new TDbConnection('xxx','yyy','zzz');
$this->OnExecuteCommand[] = array($this,'logger');
}
return $conn;
}
public function logger($sender,$param)
{
var_dump($param->Command->Text);
}
}
//alternatively as per instance of per finder object
function logger($sender,$param)
{
var_dump($param->Command->Text);
}
TActiveRecord::finder('MyRecord')->OnExecuteCommand[] = 'logger';
$obj->OnExecuteCommand[] = array($logger, 'log'); //any valid PHP callback.
Active Record Relationships
Available from Prado versions 3.1rc1 onwards.
The Prado Active Record implementation supports the foreign key mappings for database
that supports foreign key constraints. For Active Record relationships to function the
underlying database must support foreign key constraints (e.g. MySQL using InnoDB).
In the following sections we will consider the following table relationships between
Teams, Players, Skills and Profiles.
The goal is to obtain object models that represent to some degree the entity
relationships in the above figure.
There is a mismatch between relationships with objects and table relationships.
First there's a difference in representation. Objects handle links by storing references
that are held by the runtime memory-managed environment. Relational databases handle
links by forming a key into another table. Second, objects can easily use collections
to handle multiple references from a single field, while normalization forces
all entity relation links to be single valued. This leads to reversals of the data
structure between objects and tables. The approach taken in the Prado Active Record
design is to use the table foreign key constraints to derive object relationships. This implies
that the underlying database must support foreign key constraints.
Tip:
For SQLite database, you may create tables that defines the foreign key
constraints such as the example below. However, these constraints are
NOT
enforced by the SQLite database itself.
CREATE TABLE foo
(
id INTEGER NOT NULL PRIMARY KEY,
id2 CHAR(2)
);
CREATE TABLE bar
(
id INTEGER NOT NULL PRIMARY KEY,
foo_id INTEGER
CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE
);
Foreign Key Mapping
The entity relationship between the Teams and Players table is what is known
as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of
object relationships, we say that a TeamRecord object has many PlayerRecord objects.
(Notice the reversal of the direction of relationships between tables and objects.)
Has Many Relationship
We model the Team object as the following Active Record classes.
class TeamRecord extends TActiveRecord
{
const TABLE='Teams';
public $name;
public $location;
public $players=array(); // this declaration is no longer needed since v3.1.2
//define the $player member having has many relationship with PlayerRecord
public static $RELATIONS=array
(
'players' => array(self::HAS_MANY, 'PlayerRecord', 'team_name'),
);
public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}
The static $RELATIONS property of TeamRecord defines that the
property $players has many PlayerRecords. Multiple relationships
is permitted by defining each relationship with an entry in the $RELATIONS
array where array key for the entry corresponds to the property name.
In array(self::HAS_MANY, 'PlayerRecord'), the first element defines the
relationship type, the valid types are self::HAS_MANY, self::HAS_ONE,
self::BELONGS_TO and self::MANY_TO_MANY.
The second element is a string 'PlayerRecord' that corresponds to the
class name of the PlayerRecord class.
And the third element 'team_name' refers to the foreign key column in the Players table that
references to the Teams table.
Note:
As described in the code comment above, since version 3.1.2, related properties no longer
need to be explicitly declared. By default, they will be implicitly declared according to
keys of the $RELATIONS array. A major benefit of declared related properties implicitly
is that related objects can be automatically loaded in a lazy way. For example, assume we have
a TeamRecord instance $team. We can access the players via $team->players,
even if we have never issued fetch command for players. If $players is explicitly declared,
we will have to use the with approach described in the following to fetch the player records.
The foreign key constraint of the Players table is used to determine the corresponding
Teams table's corresponding key names. This is done automatically handled
in Active Record by inspecting the Players and Teams table definitions.
Info:
Since version
3.1.2, Active Record supports multiple foreign key
references of the same table. Ambiguity between multiple foreign key references to the same table is
resolved by providing the foreign key column name as the 3rd parameter in the relationship array.
For example, both of the following foreign keys
owner_id and
reporter_id
references to the same table defined in
UserRecord.
class TicketRecord extends TActiveRecord
{
public $owner_id;
public $reporter_id;
public $owner; // this declaration is no longer needed since v3.1.2
public $reporter; // this declaration is no longer needed since v3.1.2
public static $RELATION=array
(
'owner' => array(self::BELONGS_TO, 'UserRecord', 'owner_id'),
'reporter' => array(self::BELONGS_TO, 'UserRecord', 'reporter_id'),
);
}
This is applicable to relationships including
BELONGS_TO,
HAS_ONE and
HAS_MANY. See section
Self Referenced Association Tables for solving ambiguity of
MANY_TO_MANY
relationships.
The "has many" relationship is not fetched automatically when you use any of the Active Record finder methods.
You will need to explicitly fetch the related objects as follows. In the code below, both lines
are equivalent and the method names are case insensitive.
$team = TeamRecord::finder()->withPlayers()->findAll();
$team = TeamRecord::finder()->with_players()->findAll(); //equivalent
The method with_xxx() (where xxx is the relationship property
name, in this case, players) fetches the corresponding PlayerRecords using
a second query (not by using a join). The with_xxx() accepts the same
arguments as other finder methods of TActiveRecord, e.g. with_players('age = ?', 35).
Note:
It is essential to understand that the related objects are fetched using additional
queries. The first query fetches the source object, e.g. the
TeamRecord in the above example code.
A second query is used to fetch the corresponding related
PlayerRecord objects.
The usage of the two query is similar to a single query using Left-Outer join with the
exception that null results on the right table
are not returned. The consequence of using two or more queries is that the aggregates
and other join conditions are not feasible using Active Records. For queries outside the
scope of Active Record the
SqlMap Data Mapper may be considered.
Info:
The above with approach also works with implicitly declared related properties (introduced
in version 3.1.2). So what is the difference between the with approach and the lazy loading
approach? Lazy loading means we issue an SQL query if a related object is initially accessed and not ready,
while the with approach queries for the related objects once for all, no matter the related objects
are accessed or not. The lazy loading approach is very convenient since we do not need to explictly
load the related objects, while the with approach is more efficient if multiple records are
returned, each with some related objects.
Has One Relationship
The entity relationship between Players and Profiles is one to one. That is,
each PlayerRecord object has one ProfileRecord object (may be none or null).
A has one relationship is nearly identical to a has many relationship with the exception
that the related object is only one object (not a collection of objects).
Belongs To Relationship
The "has many" relationship in the above section defines a collection of foreign
objects. In particular, we have that a TeamRecord has many (zero or more)
PlayerRecord objects. We can also add a back pointer by adding a property
in the PlayerRecord class that links back to the TeamRecord object,
effectively making the association bidirectional.
We say that the $team property in PlayerRecord class belongs to a TeamRecord object.
The following code defines the complete PlayerRecord class with 3 relationships.
class PlayerRecord extends TActiveRecord
{
const TABLE='Players';
public $player_id;
public $age;
public $team_name;
public $team; // this declaration is no longer needed since v3.1.2
public $skills=array(); // this declaration is no longer needed since v3.1.2
public $profile; // this declaration is no longer needed since v3.1.2
public static $RELATIONS=array
(
'team' => array(self::BELONGS_TO, 'TeamRecord', 'team_name'),
'skills' => array(self::MANY_TO_MANY, 'SkillRecord', 'Player_Skills'),
'profile' => array(self::HAS_ONE, 'ProfileRecord', 'player_id'),
);
public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}
The static $RELATIONS property of PlayerRecord defines that the
property $team belongs to a TeamRecord.
The $RELATIONS array also defines two other relationships that we
shall examine in later sections below.
In array(self::BELONGS_TO, 'TeamRecord', 'team_name'), the first element defines the
relationship type, in this case self::BELONGS_TO;
the second element is a string 'TeamRecord' that corresponds to the
class name of the TeamRecord class; and the third element 'team_name' refers
to the foreign key of Players referencing Teams.
A player object with the corresponding team object may be fetched as follows.
$players = PlayerRecord::finder()->with_team()->findAll();
The method with_xxx() (where xxx is the relationship property
name, in this case, team) fetches the corresponding TeamRecords using
a second query (not by using a join). The with_xxx() accepts the same
arguments as other finder methods of TActiveRecord, e.g.
with_team('location = ?', 'Madrid').
Tip:
Additional relationships may be fetched by chaining the
with_xxx() together as the following
example demonstrates.
$players = PlayerRecord::finder()->with_team()->with_skills()->findAll();
Each
with_xxx() method will execute an additional SQL query. Every
with_xxx() accepts arguments similar to those in the
findAll() method and is only
applied to that particular relationship query.
The "belongs to" relationship of ProfileRecord class is defined similarly.
class ProfileRecord extends TActiveRecord
{
const TABLE='Profiles';
public $player_id;
public $salary;
public $player; // this declaration is no longer needed since v3.1.2
public static $RELATIONS=array
(
'player' => array(self::BELONGS_TO, 'PlayerRecord'),
);
public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}
In essence, there exists a "belongs to" relationship for objects corresponding to
entities that has column which are foreign keys. In particular, we see that
the Profiles table has a foreign key constraint on the column player_id
that relates to the Players table's player_id column. Thus, the ProfileRecord
object has a property ($player) that belongs to a PlayerRecord object.
Similarly, the Players table has a foreign key constraint on the column team_name that relates to the
Teams table's name column.
Thus, the PlayerRecord object has a property ($team) that belongs to a
TeamRecord object.
Parent Child Relationships
A parent child relationship can be defined using a combination of has many and belongs to
relationship that refers to the same class. The following example shows a parent children relationship between
"categories" and a "parent category".
class Category extends TActiveRecord
{
public $cat_id;
public $category_name;
public $parent_cat_id;
public $parent_category; // this declaration is no longer needed since v3.1.2
public $child_categories=array(); // this declaration is no longer needed since v3.1.2
public static $RELATIONS=array
(
'parent_category' => array(self::BELONGS_TO, 'Category', 'parent_cat_id'),
'child_categories' => array(self::HAS_MANY, 'Category', 'parent_cat_id'),
);
}
Query Criteria for Related Objects
In the above, we show that an Active Record object can reference to its related objects by
declaring a static class member $RELATIONS which specifies a list of relations. Each relation
is specified as an array consisting of three elements: relation type, related AR class name,
and the foreign key(s). For example, we use array(self::HAS_MANY, 'PlayerRecord', 'team_name')
to specify the players in a team. There are two more optional elements that can be specified
in this array: query condition (the fourth element) and parameters (the fifth element).
They are used to control how to query for the related objects. For example, if we want to obtain
the players ordered by their age, we can specify array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'ORDER BY age').
If we want to obtain players whose age is smaller than 30, we could use
array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'age<:age', array(':age'=>30)). In general,
these two additional elements are similar as the parameters passed to the find() method in AR.
Association Table Mapping
Objects can handle multivalued fields quite easily by using collections as field values.
Relational databases don't have this feature and are constrained to single-valued fields only.
When you're mapping a one-to-many association you can handle this using has many relationships,
essentially using a foreign key for the single-valued end of the association.
But a many-to-many association can't do this because there is no single-valued end to
hold the foreign key.
The answer is the classic resolution that's been used by relational data people
for decades: create an extra table (an association table) to record the relationship.
The basic idea is using an association table to store the association. This table
has only the foreign key IDs for the two tables that are linked together, it has one
row for each pair of associated objects.
The association table has no corresponding in-memory object and its primary key is the
compound of the two primary keys of the tables that are associated.
In simple terms, to load data from the association table you perform two queries (in general, it may also be achieved using one query consisting of joins).
Consider loading the SkillRecord collection for a list PlayerRecord objects.
In this case, you do queries in two stages.
The first stage queries the Players table to find all the rows of the players you want.
The second stage finds the SkillRecord object for the related player ID for each row
in the Player_Skills association table using an inner join.
The Prado Active Record design implements the two stage approach. For the
Players-Skills M-N (many-to-many) entity relationship, we
define a many-to-many relationship in the PlayerRecord class and
in addition we may define a many-to-many relationship in the SkillRecord class as well.
The following sample code defines the complete SkillRecord class with a
many-to-many relationship with the PlayerRecord class. (See the PlayerRecord
class definition above to the corresponding many-to-many relationship with the SkillRecord class.)
class SkillRecord extends TActiveRecord
{
const TABLE='Skills';
public $skill_id;
public $name;
public $players=array(); // this declaration is no longer needed since v3.1.2
public static $RELATIONS=array
(
'players' => array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills'),
);
public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}
The static $RELATIONS property of SkillRecord defines that the
property $players has many PlayerRecords via an association table 'Player_Skills'.
In array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills'), the first element defines the
relationship type, in this case self::MANY_TO_MANY,
the second element is a string 'PlayerRecord' that corresponds to the
class name of the PlayerRecord class, and the third element is the name
of the association table name.
Note:
Prior to version 3.1.2 (versions up to 3.1.1), the many-to-many relationship was
defined using self::HAS_MANY. For version 3.1.2 onwards, this must be changed
to self::MANY_TO_MANY. This can be done by searching for the HAS_MANY in your
source code and carfully changing the appropriate definitions.
A list of player objects with the corresponding collection of skill objects may be fetched as follows.
$players = PlayerRecord::finder()->withSkills()->findAll();
The method with_xxx() (where xxx is the relationship property
name, in this case, Skill) fetches the corresponding SkillRecords using
a second query (not by using a join). The with_xxx() accepts the same
arguments as other finder methods of TActiveRecord.
Self Referenced Association Tables
For self referenced association tables, that is, the association points to the same
table. For example, consider the items table with M-N related
item via the related_items association table. The syntax in the following
example is valid for a PostgreSQL database. For other database, consult their respective documentation for
defining the foreign key constraints.
CREATE TABLE items
(
"item_id" SERIAL,
"name" VARCHAR(128) NOT NULL,
PRIMARY KEY("item_id")
);
CREATE TABLE "related_items"
(
"item_id" INTEGER NOT NULL,
"related_item_id" INTEGER NOT NULL,
CONSTRAINT "related_items_pkey" PRIMARY KEY("item_id", "related_item_id"),
CONSTRAINT "related_items_item_id_fkey" FOREIGN KEY ("item_id")
REFERENCES "items"("item_id")
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "related_items_related_item_id_fkey" FOREIGN KEY ("related_item_id")
REFERENCES "items"("item_id")
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE
);
The association table name in third element of the relationship array may
contain the foreign table column names. The columns defined in the association
table must also be defined in the record class (e.g. the $related_item_id property
corresponds to the related_item_id column in the related_items table).
class Item extends TActiveRecord
{
const TABLE="items";
public $item_id;
public $details;
//additional foreign item id defined in the association table
public $related_item_id;
public $related_items=array(); // this declaration is no longer needed since v3.1.2
public static $RELATIONS=array
(
'related_items' => array(self::MANY_TO_MANY,
'Item', 'related_items.related_item_id'),
);
}
Tip:
Compound keys in the foreign table can
be specified as comma separated values between brackets. E.g.
'related_items.(id1,id2)'.
Lazy Loading Related Objects
Note:
Implicitly declared related properties introduced in version 3.1.2 automatically have lazy
loading feature. Therefore, the lazy loading technique described in the following is no longer
needed in most of the cases, unless you want to manipulate the related objects through getter/setter.
Using the with_xxx() methods will load the relationship record on demand. Retrieving the
related record using lazy loading (that is, only when those related objects are accessed) can be
achieved by using a feature of the TComponent that provides accessor methods. In particular,
we define a pair of getter and setter methods where the getter method will retrieve the relationship
conditionally. The following example illustrates that the PlayerRecord can retrieve its
$skills foreign objects conditionally.
class PlayerRecord extends BaseFkRecord
{
//... other properties and methods as before
private $_skills; //change to private and default as null
public function getSkills()
{
if($this->_skills===null && $this->player_id !==null)
{
//lazy load the skill records
$this->setSkills($this->withSkills()->findByPk($this->player_id)->skills);
}
else if($this->_skills===null)
{
//create new TList;
$this->setSkills(new TList());
}
return $this->_skills;
}
public function setSkills($value)
{
$this->_skills = $value instanceof TList ? $value : new TList($value);
}
}
We first need to change the $skills=array() declaration to a private property
$_skills (notice the underscore) and set it to null instead. This allows us
to define the skills property using getter/setter methods
(see Components for details). The getSkills()
getter method for the skills property will lazy load the corresponding skills foreign record
when it is used as follows. Notice that we only do a lazy load when its $player_id is
not null (that is, when the record is already fetched from the database or player id was already set).
$player = PlayerRecord::finder()->findByPk(1);
var_dump($player->skills); //lazy load it on first access
var_dump($player->skills[0]); //already loaded skills property
$player->skills[] = new SkillRecord(); //add skill
The setSkills() ensures that the skills property will always be a TList.
Using a TList allows us to set the elements of the skills property as if they were
arrays. E.g. $player->skills[] = new SkillRecord(). If array was used, a PHP error
will be thrown.
Column Mapping
Since v3.1.1, Active Record starts to support column mapping. Column mapping allows developers
to address columns in Active Record using a more consistent naming convention. In particular,
using column mapping, one can access a column using whatever name he likes, rather than limited by
the name defined in the database schema.
To use column mapping, declare a static array named COLUMN_MAPPING in the Active Record class.
The keys of the array are column names (called physical column names) as defined in the database
schema, while the values are corresponding property names (called logical column names) defined
in the Active Record class. The property names can be either public class member variable names or
component property names defined via getters/setters. If a physical column name happens to be the same
as the logical column name, they do not need to be listed in COLUMN_MAPPING.
class UserRecord extends TActiveRecord
{
const TABLE='users';
public static $COLUMN_MAPPING=array
(
'user_id'=>'id',
'email_address'=>'email',
'first_name'=>'firstName',
'last_name'=>'lastName',
);
public $id;
public $username; // the physical and logical column names are the same
public $email;
public $firstName;
public $lastName;
//....
}
With the above column mapping, we can address first_name using $userRecord->firstName
instead of $userRecord->first_name. This helps separation of logic and model.
References
- Fowler et. al. Patterns of Enterprise Application Architecture,
Addison Wesley, 2002.
- B. Venners with B. Eckel. Inappropriate Abstractions - A Conversation with Anders Hejlsberg, Part VI.
Artima Developer, 2003.