**Course Title:** Mastering Yii Framework: Building Scalable Web Applications **Section Title:** Advanced Active Record and Querying **Topic:** Using query builder for complex database queries **Overview** In the previous topics, we've explored the basics of Active Record and database interactions using Yii. However, as our applications grow in complexity, we often need to perform more sophisticated database queries. This is where the query builder comes in – a powerful tool that allows us to construct complex queries using a fluent interface. **What is the Query Builder?** The query builder is a part of Yii's Active Record component that enables us to build complex database queries using a chain of method calls. It provides a fluent interface, making it easy to read and write queries. **Benefits of Using the Query Builder** 1. **Improved Readability**: The query builder makes our code more readable by breaking down complex queries into a series of method calls. 2. **Reduced SQL Injection Risks**: By using the query builder, we can avoid directly injecting user input into our SQL queries, reducing the risk of SQL injection attacks. 3. **Easier Maintenance**: The query builder allows us to easily modify and extend our queries without having to rewrite the underlying SQL code. **Basic Query Builder Operations** Here are some basic operations you can perform using the query builder: * **Select**: Specify the columns to retrieve from the database. * **Where**: Add conditions to filter the results. * **AndWhere**: Add additional conditions to filter the results. * **OrWhere**: Add conditions to filter the results using an OR operator. * **OrderBy**: Sort the results in ascending or descending order. * **Limit**: Limit the number of results returned. **Example: Retrieving Users with a Specific Role** Suppose we want to retrieve all users with the role 'admin'. We can use the query builder to construct the following query: ```php $users = User::find() ->where(['role' => 'admin']) ->all(); ``` **Example: Retrieving Users with a Specific Role and Name** Suppose we want to retrieve all users with the role 'admin' and name 'John'. We can use the query builder to construct the following query: ```php $users = User::find() ->where(['role' => 'admin', 'name' => 'John']) ->all(); ``` **Example: Retrieving Users with a Specific Role and Name, and Sorting by Age** Suppose we want to retrieve all users with the role 'admin' and name 'John', and sort the results by age in descending order. We can use the query builder to construct the following query: ```php $users = User::find() ->where(['role' => 'admin', 'name' => 'John']) ->orderBy('age DESC') ->all(); ``` **Conclusion** In this topic, we've explored the query builder in Yii and how it can be used to construct complex database queries. We've covered basic query builder operations, including select, where, and order by, and provided examples of how to use these operations to retrieve specific data from the database. **Practice Exercise** Try using the query builder to construct a query that retrieves all users with a specific role and name, and sorts the results by age in descending order. **Additional Resources** For more information on the query builder in Yii, please refer to the official Yii documentation: <https://www.yiiframework.com/doc/guide/2.0/en/db-active-record> If you have any questions or need help with the practice exercise, please leave a comment below.