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:

//創(chuàng)建一個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: <pre class='brush:php;toolbar:false;'>//查詢標題包含“Yii”并且作者為“admin”的文章，并按照發(fā)布時間倒序排序，返回前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>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:

//創(chuàng)建一個查詢構建器對象，并構建查詢語句
$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);

//執(zhí)行查詢，并返回結果集
$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: <pre class='brush:php;toolbar:false;'>//創(chuàng)建一個命令對象，并執(zhí)行SQL語句 Yii::$app-&gt;db-&gt;createCommand() -&gt;update('post', ['status' =&gt; 1], 'author = &quot;admin&quot;') -&gt;execute();</pre>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:<pre class='brush:php;toolbar:false;'>//創(chuàng)建一個命令對象，并執(zhí)行原始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><p>這個例子中，我們首先創(chuàng)建了一個Command對象，并使用<code>setSql()方法指定一條原始的SQL查詢語句。該語句將post表和user表進行左連接，查詢出所有狀態(tài)為1且用戶角色為“admin”的文章，并按照發(fā)布時間倒序排序，返回前10條記錄。最后，我們使用queryAll()方法執(zhí)行該查詢，并返回所有符合要求的記錄。

總結：

在Yii框架中，我們可以使用多種方式來訪問數(shù)據(jù)庫中的數(shù)據(jù)，包括Active Record查詢、Query Builder查詢、命令查詢和原始SQL查詢。不同的查詢構建器適用于不同的查詢場景，我們需要根據(jù)實際需求來選擇最合適的查詢方式。通過本文的介紹，相信讀者已經(jīng)對Yii框架中的數(shù)據(jù)查詢有了更深入的了解，希望對大家在實際開發(fā)中使用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!