Description of the ThinkPHP Database View model

In the preceding ThinkPHP database view model, the View model of two tables is used as an example. The View model of multiple tables can be defined according to The View model definition rules. To define the View model of multiple tables, you only need to add the table element in sequence when defining the $ viewFields attribute. Database View for multiple tables

In the previous article ThinkPHP
The database View model provides two table view models. you can define view models for multiple tables according to The View model definition rules. To define the View model of multiple tables, you only need to define the $ viewFields
Attribute, add the table element in sequence.

For example, when reading the document information, we need to read both the author information and the document category information:

2. Class ArticleViewModel extends ViewModel {
   
3. Public $ viewFields = array (
   
4. 'Article' => array ('aid ', 'title', 'content', 'uid '),
   
5. 'Category '=> array ('Category _ name',' _ on' => 'article. cid = category. Cid '),
   
6. 'User' => array ('username', '_ on' => 'article. uid = user. uid '),
   
7. );
   
8. }
   
9. ?>
   

Define View field alias

In the view model file, the value of each table element is the corresponding field name. if the field names of different tables are the same, you need to define the field alias to avoid repeated fields:

2. Class ArticleViewModel extends ViewModel {
   
3. Public $ viewFields = array (
   
4. 'Article' => array ('aid ', 'title', 'Content '),
   
5. 'Category '=> array ('title' => 'Category _ name',' _ on' => 'article. cid = category. Cid '),
   
6. 'User' => array ('username', '_ on' => 'article. uid = user. uid '),
   
7. );
   
8. }
   
9. ?>

For example, in the preceding example, category
The table also has a title field, so you can avoid field conflicts by defining the alias 'title' => 'Category _ name.
Define the View model table query
JOIN type

As mentioned above, the ThinkPHP Database View model is simulated by JOIN queries. Therefore, when defining a view model, you can also define
_ Type value to define the JOIN type for table query:

2. Class ArticleViewModel extends ViewModel {
   
3. Public $ viewFields = array (
   
4. 'Article' => array ('aid ', 'title', 'content', 'uid',' _ type' => 'left '),
   
5. 'User' => array ('username', '_ on' => 'article. uid = user. uid '),
   
6. );
   
7. }
   
8. ?>

The actual executed SQL statement is changed:

1. SELECT article. aid AS aid, article. title AS title, article. content AS content, article. uid AS uid, user. username AS username FROM test_article article left join test_user user ON article. uid = user. uid
   

For more information about JOIN types of table JOIN queries, see MySQL JOIN
Table connection series.
Database View model query

Use method D
After the defined View model file is instantiated, it can be queried like a common data table. it supports conditional query and sorting.

Read specified $ _ GET <'aid '>
Parameter article:

2. Class ArticleAction extends Action {
   
3. Public function index (){
   
4. Header ("Content-Type: text/html; charset = utf-8 ");
   
   
5. $ Dao = D ('articleview'); // instantiate the View
   
6. $ Article = $ Dao-> where ('aid = '. intval ($ _ GET <'aid'>)-> find ();
   
7. Print_r ($ article_list );
   
8. }
   
9. }
   
10. ?>
    

Note:

Because the View cannot define the primary key, in the above example, if you directly pass the parameter
In the find () method, ThinkPHP sets the default primary key field as id, so that the corresponding data is not obtained. Therefore, you must use where ()
Method.

You can also use the order () method for sorting. the limit () method limits the number of returned queries and the group () method ()
To remove duplicate records and so on (view queries often have duplicate data), and the same is true for single table queries.
View model where ()

In the above example, if we want to query all the articles of a user and follow the article
Aid in descending order:

$ Article_list = $ Dao-> where ('uid = 1')-> order ('aid
DESC ')-> select ();

At this time, the query results will not be obtained, and the actual SQL statement is printed:

1. SELECT article. aid AS aid, article. title AS title, article. content AS content, article. uid AS uid,
   
2. User. username AS username FROM test_article article JOIN test_user user ON article. uid = user. uid
   
3. WHERE uid = 1 order by article. aid DESC

The reason is that
Both the article and user tables contain uid
Here, we can use the method of specifying the field alias mentioned above to solve the problem. a simpler method is to directly specify the table name in the condition:

$ Article_list =
$ Dao-> where ('article. uid = 1')-> order ('aid
DESC ')-> select ();

Some fields cannot be found when the view model is used.

When a View model is used, some field values cannot be read. the common reason is that when the view model is defined, this field is not defined or multiple tables have the same field name, this field is used during query (as shown in the preceding example ). In this case, check whether the view definition is correct or
JOIN SQL
Print it out and execute it directly in the database to check the problem.

To sum up, when querying multiple associated tables, you only need to define the database View model according to the rules, which greatly simplifies the query of multiple associated tables.
SQL complexity.