ActiveRecord offers a convenient way to access and manipulate data stored in a database. This class is linked to a database table and represents a row of the linked table. Its attributes are the fields of the table and its methods allow us to perform common actions on database, such as selecting, inserting, or updating SQL statements.
Many common databases are supported by ActiveRecord, such as:
- Microsoft SQL Server
Also, some NoSQL databases are supported, such as:
ActiveRecord reads the table structure every time it is instanced and makes available table columns as its properties. Every change to the table structure is immediately available in the ActiveRecord object.
Therefore, if a table contains the fields
room_number, and if
$model is an instance of
yiidbActiveRecord, in order to access these fields, it will be enough to type:
$id = $model->id; $floor = $model->floor; $room_number = $model->room_numer;
ActiveRecord handles properties request with the
__get magic method and catches the respective content of a table column. In the previous paragraph, you saw how to create a model class from database tables to extend
yiidbActiveRecord with Gii. The syntax used by ActiveRecord is simple and redundant, so it is easy to remember. Now let’s look at how to query data from a database with ActiveRecord.
Data is fetched from a database through an
yiidbActiveQuery object to build the query, and finally calls on
all() methods to get an ActiveRecord object or a list of ActiveRecord objects.
An ActiveQuery object is returned from an ActiveRecord object by calling its static method
Room is a model (and subclasses ActiveRecord), an ActiveQuery will be returned from:
// $query is an ActiveQuery object $query = Room::find();
ActiveQuery objects provide methods to build the query with names such as in SQL expression.
The most common ones are:
where()to add conditions
orderBy()to apply an order
groupBy()to make aggregations
Almost all of these methods support a parameter that can be a string or an array. If it is a string, it will be passed exactly as it is to the SQL query; if it is an array, a key will be used as the column name, and a value as the corresponding value. For example, we want to build query to find a room on the first floor:
$query = Room::find()->where('floor = 1'); // equivalent to $query = Room::find()->where(['floor' => 1]);
For complex conditions,
where() supports the operator format where the condition is an array with:
[operator, operand1, operand2, …]
For example, we want to build a query to find a room on the first floor:
$query = Room::find()->where(['>=', 'floor', 1]); // equivalent to $query = Room::find()->where('floor >= 1';
Other conditions can be added using
orWhere(), by just using the
or logical link.
An array parameter of the
where() method is preferable to a string, because we can easily split the field name from its content and set the second parameter of the
where() method with an array with pair keys
=> values of parameters.
After creating a query object, to get data from an ActiveQuery, we will have:
one(): This method returns an ActiveRecord object or null if not found
all(): This method returns a list of ActiveRecord objects or an empty array if not found
So, to get rooms on the first floor, we must write:
$query = Room::find()->where(['floor' => 1]); $items = $query->all(); // equivalent to $items = Room::find()->where(['floor' => 1])->all();
There is a more concise syntax to fetch data from an ActiveRecord: the
findAll() methods, which return a single ActiveRecord or a list of ActiveRecords. The only difference from the previous methods is that they accept a single parameter, which can be:
- A number to filter by primary key
- An array of scalar values to filter by a list of primary key values (only for
findOne()returns a single ActiveRecord)
- An array of name-value pair to filter by a set of attribute values
Other common methods of ActiveRecord are:
validate(): This method is used to apply rules validation to attributes of a model
save(): This method is used to save a new model or to update one that already exists (if the
save()method is applied to a fetched ActiveRecord object)
delete(): This method is used to delete a model