Managing Database Logic with Yii Active Record

Problem: The Friction of Manual SQL in CRUD

When building data-driven features, writing manual INSERT and UPDATE statements for every form creates a maintenance burden. If a column name changes in the database, you must hunt through controllers and services to update raw strings, increasing the risk of runtime crashes and schema mismatch.

Thesis: Yii's Active Record provides an object-oriented abstraction that maps database tables to PHP classes, allowing you to handle data as objects while maintaining visibility into the generated SQL.

Automating Model Creation with Gii

Yii includes a code generator called Gii that inspects your database schema and generates the necessary Active Record classes. This eliminates the need to manually define properties for every table column.

To generate a model, run the following command from your project root (requires a configured database connection in your app config):

# Run as the user owning the web directory to ensure file permissions
php yii gii/model --tableName=customer --modelClass=Customer

Risk: Running this command on an existing model will overwrite the file. Always commit your custom model logic to version control or use a separate base class if you intend to regenerate models frequently.
The resulting class extends yii\db\ActiveRecord and includes a tableName() method, which tells the framework exactly which table to query.

Worked Example: Implementing a Customer Update Flow
The following example demonstrates how Active Record handles data loading, validation, and persistence without writing a single line of SQL.
namespace app\controllers;

use Yii;
use app\models\Customer;
use yii\web\Controller;
use yii\web\NotFoundHttpException;

class CustomerController extends Controller
{
    public function actionUpdate($id)
    {
        // find() returns an ActiveQuery object; one() executes it
        $model = Customer::findOne($id);
        if (!$model) {
            throw new NotFoundHttpException('Customer not found.');
        }

        // load() populates the model from POST data
        // validate() checks rules defined in the model class
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['view', 'id' => $model->id]);
        }

        return $this->render('update', ['model' => $model]);
    }

    public function actionList()
    {
        // Eager loading using with() prevents the N+1 query problem
        $customers = Customer::find()->with('orders')->all();
        return $this->render('list', ['customers' => $customers]);
    }
}

To verify the result, use the Yii Debug Toolbar. In the "Database" section, you can see the exact SQL executed. For findOne($id), you will see a parameterized SELECT * FROM customer WHERE id = :id, which protects the application from SQL injection.

Trade-off: Eager Loading vs. Lazy Loading
Yii supports two ways of handling related data (e.g., a Customer's Orders). Lazy loading fetches the related data only when you access the property ($customer->orders), which is fine for a single record. However, in a loop of 50 customers, lazy loading triggers 51 queries (1 for the list, 50 for the orders)—this is the N+1 problem.
Eager loading via with('orders') reduces this to 2 queries. The trade-off is memory: eager loading pulls all related records into PHP memory at once. If the related table contains thousands of rows per parent, eager loading can exhaust the PHP memory limit.

Limitation: Complex Reporting and Bloated Models
Active Record is designed for CRUD, not complex analytics. Using it for deep joins, window functions, or heavy aggregations often results in inefficient SQL or unreadable PHP code. In these cases, use the yii\db\Query builder or raw SQL via Yii::$app->db->createCommand().
Additionally, avoid placing complex business logic directly in the Active Record class. If your model exceeds a few hundred lines, move the logic to a separate Service layer to keep the model focused on data access.

Closing: Verify and Optimize
Start by using Gii to scaffold your models and use Active Record for standard data entry and retrieval. Always keep the Debug Toolbar open during development to monitor the query count. When you see the query count spike during a page load, switch from lazy to eager loading, or drop down to the Query Builder for high-performance reporting.