Using ActiveRecord to manipulate data in yii framework – part 3

This tutorial is part of hotel reservation series try out part 1 and part 2 first

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.

Recommended :  Create an E-Commerce Website with laravel 5.2

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 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 andWhere() or orWhere(), by just using the and or or logical link.

Recommended :  Don't wait any longer with queues In Laravel 5.1

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();

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() because findOne() returns a single ActiveRecord)
  • An array of name-value pair to filter by a set of attribute values
Recommended :  Create a Discovery app with yii framework

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
prevChapter 1 of 5

About the author

Deven Rathore

I'm Deven Rathore, a multidisciplinary & self-taught designer with 3 years of experience. I'm passionate about technology, music, coffee, traveling and everything visually stimulating. Constantly learning and experiencing new things.

Pin It on Pinterest

Shares