How to Program With Yii2: ActiveRecord

Admin 2017-03-23 19:40:48
Final product image
What You'll Be Creating

If you're asking, "What's Yii?" check out my earlier tutorial: Introduction to the Yii Framework, which reviews the benefits of Yii and includes an overview of the changes in Yii 2.0, released in October 2014.

In this Programming With Yii2 series, I'm guiding readers in use of the Yii2 Framework for PHP. In today's tutorial, I'll walk you through using Yii's object-relational mapping, known as ORM, for working with databases. It's called Active Record and is a key aspect of programming database applications efficiently in Yii.

Yii offers different ways to work with your database programmatically, such as direct queries and a query builder, but using Active Record offers a complete set of benefits for object-oriented database programming. Your work becomes more efficient, more secure, works within Yii's model view controller architecture, and is portable should you decide to switch database platforms (e.g. MySQL to PostgreSQL).

Follow along as I detail the basics of Active Record within Yii.

Just a reminder, I do participate in the comment threads below. I'm especially interested if you have different approaches, additional ideas, or want to suggest topics for future tutorials. If you have a question or topic suggestion, please post below. You can also reach me on Twitter @reifman directly.

What's Active Record?

Yii's model view controller is one of its key benefits. Active Record provides an object-oriented solution for working with your databases which is closely integrated with Yii models. According to Wikipedia, the general term Active Record was "named by Martin Fowler in his 2003 book Patterns of Enterprise Application Architecture."

The Yii documentation summarizes this concisely:

An Active Record class is associated with a database table, an Active Record instance corresponds to a row of that table, and an attribute of an Active Record instance represents the value of a particular column in that row. Instead of writing raw SQL statements, you would access Active Record attributes and call Active Record methods to access and manipulate the data stored in database tables.

The integration of the Active Record patterning into Yii is a great strength of the framework, but common to most frameworks such as Ruby on Rails. 

This abstraction from models to database tables allows the framework to perform the heavy lifting of security everywhere, e.g. breaking down SQL injection queries.

Yii's Active Record support also provides portability across a number of databases. You can switch databases without likely needing to change a lot of code:

  • MySQL 4.1 or later
  • PostgreSQL 7.3 or later
  • SQLite 2 and 3
  • Microsoft SQL Server 2008 or later
  • CUBRID 9.3 or later
  • Oracle
  • Sphinx: via yii\sphinx\ActiveRecord, requires the yii2-sphinx extension
  • ElasticSearch: via yii\elasticsearch\ActiveRecord, requires the yii2-elasticsearch extension

And the following NoSQL databases:

Learning the Basics

In the earlier episode, How to Program With Yii2: Working With the Database and Active Record, I walked through creating your database, how Yii connects to it for each session, using a migration to create database tables, and using Gii (Yii's helpful code scaffolding generator) to create default model code. If you're unfamiliar with any of this, please review that episode.

In this episode, I'll focus more on leveraging Active Record in your code.

Declaring an Active Record Class in a Model

First, let me review how to transform a Yii model to leverage Active Record. I'll use an example model I created in the Building Your Startup series. That series guides you through how I'm building my startup, Meeting Planner, in Yii2.

I'll use the example of a simple model I created called Launch, which allows home page visitors to provide their email address if they want to be notified when the product is out of preview and fully released.

Yii2 Programming Active Record - Example of Home Page Launch Email Gathering

Using Active Record with a model is quite simple; notice the class Launch extends \yii\db\ActiveRecord:

<?php

namespace frontend\models;

use Yii;
use yii\db\ActiveRecord;


/**
 * This is the model class for table "launch".
 *
 * @property integer $id
 * @property string $email
 * @property string $ip_addr
 * @property integer $status
 * @property integer $created_at
 * @property integer $updated_at
 */
class Launch extends \yii\db\ActiveRecord
{
  const STATUS_REQUEST =0;
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'launch';
    }

That's it.

Building Queries

Let's look at some common Active Record queries. 

Individual Records

If you have a record ID, often from a query parameter from a controller, it's easy to find the record you want:

public function actionSomething($id)
{
  $model = Launch::findOne($id);

This is identical to:

$model = Launch::find()
    ->where(['id' => $id])
    ->one();

You can also extend the ->where array with more fields or boolean conditions:

$model = Launch::find()
    ->where(['id' => $id,'status'=>Launch::ACTIVE_REQUEST])
    ...
    
//equivalent to
$model = Launch::find()
    ->where(['id' => $id)
    ->andWhere(['status'=>Launch::ACTIVE_REQUEST])
    ->orWhere(['status'=>Launch::FUTURE_REQUEST])
    ...

Multiple Records

Here's an example of finding all records that match a specific status sorted by $id:

$people = Launch::find()
  ->where(['status' => Launch::STATUS_REQUEST])
  ->orderBy('id')
  ->all();

The ->all(); finds all records instead of only one. The variable $people is returned as an array of model objects. Alternately, when there are no conditions, you can access all records with ->findAll();

Returning an Array

Using indexBy returns an array of items indexed by their id:

$people = Launch::find()
    ->indexBy('id')
    ->all();

Alternately, you can return an associative array with ->asArray():

$people = Launch::find()
    ->asArray()
    ->all();

Note: Yii's documentation says, "While this method saves memory and improves performance, it is closer to the lower DB abstraction layer and you will lose most of the Active Record features."

Counting Records

You can also return just a count from a query:

$count = Launch::find()
    ->where(['status' => Launch::STATUS_REQUEST])
    ->count();

I use counts a lot in Meeting Planner for statistics for example; learn more in our Dashboard episode:

// calculate  $count_meetings_completed
$hd->count_meetings_completed = Meeting::find()->where(['status'=>Meeting::STATUS_COMPLETED])->andWhere('created_at<'.$since)->count();;
// calculate  $count_meetings_expired
$hd->count_meetings_expired = Meeting::find()->where(['status'=>Meeting::STATUS_EXPIRED])->andWhere('created_at<'.$since)->count();;
// calculate  $count_meetings_planning
$hd->count_meetings_planning = Meeting::find()->where('status<'.Meeting::STATUS_COMPLETED)->andWhere('created_at<'.$since)->count();;
// calculate  $count_places
$hd->count_places = Place::find()->where('created_at>'.$after)->andWhere('created_at<'.$since)->count();

Accessing the Data

Once you've queried data, such as an individual model, it's easy to access the data as a model object:

$model = Launch::findOne($id);  
$id = $model->id;
$email = $model->email;

I often process arrays this way:

$users = User::findAll();
foreach ($users as $u) {
 $id = $u->id;
 $email = $u->email;

Massive Assignment

You can also quickly assign an array to a model record via ActiveRecord:

$values = [
    'name' => 'James',
    'email' => '[email protected]',
];
$customer = new Customer();
$customer->attributes = $values;
$customer->save();

This is often used for populating model data after a form submission:

if (isset($_POST['FormName'])) {
    $model->attributes = $_POST['FormName'];
    if ($model->save()) {
        // handle success
    }
}

Or you can use ->load() for this:

if ($model->load(Yii::$app->request->post()) && $model->save()) {
...
}

Yii's Gii scaffolding code generator is great at generating models using ActiveRecord that do a lot of this for you, e.g. models, controllers, forms, views, etc.

Saving Data

As you can see above, saving data with Active Record is easy as well. In this example from the Yii documentation, a new record is created and saved—and then a record is loaded by id, and updates are saved:

// insert a new row of data
$customer = new Customer();
$customer->name = 'James';
$customer->email = '[email protected]';
$customer->save();

// update an existing row of data
$customer = Customer::findOne(123);
$customer->email = '[email protected]';
$customer->save();

Deleting Records

Deleting a record is even easier:

$u = User::findOne(99);
$u->delete();

Updating Counters

Yii also offers easy counter increments. Let's say a user schedules another meeting, and I'm tracking how many in the user table:

$u = User::findOne(99);
$u->updateCounters(['meeting_count'=>1]);
// equivalent to
// UPDATE `User` SET `meeting_count` = `meeting_count` + 1 WHERE `id` = 99

Relations

Connecting tables across indexes is one of Active Record's most powerful capabilities. For example, in Meeting Planner, each meeting may have 0 or more MeetingPlaces. The Meeting.php model defines a relational ActiveQuery for this:

* @property MeetingPlace[] $meetingPlaces
/**
 * @return \yii\db\ActiveQuery
 */
public function getMeetingPlaces()
{
    return $this->hasMany(MeetingPlace::className(), ['meeting_id' => 'id']);
}

Then, I can access all a meeting's places with the $meetingPlaces property. Below, I load a meeting and iterate over all of its meetingPlaces quite easily as if it was a built-in array of sub-objects:

$mtg=Meeting::find()->where(['id'=>$meeting_id])->one();
foreach ($mtg->meetingPlaces as $mp) {
  ...
}

Of course, this relies on creating a foreign key when you create the table in its migration:

$this->createTable('{{%meeting_place}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'place_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'suggested_by' => Schema::TYPE_BIGINT.' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_meeting_place_meeting',
        '{{%meeting_place}}', 'meeting_id', '{{%meeting}}',
        'id', 'CASCADE', 'CASCADE');

What's Next

I hope this provided an easy introduction to some of Active Record's awesomeness. It also includes Life Cycles, Transactions, and Locking, which I may write about in the future. If you want to jump ahead, Yii2 offers two great areas for learning more in its documentation: Yii2 Guide to Active Record and Yii2 Active Record functional specifications. These are well-written introductions.

Watch for upcoming tutorials in the Programming With Yii2 series as we continue diving into different aspects of the framework. You may also want to check out the aforementioned Building Your Startup With PHP series.

If you'd like to know when the next Yii2 tutorial arrives, follow me @reifman on Twitter or check my instructor page. 

Related Links

Tags:

Comments

Related Articles

How to Use the Latest Updates to the Marketplace API

How to Use the Latest Updates to the Marketplace A...

Object-Oriented Programming in WordPress: Building the Plugin I

Object-Oriented Programming in WordPress: Building...

Create a License Controlled Update System: The License Manager API

Create a License Controlled Update System: The Lic...

Object-Oriented Autoloading in WordPress, Part 2

Object-Oriented Autoloading in WordPress, Part 2

Building Your Startup With PHP: Localization With I18n

Building Your Startup With PHP: Localization With...

Roll Your Own Templating System in PHP

Roll Your Own Templating System in PHP