Data query in Yii framework: access data efficiently

Yii framework is an open source PHP web application framework that provides numerous tools and components to simplify the process of web application development, of which data query is one of the important components. In the Yii framework, we can use SQL-like syntax to access the database to query and manipulate data efficiently.

The query builder of Yii framework mainly includes the following types: Active Record query, Query Builder query, command query and original SQL query. This article will introduce these query builders and how to use them one by one to help beginners better master how to use data queries in the Yii framework.

1. Active Record Query

Active Record mode is one of the most commonly used data access modes in the Yii framework. It provides an object-oriented interface that allows us to Manipulate data in the database just as you would an object-oriented instance. In the Yii framework, each Active Record class corresponds to a database table, and we can access the data in the table by calling the static method of the class.

The following is an example of an Active Record query:

//创建一个Active Record对象
$post = Post::findOne(1);

//输出该对象的属性
echo $post->title;

In this example, we first create a Post class using the findOne() method Instance of Attributes. In actual development, we usually need to perform data filtering, sorting, paging and other operations. The Yii framework provides rich methods to implement these functions. For example, we can use the where() method to add filter conditions, the

orderBy()

method to specify the sorting method, and the limit() method to limit returns The number of records, use the offset() method to specify the starting position of the returned record. The following is an example: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>//查询标题包含“Yii”并且作者为“admin”的文章，并按照发布时间倒序排序，返回前10条记录 $posts = Post::find()-&gt;where(['like', 'title', 'Yii']) -&gt;andWhere(['author' =&gt; 'admin']) -&gt;orderBy(['created_at' =&gt; SORT_DESC]) -&gt;limit(10) -&gt;offset(0) -&gt;all();</pre><div class="contentsignin">Copy after login</div></div>In this example, we use the find() method to create an Active Record query object, and then use

where()

and # The ##andWhere() method adds two filter conditions, namely that the title contains "Yii" and the author is "admin"; the orderBy() method is used to specify sorting in reverse order of release time; use The limit() method limits the number of returned records to 10; use the offset() method to specify that the starting position of the returned records is 0 records. Finally, we use the all() method to execute the query and return all records that meet the requirements. Query BuilderQuery

Query Builder is another commonly used data access method in the Yii framework. It provides a chain call method to build SQL query statements. , more suitable for complex query requirements. In the Yii framework, we can use the

Yii::$app->db->createCommand()
2. method to create a Query Builder object, and then use a series of methods of the object to build query statements.

The following is an example of a Query Builder query:

//创建一个查询构建器对象，并构建查询语句
$query = Yii::$app->db->createCommand()
        ->select('id, title, content')
        ->from('post')
        ->where(['like', 'title', 'Yii'])
        ->andWhere(['author' => 'admin'])
        ->orderBy(['created_at' => SORT_DESC])
        ->limit(10)
        ->offset(0);

//执行查询，并返回结果集
$posts = $query->queryAll();

In this example, we first use Yii::$app->db->createCommand()

The method creates a Query Builder object and then uses the object's

select()

,

from(), where(), andWhere()# Methods such as ##, orderBy(), limit(), and offset() are used to construct query statements. Finally, we use the queryAll() method to execute the query and return all records that meet the requirements. The biggest difference between Query Builder and Active Record is that Query Builder does not need to define a model class, so it is suitable for some simple query scenarios, such as statistical queries. Command query

Command query is the most original data access method in the Yii framework. We can use this method to perform some database operations that do not require returning a result set. Such as update, delete, insert, etc. In the Yii framework, we can use the

Yii::$app->db->createCommand()

method to create a Command object, and then use the
3. execute()
method of the object to execute SQL statements.

The following is an example of a command query: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>//创建一个命令对象，并执行SQL语句 Yii::$app-&gt;db-&gt;createCommand() -&gt;update('post', ['status' =&gt; 1], 'author = &quot;admin&quot;') -&gt;execute();</pre><div class="contentsignin">Copy after login</div></div>In this example, we first use the Yii::$app->db->createCommand() method Created a Command object, and then used the object's

update()

method to construct an update statement that changed all

author

in the post table to "admin "The status attribute of the record is updated to 1; finally, we use the execute() method to execute the update statement. Original SQL queryIn some special cases, we may need to execute some complex query statements that cannot be processed through Active Record, Query Builder or command query. Raw SQL queries can be used at this time. In the Yii framework, we can use the

Yii::$app->db->createCommand()

method to create a Command object, and then use the
4. setSql()
method of the object To specify the original SQL statement, and use the

queryAll()

method to execute the query. The following is an example of a raw SQL query:<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>//创建一个命令对象，并执行原始SQL查询 $connection = Yii::$app-&gt;db; $command = $connection-&gt;createCommand(&quot; SELECT p.id, p.title, u.username FROM post p LEFT JOIN user u ON p.author_id = u.id WHERE p.status = 1 AND u.role = 'admin' ORDER BY p.created_at DESC LIMIT 10 OFFSET 0 &quot;); $posts = $command-&gt;queryAll();</pre><div class="contentsignin">Copy after login</div></div><p>这个例子中，我们首先创建了一个Command对象，并使用<code>setSql()方法指定一条原始的SQL查询语句。该语句将post表和user表进行左连接，查询出所有状态为1且用户角色为“admin”的文章，并按照发布时间倒序排序，返回前10条记录。最后，我们使用queryAll()方法执行该查询，并返回所有符合要求的记录。

总结：

在Yii框架中，我们可以使用多种方式来访问数据库中的数据，包括Active Record查询、Query Builder查询、命令查询和原始SQL查询。不同的查询构建器适用于不同的查询场景，我们需要根据实际需求来选择最合适的查询方式。通过本文的介绍，相信读者已经对Yii框架中的数据查询有了更深入的了解，希望对大家在实际开发中使用Yii框架有所帮助。

The above is the detailed content of Data query in Yii framework: access data efficiently. For more information, please follow other related articles on the PHP Chinese website!