Connections, models and active records are the foundations of everything that concerns database access and management. They are used to establish database connections, manage tables and their possible relationship, as well as manage the records of these tables. Leveraging OOP, the models and active records are instances which properties, getters/setters and behavior can be inherited in a business logic.
Using the query interface, you won't have to write raw SQL, manage table relationship, or worry about injection.
Finally, using providers you can define all your connections and models in a single place. Connections are established and models are instantiated on demand, so feel free the define hundreds of them.
The implementation of the query interface is vastly inspired by Ruby On Rails' Active Record Query Interface.
Unless you bound ActiveRecord to ICanBoogie using the icanboogie/bind-activerecord
package, you need to define the lazy getter for the activerecord_cache
property of
the Model class.
The following code should do the trick:
<?php
use ICanBoogie\ActiveRecord\RuntimeActiveRecordCache;
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\Prototype;
Prototype::from(Model::class)['lazy_get_activerecord_cache'] = function(Model $model) {
return new RuntimeActiveRecordCache($model);
};
A connection to a database is created with a Connection instance.
The following code demonstrates how a connection can be established to a MySQL database and a SQLite temporary database:
<?php
use ICanBoogie\ActiveRecord\Connection;
# a connection to a MySQL database
$connection = new Connection('mysql:dbname=example', 'username', 'password');
# a connection to a SQLite temporary database stored in memory
$connection = new Connection('sqlite::memory:');
The Connection class extends PDO. It takes the same parameters, and custom options can be provided with the driver options to specify a prefix to table names, specify the charset and collate of the connection or its timezone.
The ConnectionOptions::TABLE_NAME_PREFIX
option specifies the prefix for all the tables name
of the connection. Thus, if the icybee
prefix is defined the nodes
table is
renamed as icybee_nodes
.
The {table_name_prefix}
placeholder is replaced in queries by the prefix:
<?php
$statement = $connection('SELECT * FROM `{table_name_prefix}nodes` LIMIT 10');
The ConnectionOptions::CHARSET_AND_COLLATE
option specify the charset and the collate
of the connection in a single string e.g. "utf8/general_ci" for the "utf8" charset and the
"utf8_general_ci" collate.
The {charset}
and {collate}
placeholders are replaced in queries:
<?php
$connection('ALTER TABLE nodes CHARACTER SET "{charset}" COLLATE "{collate}"');
The ConnectionOptions::TIMEZONE
option specifies the time zone of the connection.
A model is an object-oriented representation of a database table, or a group of tables. A model is used to create, update, delete and query records. Models are instances of the Model class, and usually implement a specific business logic.
<?php
namespace App\Modules\Nodes;
use ICanBoogie\ActiveRecord;
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\ActiveRecord\ModelCollection;
class NodeModel extends Model
{
// …
}
class Node extends ActiveRecord
{
public $id;
public $title;
public $number;
// …
}
$models = new ModelCollection($connections, [
'nodes' => [
Model::ACTIVERECORD_CLASS => Node::class,
Model::SCHEMA => [
'id' => 'serial',
'title' => [ 'varchar', 80 ],
'number' => [ 'integer', 'unsigned' => true ]
]
]
]);
$models->install();
$node_model = $models['nodes'];
$node = Node::from([
'title' => "My first node",
'number' => 123
], [ $node_model ]);
// ^ because we don't use a model provider yet, we need to specify the model to the active record
# or
$node = $node_model->new([
'title' => "My first node",
'number' => 123
]);
$id = $node->save();
echo "Saved node, got id: $id\n";
The CONNECTION
key specifies the database connection, an instance of the Connection class.
The ACTIVERECORD_CLASS
key specifies the class used to instantiate the active records of the
model.
The NAME
key specifies the name of the table. If a table prefix is defined by the connection,
it is used to prefix the table name. The name
and unprefixed_name
properties returns the
prefixed name and original name of the table:
<?php
echo "table name: {$model->name}, original: {$model->unprefixed_name}.";
The {self}
placeholder is replaced in queries by the name
property:
<?php
$stmt = $model('SELECT * FROM `{self}` LIMIT 10');
The columns and properties of the table are defined with a schema, which is specified by the
SCHEMA
attribute. A key defines the name of the column, and a value defines the properties of
the column. Most column types use the following basic definition pattern:
'<identifier>' => '<type_and_default_options>'
# or
'<identifier>' => [ '<type>', <size> ];
The following types are available: blob
, char
, integer
, text
, varchar
,
bit
, boolean
, date
, datetime
, time
, timestamp
, year
, enum
, double
et float
. The
serial
and foreign
special types are used to define auto incrementing primary keys and foreign
keys:
<?php
[
'nid' => 'serial', // bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`nid`)
'uid' => 'foreign' // bigint(20) unsigned NOT NULL, KEY `uid` (`uid`)
];
The size of the field can be defined as an integer for the blob
, char
, integer
, varchar
and
bit
types:
<?php
[
'title' => 'varchar', // varchar(255) NOT NULL
'slug' => [ 'varchar', 80 ], // varchar(80) NOT NULL
'weight' => 'integer', // int(11) NOT NULL
'small_count' => [ 'integer', 8 ] // int(8) NOT NULL,
'price' => [ 'float', [ 10, 3 ] ] // float(10,3) NOT NULL
];
The size of the field can be defined using the qualifiers tiny
, small
, medium
,
big
or long
for the blob
, char
, integer
, text
and varchar
types:
<?php
[
'body' => [ 'text', 'long' ] // longtext NOT NULL
];
The qualifier null
specifies that a field can be null, by default fields are not capable
of receiving the null
:
[ 'varchar', 'null' => true ] // varchar(255)
[ 'integer', 'null' => true ] // int(11)
[ 'integer' ] // int(11) NOT NULL
The qualifier unsigned
specifies that a numeric value is not signed:
[ 'integer' ] // int(11)
[ 'integer', 'unsigned' => true ] // int(10) unsigned
The qualifier indexed
specifies that a field should be indexed:
[
'slug' => [ 'varchar', 'indexed' => true ], // varchar(255) NOT NULL, KEY `slug` (`slug`)
'is_online' => [ 'boolean', 'indexed' => true ] // tinyint(1) NOT NULL, KEY `is_online` (`is_online`),
'page_id' => [ 'foreign', 'indexed' => 'page-content' ], // bigint(20) unsigned NOT NULL
'content_id' => [ 'foreign', 'indexed' => 'page-content' ], // bigint(20) unsigned NOT NULL, KEY `page-content` (`page_id`, `content_id`)
];
The qualifier primary
specifies that a column is a primary key. A multi-column primary key is
created if multiple columns have the primary
qualifier:
[
'vtid' => [ 'foreign', 'primary' => true ],
'nid' => [ 'foreign', 'primary' => true ],
'weight' => [ 'integer', 'unsigned' => true ]
];
// ADD PRIMARY KEY ( `vtid` , `nid` )
Once the model has been defined, its associated table can easily be created with the install()
method.
<?php
$model->install();
The is_installed()
method checks if a model has already been installed.
Note: The method only checks if the corresponding table exists, not if its schema is correct.
<?php
if ($model->is_installed())
{
echo "The model is already installed.";
}
The following placeholders are replaced in model queries:
{alias}
: The alias of the table.{prefix}
: The prefix of the table names of the connection.{primary}
: The primary key of the table.{self}
: The name of the table.{self_and_related}
: The escaped name of the table and the possible JOIN clauses.
A model can extend another, just like a class can extend another in PHP. Fields are inherited and the primary key of the parent is used to link records together. When the model is queried, the tables are joined. When values are inserted or updated, they are split to update the various tables. Also, the connection of the parent model is inherited.
The EXTENDING
attribute specifies the model to extend.
<?php
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\DateTime;
$models = new ModelCollection($connections, [
'nodes' => [
Model::SCHEMA => [
'nid' => 'serial',
'title' => 'varchar'
]
],
'contents' => [
Model::EXTENDING => 'nodes',
Model::SCHEMA => [
'body' => 'text',
'date' => 'date'
]
],
'news' => [
Model::EXTENDING => 'contents'
]
];
$models['news']->save([
'title' => "Testing!",
'body' => "Testing...",
'date' => DateTime::now()
]);
Contrary to tables, models are not required to define a schema if they extend another model, but they may end with different parents.
In the following example the parent table of news
is nodes
but its parent model is
contents
. That's because news
doesn't define a schema and thus inherits the schema and some
properties of its parent model.
<?php
echo $news->parent->id; // nodes
echo $news->parent_model->id; // contents
Records of a model can belong to records of other models. For instance, a news article belonging
to a user. The relation is specified with the BELONGS_TO
attribute. When the belongs to
relation is specified, a getter is automatically added to the prototype of the records. For
instance, if records of a news
model belong to records of a users
model, than the get_user
getter is added to the prototype of the records of the news
model. The user of a news record
can then by obtained using the magic property user
.
<?php
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\ActiveRecord\ModelCollection;
$models = new ModelCollection($this->connections, [
'news' => [
Model::BELONGS_TO => 'users',
Model::SCHEMA => [
'news_id' => 'serial',
'uid' => 'foreign'
// …
]
],
'users' => [
Model::SCHEMA => [
'uid' => 'serial',
'name' => 'varchar'
// …
]
]
]);
$record = $news->one;
echo "{$record->title} belongs to {$record->user->name}.";
A one-to-many relation can be established between two models. For instance, an article having
many comments. The relation is specified with the HAS_MANY
attribute or the has_many()
method
of the parent model. A getter is added to the active record class of the model and returns a
Query instance when it is accessed.
The following example demonstrates how a one-to-many relation can be established between the "articles" and "comments" models, while creating the models:
<?php
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\ActiveRecord\ModelCollection;
// …
$models = new ModelCollection($connections, [
'comments' => [
Model::ACTIVERECORD_CLASS => Comment::class,
Model::SCHEMA => [
'comment_id' => 'serial',
'article_id' => 'foreign',
'body' => 'text'
]
],
'articles' => [
Model::ACTIVERECORD_CLASS => Article::class,
Model::HAS_MANY => 'comments',
Model::SCHEMA => [
'article_id' => 'serial',
'title' => 'varchar'
]
]
]);
The relation can also be established after the models are created using the has_many
method:
<?php
$articles = $models['articles'];
$comments = $models['comments'];
$articles->has_many($comments);
# or, if the model can be obtained with `get_model()`
$articles->has_many('comments');
# The local and foreign keys can be specified if they cannot be obtained from the models
$articles->has_many('comments', [ 'local_key' => 'article_id', 'related_key' => 'comment_id' ]);
# The name of the magic property can also be specified
$articles->has_many('comments', [ 'as' => 'article_comments' ]);
The following example demonstrates how all the comments of the author called "me" can be retrieved in order from an article:
<?php
$comments = $articles->one->comments->filter_by_author("me")->ordered->all;
An active record is an object-oriented representation of a record in a database. Usually, the table columns are its public properties and it is not unusual that getters/setters and business logic methods are implemented by its class.
The model managing the record needs to be specified when the instance is created. It can be
specified through the __construct
method as a model identifier or a Model instance, or by
defining the MODEL_ID
constant, which is the favorite method since it can be easily
introspected.
<?php
namespace App;
use ICanBoogie\ActiveRecord;
class Node extends ActiveRecord
{
const MODEL_ID = "nodes";
// …
protected function get_next()
{
return $this->model->own->visible->where('date > ?', $this->date)->order('date')->one;
}
protected function get_previous()
{
return $this->model->own->visible->where('date < ?', $this->date)->order('date DESC')->one;
}
// …
}
Active record are instantiated just like any other object, but the from()
method is
usually preferred for its shorter notation:
<?php
$record = Article::from([
'title' => "An example",
'body' => "My first article",
'language' => "en",
'is_online' => true
]);
Most properties of an active record are persistent. The save()
method is used to save the active
record to the database. The model is clever enough to filter the properties.
<?php
$record = $model[10];
$record->is_online = false;
$record->save();
Before a record is saved its alter_persistent_properties()
is invoked to alter the properties
that will be sent to the model. One may extend the method to add, remove or alter properties
without altering its instance.
The delete()
method deletes the active record from the database:
<?php
$record = $model[190];
$record->delete();
The package comes with three trait properties especially designed to handle DateTime instances: DateTimeProperty, CreatedAtProperty, UpdatedAtProperty. Using this properties you are guaranteed to always get a DateTime instance, no matter what value type is used to set the date and time.
<?php
namespace App;
use ICanBoogie\ActiveRecord;
class Node extends ActiveRecord
{
public $title;
use CreatedAtProperty;
use UpdatedAtProperty;
}
$node = new Node;
echo get_class($node->created_at); // ICanBoogie\Datetime
echo $node->created_at->is_empty; // true
$node->created_at = 'now';
echo $node->created_at; // 2014-02-21T15:00:00+0100
The query interface provides different ways to retrieve data from the database. Using the query interface you can find records using a variety of methods and conditions; specify the order, fields, grouping, limit, or the tables to join; use dynamic or scoped filters; check the existence or particular records; perform various calculations.
Queries often starts from a model, in the following examples the $model
variable is a reference
to a model managing nodes.
To retrieve objects and values from the database several finder methods are provided. Each of these methods defines the fragments of the database query. Complex queries can be created without having to write any raw SQL.
The methods are:
- where
- select
- group
- having
- order
- limit
- offset
- join
All of the above methods return a Query instance, allowing you to chain them.
Records can be retrieved in various ways, especially using the all
, one
, pairs
or rc
magic properties. The find()
method—used to retrieve a single record or a set of records—is the
most simple of them.
Retrieving a single record using its primary key is really simple. You can either use the find()
method of the model, or use the model as an array.
<?php
$article = $model->find(10);
# or
$article = $model[10];
Retrieving a set or records using their primary key is really simple too:
<?php
$articles = $model->find([ 10, 32, 89 ]);
# or
$articles = $model->find(10, 32, 89);
The RecordNotFound exception is thrown when a record could not be found. Its records
property
can be used to know which records could be found and which could not.
Note: The records of the set are returned in the same order they are requested, this also applies
to the records
property of the RecordNotFound exception.
Records retrieved using find()
are cached, they are reused by subsequent calls. This also
applies to the array notation.
<?php
$article = $model[12]; // '12' retrieved from database
$articles = $model->find(11, 12, 13); // '11' and '13' retrieved from database, '12' is reused.
The where()
method specifies the conditions used to filter the records. It represents
the WHERE
-part of the SQL statement. Conditions can either be specified as a string, as a
list of arguments or as an array.
Adding a condition to a query can be as simple as $model->where('is_online = 1');
. This
would return all the records where the is_online
field equals "1".
Warning: Building you own conditions as string can leave you vulnerable to SQL injection
exploits. For instance, $model->where('is_online = ' . $_GET['online']);
is not safe. Always
use placeholders when you can't trust the source of your inputs:
<?php
$model->where('is_online = ?', $_GET['online']);
Of course you can use multiple conditions:
<?php
$model->where('is_online = ? AND is_home_excluded = ?', $_GET['online'], false);
and()
is alias to where()
and should be preferred when linking adding conditions:
<?php
$model->where('is_online = ?', $_GET['online'])->and('is_home_excluded = ?', false);
Conditions can also be specified as arrays:
<?php
$model->where([ 'is_online' => $_GET['online'], 'is_home_excluded' => false ]);
Records belonging to a subset can be retrieved using an array as condition value:
<?php
$model->where([ 'orders_count' => [ 1,3,5 ] ]);
This generates something like: ... WHERE (orders_count IN (1,3,5))
.
When conditions are specified as an array it is possible to modify the comparing function. Prefixing a field name with an exclamation mark uses the not equal operator.
The following example demonstrates how to search for records where the order_count
field is
different than "2":
<?php
$model->where([ '!order_count' => 2 ]);
… WHERE `order_count` != 2
This also works with subsets:
<?php
$model->where([ '!order_count' => [ 1,3,5 ] ]);
… WHERE `order_count` NOT IN(1, 3, 5)
Conditions can also be specified as methods, prefixed by filter_by_
and separated by _and_
:
<?php
$model->filter_by_slug('creer-nuage-mots-cle');
$model->filter_by_is_online_and_uid(true, 3);
Is equivalent to:
<?php
$model->where([ 'slug' => 'creer-nuage-mots-cle' ]);
$model->where([ 'is_online' => true, 'uid' => 3 ]);
Scopes can be viewed as model defined filters. Models can define their own filters,
inherit filters from their parent class and override them. For instance, this is how a
similar_site
, similar_language
and visible
scopes could be defined:
<?php
namespace Website\Nodes;
use ICanBoogie\Core;
use ICanBoogie\ActiveRecord\Query;
class Model extends \ICanBoogie\ActiveRecord\Model
{
// …
protected function scope_similar_site(Query $query, $site_id = null)
{
return $query->and('site_id = 0 OR site_id = ?', $site_id !== null ? $site_id : Core::get()->site->site_id);
}
protected function scope_similar_language(Query $query, $language = null)
{
return $query->and('language = "" OR language = ?', $language !== null ? $language : Core::get()->site->language);
}
protected function scope_visible(Query $query, $visible = true)
{
return $query->similar_site->similar_language->filter_by_is_online($visible);
}
// …
}
Now you can easily retrieve the first ten records that are visible on your website:
<?php
$model->visible->limit(10);
Or retrieve the first ten French records:
<?php
$model->similar_language('fr')->limit(10);
The order()
method retrieves records in a specific order.
The following example demonstrates how to get records in the ascending order of their creation date:
<?php
$model->order('created');
A direction can be specified:
<?php
$model->order('created ASC');
# or
$model->order('created DESC');
Multiple fields can be used while ordering:
<?php
$model->order('created DESC, title');
Records can also be ordered by field:
<?php
$model->where([ 'nid' => [ 1, 2, 3 ] ])->order('nid', [ 2, 3, 1 ]);
# or
$model->where([ 'nid' => [ 1, 2, 3 ] ])->order('nid', 2, 3, 1);
The group()
method specifies the GROUP BY
clause.
The following example demonstrates how to retrieve the first record of records grouped by day:
<?php
$model->group('date(created)')->order('created');
The having()
method specifies the HAVING
clause, which specifies the conditions of the
GROUP BY
clause.
The following example demonstrates how to retrieve the first record created by day for the past month:
<?php
$model->group('date(created)')->having('created > ?', new DateTime('-1 month'))->order('created')
The limit()
method limits the number of records to retrieve.
<?php
$model->limit(10); // retrieves the first 10 records
With two arguments, an offset can be specified:
<?php
$model->limit(5, 10); // retrieves records from the 6th to the 16th
The offset can also be defined using the offset()
method:
<?php
$model->offset(5); // retrieves records from the 6th to the last
$model->limit(10)->offset(5);
By default all fields are selected (SELECT *
) and records are instances of the ActiveRecord
class defined by the model. The select()
method selects only a subset of fields from
the result set, in which case each row of the result set is returned as an array, unless a fetch
mode is defined.
The following example demonstrates how to get the identifier, creation date and title of records:
<?php
$model->select('nid, created, title');
Because the SELECT
string is used as is to build the query, complex SQL statements can be
used:
<?php
$model->select('nid, created, CONCAT_WS(":", title, language)');
The join()
method specifies the JOIN
clause. A raw string or a model identifier
can be used to specify the join. The method can be used multiple times to create multiple joints.
A Query instance can be joined as a subquery. The following options are available:
mode
: Specifies the join mode. Default:INNER
.as
: Alias for the subquery. Default: The alias of the model associated with the query.on
: The column used for the conditional expression. Depending on the columns available, the method tries to determine the best solution betweenON
andUSING
.
The following example demonstrates how to fetch users and order them by the number
of online article they published since last year. We use the join mode LEFT
so that users
that did not publish articles are fetched as well.
<?php
// …
$online_article_count = $models['articles']
->select('user_id, COUNT(node_id) AS online_article_count')
->filter_by_type_and_created_at('articles', new DateTime('-1 year'))
->online
->group('user_id');
$users = $models['users']
->join($online_article_count, [ 'on' => 'user_id', 'mode' => 'LEFT' ])
->order('online_article_count DESC)';
A join can be specified using a model or a model identifier, in which case the relationship between that model and the model associated with the query is used to create the join. The following options are available:
mode
: Specifies the join mode. Default:INNER
.as
: Alias for the joining model. Default: The alias of the joining model.
The column character ":" is used to distinguish a model identifier from a raw fragment.
<?php
$model->join($contents_model);
# or
$model->join(':contents');
$model->join(':contents', [ 'mode' => 'LEFT', 'as' => 'cnt' ]);
Note: If a model identifier is provided, the model collection associated with the query's model is used to obtain the model.
Finally, a join can be specified using a raw string, which will be included as is in the final SQL statement.
<?php
$model->join('INNER JOIN `contents` USING(`nid`)');
There are many ways to retrieve data. We have already seen the find()
method, which can be used
to retrieve records using their identifier. The following methods or magic properties work with
conditions.
Instances of Query are traversable, it's the easiest way the retrieve the rows of a result set:
<?php
foreach ($model->where('is_online = 1') as $node)
{
// …
}
The magic property all
retrieves the complete result set as an array:
<?php
$array = $model->all;
$array = $model->visible->order('created DESC')->all
The all()
method retrieves the complete result set using a specific fetch mode:
<?php
$array = $model->all(\PDO::FETCH_ASSOC);
$array = $model->visible->order('created DESC')->all(\PDO::FETCH_ASSOC)
The one
magic property retrieves a single record:
<?php
$record = $model->one;
$record = $model->visible->order('created DESC')->one
The one()
method retrieves a single record using a specific fetch mode:
<?php
$record = $model->one(\PDO::FETCH_ASSOC);
$record = $model->visible->order('created DESC')->one(\PDO::FETCH_ASSOC);
Note: The number of records to retrieve is automatically limited to 1.
The pairs
magic property retrieves key/value pairs when selecting two columns, the first column
is the key and the second its value.
<?php
$model->select('nid, title')->pairs;
Results are similar to the following example:
array
34 => string 'Créer un nuage de mots-clé' (length=28)
57 => string 'Générer à la volée des miniatures avec mise en cache' (length=56)
307 => string 'Mes premiers pas de développeur sous Ubuntu 10.04 (Lucid Lynx)' (length=63)
...
The rc
magic property retrieves the first column of the first row.
<?php
$title = $model->select('title')->rc;
Note: The number of records to retrieve is automatically limited to 1.
The fetch mode is usually selected by the query interface but the mode
can be used to specify
it.
<?php
$model->select('nid, title')->mode(\PDO::FETCH_NUM);
The mode()
method accepts the same arguments as the
PDOStatement::setFetchMode method.
As we have seen in previous examples, the fetch mode can also be specified when fetching data
with the all()
and one()
methods.
<?php
$array = $model->order('created DESC')->all(\PDO::FETCH_ASSOC);
$record = $model->order('created DESC')->one(\PDO::FETCH_ASSOC);
The exists()
method checks the existence of a record, it queries the database just like find()
but returns true
when a record is found and false
otherwise.
<?php
$model->exists(1);
The method accepts multiple identifiers in which case it returns true
when all the
records exist, false
when all the record don't exist, and an array otherwise.
<?php
$model->exists(1, 2, 999)
# or
$model->exists([ 1, 2, 999 ]);
The method would return the following result if records "1" and "2" exist but not record "999".
array
1 => boolean true
2 => boolean true
999 => boolean false
The exists
magic property is true
if at least one record matching the specified conditions
exists, false
otherwise.
<?php
$model->filter_by_author('Madonna')->exists;
The exists
magic property of the model is true
if the modal has at least one record, false
otherwise.
<?php
$model->exists;
The count
magic property is the number of records in a model or matching a query.
<?php
$model->count;
Or on a query:
<?php
$model->filter_by_firstname('Ryan')->count;
Of course, all query methods can be combined:
<?php
$model->filter_by_firstname('Ryan')->join(':content')->where('YEAR(date) = 2011')->count;
The count()
method returns an array with the number of recond for each value of a field:
<?php
$model->count('is_online');
array
0 => string '35' (length=2)
1 => string '145' (length=3)
In this example, there are 35 record online and 145 offline.
The average()
, minimum()
, maximum()
and sum()
methods are respectively used, for a column,
to compute its average value, its minimum value, its maximum value and its sum.
All calculation methods work directly on the model:
<?php
$model->average('price');
And on a query:
<?php
$model->filter_by_category('Toys')->average('price');
Of course, all query methods can be combined:
<?php
$model->filter_by_category('Toys')->join(':content')->where('YEAR(date) = 2011')->average('price');
The following properties might be helpful, especially when you are using the Query interface to create a query string to be used in the subquery of another query:
conditions
: The conditions rendered as a string.conditions_args
: The arguments to the conditions.model
: The model associated with the query.
The following example demonstrates how a query on some taxonomy models can be used as a subquery to obtain only the online articles in a "music" category:
<?php
// …
$taxonomy_query = $models['taxonomy.terms/nodes']
->join(':taxonomy.vocabulary')
->join(':taxonomy_vocabulary/scopes')
->where([
'termslug' => "music",
'vocabularyslug' => "category",
'constructor' => "articles"
])
->select('nid');
$articles = $models['articles']
->filter_by_is_online(true)
->and("nid IN ($taxonomy_query)", $taxonomy_query->conditions_args)
->all;
# or
$articles = $models['articles']
->filter_by_is_online_and_nid(true, $taxonomy_query)
->all;
The records matching a query can be deleted using the delete()
method:
<?php
$models['nodes']
->filter_by_is_deleted_and_uid(true, 123)
->limit(10)
->delete();
You might need to join tables to decide which record to delete, in which case you might want to define in which tables the records should be deleted. The following example demonstrates how to delete the nodes and comments of nodes belonging to user 123 and marked as deleted:
<?php
$models['comments']
->filter_by_is_deleted_and_uid(true, 123)
->join(':nodes')
->delete('comments, nodes');
When using join()
the table associated with the query is used by default. The following
example demonstrates how to delete nodes that lack content:
<?php
$models['nodes']
->join(':contents', [ 'mode' => 'LEFT' ])
->where('content.nid IS NULL')
->delete()
Retrieving records:
<?php
$record = $model[10];
# or
$record = $model->find(10);
$records = $model->find(10, 15, 19);
# or
$records = $model->find([ 10, 15, 19 ]);
Conditions:
<?php
$model->where('is_online = ?', true);
$model->where([ 'is_online' => true, 'is_home_excluded' => false ]);
$model->where('site_id = 0 OR site_id = ?', 1)->and('language = '' OR language = ?', "fr");
# Sets
$model->where([ 'order_count' => [ 1, 2, 3 ] ]);
$model->where([ '!order_count' => [ 1, 2, 3 ] ]); # NOT
# Dynamic filters
$model->filter_by_nid(1);
$model->filter_by_site_id_and_language(1, 'fr');
# Scopes
$model->visible;
$model->own->visible->ordered;
Grouping and ordering:
<?php
$model->group('date(created)')->order('created');
$model->group('date(created)')->having('created > ?', new DateTime('-1 month'))->order('created');
Limits and offsets:
<?php
$model->limit(10); // first 10 records
$model->limit(5, 10); // 6th to the 16th records
$model->offset(5); // from the 6th to the last
$model->offset(5)->limit(10);
Fields selection:
<?php
$model->select('nid, created, title');
$model->select('nid, created, CONCAT_WS(":", title, language)');
Joins:
<?php
$model->join($subquery, [ 'on' => 'nid' ]);
$model->join(':contents');
$model->join('INNER JOIN contents USING(nid)');
Retrieving data:
<?php
$model->all;
$model->order('created DESC')->all(PDO::FETCH_ASSOC);
$model->order('created DESC')->mode(PDO::FETCH_ASSOC)->all;
$model->order('created DESC')->one;
$model->select('nid, title')->pairs;
$model->select('title')->rc;
Testing object existence:
<?php
$model->exists;
$model->exists(1, 2, 3);
$model->exists([ 1, 2, 3 ]);
$model->where('author = ?', 'madonna')->exists;
Calculations:
<?php
$model->count;
$model->count('is_online'); // count is_online = 0 and is_online = 1
$model->filter_by_is_online(true)->count; // count is_online = 1
$model->average('score');
$model->minimum('age');
$model->maximum('age');
$model->sum('comments_count');
Providers are included to manage connections and models.
The connections provider manages database connections.
Connection definitions can be specified while creating the ConnectionCollection instance.
<?php
use ICanBoogie\ActiveRecord\ConnectionCollection;
$connections = new ConnectionCollection
([
'one' => [
'dsn' => 'sqlite::memory:'
],
'bad' => [
'dsn' => 'mysql:dbname=bad_database' . uniqid()
]
]);
Or after:
<?php
$connections['two'] = [
'dsn' => 'mysql:dbname=example',
'username' => 'root',
'password' => 'root'
];
You can modify a connection definition until it is established. A ConnectionAlreadyEstablished exception is thrown in attempt to modify the definition of an already established connection.
ConnectionCollection instances are used as arrays. For instance, this is how you obtain a Connection instance, which represents a database connection:
<?php
$one = $connections['one'];
Database connections are created on demand, so that you can define a hundred of them and they will only be established when needed.
A ConnectionNotDefined exception is thrown in attempt to obtain a connection that is not defined.
Because ConnectionCollection instances are used as arrays, the isset()
function is used to check
if a connection is defined.
<?php
if (isset($connections['one']))
{
echo "The connection 'one' is defined.\n";
}
The definitions
magic property returns the current connection definitions. The property is
read-only.
<?php
foreach ($connections->definitions as $id => $definition)
{
echo "The connection '$id' is defined.\n";
}
An array with the established connections can be retrieved using the established
magic property.
The property is read-only.
<?php
foreach ($connections->established as $id => $connection)
{
echo "The connection '$id' is established.\n";
}
The ConnectionCollection instance itself can be used to traverse established connections.
<?php
foreach ($connections as $id => $connection)
{
echo "The connection '$id' is established.\n";
}
The models provider manages models.
Model definitions can be specified while creating the ModelCollection instance.
Note: You don't have to create the Connection instances used by the models, you can use their identifier which will get resolved when the model is needed.
Note: If CONNECTION
is not specified the primary
connection is used.
<?php
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\ActiveRecord\ModelCollection;
$models = new ModelCollection($connections, [
'nodes' => [
// …
Model::SCHEMA => [
'nid' => 'serial',
'title' => 'varchar'
// …
]
],
'contents' => [
// …
Model::EXTENDING => 'nodes'
]
]);
Model definitions can be modified or added after the ModelCollection instance has been created.
<?php
use ICanBoogie\ActiveRecord\Model;
$models['new'] = [
// …
Model::EXTENDING => 'contents'
];
You can modify the definition of a model until it is instantiated. A ModelAlreadyInstantiated exception is thrown in attempt to modify the definition of an already instantiated model.
Use the ModelCollection instance as an array to obtain a Model instance.
<?php
$nodes = $models['nodes'];
Models are instantiated on demand, so that you can define a hundred models an they will only by instantiated, along with their database connection, when needed.
A ModelNotDefined exception is thrown in attempts to obtain a model which is not defined.
The isset()
function checks if a model is defined.
<?php
if (isset($models['nodes']))
{
echo "The model 'node' is defined.\n";
}
The definitions
magic property returns the current model definitions. The property is
read-only.
<?php
foreach ($models->definitions as $id => $definition)
{
echo "The model '$id' is defined.\n";
}
An array with the instantiated models can be retrieved using the instances
magic property. The
property is read-only.
<?php
foreach ($models->instances as $id => $model)
{
echo "The model '$id' has been instantiated.\n";
}
All the models managed by the provider can be installed and uninstalled with a single command
using the install()
and uninstall()
methods. The is_installed()
method returns an array
of key/value pair where key is a model identifier and value true
if the model is
installed, false
otherwise.
<?php
$models->install();
var_dump($models->is_installed()); // [ "nodes" => true, "contents" => true ]
$models->uninstall();
var_dump($models->is_installed()); // [ "nodes" => false, "contents" => false ]
By default, each model uses an instance of RuntimeActiveRecordCache to cache its records.
This cache stores the records for the duration of the request, it is brand new with each HTTP
request. The cache is obtained using the prototype features of the model, through the
activerecord_cache
magic property.
Third parties can provide a different cache instance simply by overriding the
lazy_get_activerecord_cache
method:
<?php
use ICanBoogie\ActiveRecord\Model;
use ICanBoogie\Prototype;
Prototype::from('ICanBoogie\ActiveRecord\Model')['lazy_get_activerecord_cache'] = function(Model $model) {
return new MyActiveRecordCache($model);
};
Or using a prototype
configuration fragment:
<?php
// config/prototype.php
return [
'ICanBoogie\ActiveRecord\Model::lazy_get_activerecord_cache' => 'my_activerecord_cache_provider'
];
The exception classes defined by the package implement the ICanBoogie\ActiveRecord\Exception
interface so that they can easily be identified:
<?php
try
{
// …
}
catch (\ICanBoogie\ActiveRecord\Exception $e)
{
// an ActiveRecord exception
}
catch (\Exception $e)
{
// some other exception
}
The following exceptions are defined:
- ConnectionAlreadyEstablished: Exception thrown in attempt to set/unset the definition of an already established connection.
- ConnectionNotDefined: Exception thrown in attempt to obtain a connection that is not defined.
- ConnectionNotEstablished: Exception thrown when a connection cannot be established.
- ModelAlreadyInstantiated: Exception thrown in attempt to set/unset the definition of an already instantiated model.
- ModelNotDefined: Exception thrown in attempt to obtain a model that is not defined.
- RecordNotFound: Exception thrown when one or several records cannot be found.
- RelationNotDefined: Exception thrown in attempt to obtain a relation that is not defined.
- ScopeNotDefined: Exception thrown in attempt to obtain a scope that is not defined.
- StatementInvocationFailed: Exception thrown when invoking a statement fails (
execute()
returnedfalse
). - StatementNotValid: Exception thrown in attempt to execute a statement that is not valid.
- UnableToSetFetchMode: Exception thrown when the fetch mode of a statement fails to be set.
The get_model()
helper retrieves models using their identifier. It is used by active
records to retrieve their model when required, and by queries during joins. You need to patch
this helper according to your application logic because the default implementation only throws
\RuntimeException
.
In the following example, the get_model()
helper is patched to retrieve models from a provider
similar to the one we've seen in previous examples.
<?php
use ICanBoogie\ActiveRecord;
use ICanBoogie\ActiveRecord\Helpers;
Helpers::patch('get_model', function($id) use($models) {
return $models[$id];
});
$nodes = ActiveRecord\get_model('nodes');
The package requires PHP 7.0 or later and the PDO extension.
The recommended way to install this package is through Composer:
$ composer require icanboogie/activerecord
The package is available on GitHub, its repository can be cloned with the following command line:
$ git clone https://github.com/ICanBoogie/ActiveRecord.git
The package is documented as part of the ICanBoogie framework
documentation. You can generate the documentation for the package and its dependencies with the make doc
command. The documentation is generated in the build/docs
directory. ApiGen is required. The directory can later be cleaned with the make clean
command.
The test suite is ran with the make test
command. PHPUnit and Composer need to be globally available to run the suite. The command installs dependencies as required. The make test-coverage
command runs test suite and also creates an HTML coverage report in "build/coverage". The directory can later be cleaned with the make clean
command.
The package is continuously tested by Travis CI.
icanboogie/activerecord is licensed under the New BSD License - See the LICENSE file for details.