**Course Title:** Mastering Yii Framework: Building Scalable Web Applications **Section Title:** Advanced Active Record and Querying **Topic:** Implement advanced querying techniques and caching in a Yii application.(Lab topic) **Objective:** By the end of this topic, you will be able to implement advanced querying techniques and caching in a Yii application, including using query builder for complex database queries, implementing scopes and behaviors in Active Record, handling pagination and sorting, and using Yii's caching features for performance optimization. **Prerequisites:** You should have a good understanding of the Yii framework, Active Record, and query builder. You should also have completed the previous topics in this section, including "Using query builder for complex database queries" and "Implementing scopes and behaviors in Active Record". **Lab Topic: Implement Advanced Querying Techniques and Caching in a Yii Application** **1. Using Query Builder for Complex Database Queries** Query builder is a powerful tool in Yii that allows you to create complex database queries using a fluent interface. You can use query builder to perform operations such as filtering, sorting, and grouping. **Example:** ```php use yii\db\Query; $query = (new Query()) ->select(['id', 'name']) ->from('users') ') ->where(['age' => 25]) ->orderBy(['name' => SORT_ASC]) ->all(); foreach ($query as $user) { echo $user['id'] . ' ' . $user['name'] . "\n"; } ``` **2. Implementing Scopes and Behaviors in Active Record** Scopes and behaviors are two powerful features in Active Record that allow you to define reusable code for database queries and model behavior. **Example:** ```php use yii\db\ActiveRecord; class User extends ActiveRecord { public function scopeActive($query) { return $query->where(['status' => self::STATUS_ACTIVE]); } public function behaviors() { return [ 'timestamp' => [ 'class' => TimestampBehavior::className(), 'createdAtAttribute' => 'created_at', 'updatedAtAttribute' => 'updated_at', 'value' => date('Y-m-d H:i:s'), ], ]; } } ``` **3. Handling Pagination and Sorting** Pagination and sorting are two essential features in web applications that allow users to navigate and sort data. **Example:** ```php use yii\data\Pagination; $query = User::find()->where(['age' => 25]); $pagination = new Pagination(['totalCount' => $query->count()]); $users = $query->offset($pagination->offset) ->limit($pagination->limit) ->all(); foreach ($users as $user) { echo $user['id'] . ' ' . $user['name'] . "\n"; } ``` **4. Using Yii's Caching Features for Performance Optimization** Caching is a powerful technique for improving the performance of web applications. **Example:** ```php use yii\caching\Cache; $cache = new Cache(['class' => 'yii\caching\FileCache']); $data = $cache->get('user_data'); if ($data === false) { $data = User::find()->where(['id' => 1])->one(); $cache->set('user_data', $data, 3600); // cache for 1 hour } echo $data['name']; ``` **Conclusion:** In this topic, we have covered advanced querying techniques and caching in a Yii application. We have learned how to use query builder for complex database queries, implement scopes and behaviors in Active Record, handle pagination and sorting, and use Yii's caching features for performance optimization. **Exercise:** Implement a caching system for a web application using Yii's caching features. **Additional Resources:** * Yii documentation: [Query Builder](https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder) * Yii documentation: [Scopes and Behaviors](https://www.yiiframework.com/doc/guide/2.0/en/db-active-record-scopes-behaviors) * Yii documentation: [Pagination](https://www.yiiframework.com/doc/guide/2.0/en/db-pagination) * Yii documentation: [Caching](https://www.yiiframework.com/doc/guide/2.0/en/caching-overview) **Leave a comment or ask for help if you have any questions or need further clarification on any of the topics covered in this topic.**