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:
- MySQL
- PostgreSQL
- SQLite
- Oracle
- Microsoft SQL Server
Also, some NoSQL databases are supported, such as:
- Redis
- MongoDB
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 id, floor, and 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 one() or 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 ::find().
If 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 conditionsorderBy()to apply an ordergroupBy()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 andWhere() or orWhere(), by just using the and or 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 foundall(): 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();
Note
There is a more concise syntax to fetch data from an ActiveRecord: the findOne() and 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
findAll()becausefindOne()returns a single ActiveRecord) - An array of name-value pair to filter by a set of attribute values
Other common methods of ActiveRecord are:
In this example, we will query the rooms list using ActiveRecord and filter through the following fields: floor, room_number, and price_per_day with operators ( >=, <=, and =).
A data filter will take place using the SearchFilter container to encapsulate all of the filter data inside a single array.
Starting from a view, create a new file with the path basic/views/rooms/indexFiltered.php.
In this view, we will put the search filter on the top and then a table to display the results.
We have three fields to filter: floor, room_number, and price_per_day, all with an operator. The data filter will be passed to the controller and the filter selected will be kept after executing actionIndexFiltered in the controller.
This is the content of the view concerning the filtered form:
<?php
use yiihelpersUrl;
$operators = [ '=', '<=', '>=' ];
$sf = $searchFilter;
?>
<form method="post" action="<?php echo Url::to(['rooms/index-filtered']) ?>">
<input type="hidden" name="<?= Yii::$app->request->csrfParam; ?>" value="<?= Yii::$app->request->csrfToken; ?>" />
<div class="row">
<?php $operator = $sf['floor']['operator']; ?>
<?php $value = $sf['floor']['value']; ?>
<div class="col-md-3">
<label>Floor</label>
<br />
<select name="SearchFilter[floor][operator]">
<?php foreach($operators as $op) { ?>
<?php $selected = ($operator == $op)?'selected':''; ?>
<option value="<?=$op?>" <?=$selected?>><?=$op?></option>
<?php } ?>=
</select>
<input type="text" name="SearchFilter[floor][value]" value="<?=$value?>" />
</div>
<?php $operator = $sf['room_number']['operator']; ?>
<?php $value = $sf['room_number']['value']; ?>
<div class="col-md-3">
<label>Room Number</label>
<br />
<select name="SearchFilter[room_number][operator]">
<?php foreach($operators as $op) { ?>
<?php $selected = ($operator == $op)?'selected':''; ?>
<option value="<?=$op?>" <?=$selected?>><?=$op?></option>
<?php } ?>
</select>
<input type="text" name="SearchFilter[room_number][value]" value="<?=$value?>" />
</div>
<?php $operator = $sf['price_per_day']['operator']; ?>
<?php $value = $sf['price_per_day']['value']; ?>
<div class="col-md-3">
<label>Price per day</label>
<br />
<select name="SearchFilter[price_per_day][operator]">
<?php foreach($operators as $op) { ?>
<?php $selected = ($operator == $op)?'selected':''; ?>
<option value="<?=$op?>" <?=$selected?>><?=$op?></option>
<?php } ?>
</select>
<input type="text" name="SearchFilter[price_per_day][value]" value="<?=$value?>" />
</div>
</div>
<br />
<div class="row">
<div class="col-md-3">
<input type="submit" value="filter" class="btn btn-primary" />
<input type="reset" value="reset" class="btn btn-primary" />
</div>
</div>
</form>Note
Pay attention:
At the beginning of the view, there is a keyword use, which explains the complete path of the Url class. If we remove it, the framework will search the Url class requested in the <form> tag in the current namespace, that is app/controllers.
After declaring the <form> tag, we inserted:
<input type="hidden" name="<?= Yii::$app->request->csrfParam; ?>" value="<?= Yii::$app->request->csrfToken; ?>" />
This is mandatory to allow the framework to verify the sender of the post data.
The $searchFilter variable is used as $sf to provide a more concise form.
Now update RoomsController in basic/controllers/RoomsController.php and add a new action named actionIndexFiltered. Create an ActiveQuery object from Room and check whether there is content in the SearchFilter keyword of the $_POST array.
For every present filter, a condition will be added to $query using the andWhere method, passing an operator, field name, and value. For a more concise form of the actioned content, we put a filtered field in the loop, because they have the same redundant structure (operator and value):
public function actionIndexFiltered()
{
$query = Room::find();
$searchFilter = [
'floor' => ['operator' => '', 'value' => ''],
'room_number' => ['operator' => '', 'value' => ''],
'price_per_day' => ['operator' => '', 'value' => ''],
];
if(isset($_POST['SearchFilter']))
{
$fieldsList = ['floor', 'room_number', 'price_per_day'];
foreach($fieldsList as $field)
{
$fieldOperator = $_POST['SearchFilter'][$field]['operator'];
$fieldValue = $_POST['SearchFilter'][$field]['value'];
$searchFilter[$field] = ['operator' => $fieldOperator, 'value' => $fieldValue];
if( $fieldValue != '' )
{
$query->andWhere([$fieldOperator, $field, $fieldValue]);
}
}
}
$rooms = $query->all();
return $this->render('indexFiltered', [ 'rooms' => $rooms, 'searchFilter' => $searchFilter ]);
}Finally, we need to display the results in a table format. So at the bottom of the view, add a table to display the content of the filtered rooms (copied from basic/views/rooms/index.php):
<table class="table">
<tr>
<th>Floor</th>
<th>Room number</th>
<th>Has conditioner</th>
<th>Has tv</th>
<th>Has phone</th>
<th>Available from</th>
<th>Available from (db format)</th>
<th>Price per day</th>
<th>Description</th>
</tr>
<?php foreach($rooms as $item) { ?>
<tr>
<td><?php echo $item['floor'] ?></td>
<td><?php echo $item['room_number'] ?></td>
<td><?php echo Yii::$app->formatter->asBoolean($item['has_conditioner']) ?></td>
<td><?php echo Yii::$app->formatter->asBoolean($item['has_tv']) ?></td>
<td><?php echo ($item['has_phone'] == 1)?'Yes':'No' ?></td>
<td><?php echo Yii::$app->formatter->asDate($item['available_from']) ?></td>
<td><?php echo Yii::$app->formatter->asDate($item['available_from'], 'php:Y-m-d') ?></td>
<td><?php echo Yii::$app->formatter->asCurrency($item['price_per_day'], 'EUR') ?></td>
<td><?php echo $item['description'] ?></td>
</tr>
<?php } ?>
</table>Now point the browser to http://hostname/basic/web/rooms/index-filtered and this should be displayed:
A list of rooms with filters
We can create tests by changing the filter values and operators as much as we want.