ThinkPHP view query details. ThinkPHP provides a powerful view query application. you can use the View query function to specify and filter fields of multiple data tables as needed, the ThinkPHP-based view query application is very powerful. you can use the View query function to specify and filter fields of multiple data tables as needed, organize a View model based on these data tables, and then you can use this model to directly perform multi-table joint queries, which is very convenient and simple.
For example, in a project, we define three tables:
User basic table,
User_info user details table,
Dept Department category table
Now we need to obtain a user's information,
This information should include the user's account name and relevant information and the name of the department where the user is located,
At this time, we can use view query for processing.
The following is an example:
1. build a new project and configure the project (refer to the previous tutorial, which is omitted here)
2. create a database tpview and add the three tables
(1) User table
Create table 'think _ user' ('id' int (11) unsigned not null AUTO_INCREMENT COMMENT 'Id number', 'name' varchar (20) not null comment 'account ', 'password' varchar (32) not null comment 'password', 'Dept _ id' smallint (6) unsigned not null, 'status' tinyint (1) unsigned not null default '1' comment' open status', primary key ('id') ENGINE = MyISAM default charset = utf8 COMMENT = 'Member table' AUTO_INCREMENT = 2; insert into 'think _ user' ('id', 'name', 'password', 'Dept _ id', 'status') VALUES (1, 'zzguo28 ', '123', 2, 1 );
(2) User information table
Create table 'think _ user_info '('User _ id' int (11) not null comment 'user ID', 'Nick _ name' varchar (30) not null comment 'user nickname ', 'Email' varchar (100) not null comment 'email address', 'address' varchar (100) not null comment 'detailed address ', 'gender' tinyint (1) not null default '0' COMMENT 'gender', 'mobile' varchar (100) not null comment 'mobile phone number', 'telphone' varchar (100) not null comment 'phone number', key'user _ id' ('User _ id') ENGINE = MyISAM default charset = utf8 ROW_FORMAT = dynamic comment = 'User info table '; insert into 'think _ user_info '('User _ id', 'Nick _ name', 'Email', 'address', 'gender', 'mobile', 'telphone ') VALUES (1, 'Guo ', 'zzguo28 @ 163.com', '1.6 think Street, TP Road', 1, '123', '123 ');
(3) Department category table
Create table 'think _ dept '('id' smallint (3) not null AUTO_INCREMENT, 'name' varchar (50) not null, primary key ('id ')) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 4; insert into 'think _ dept' ('id', 'name') VALUES (1, 'dnt '), (2, 'sales department '), (3, 'Finance department ');
3. create the basic Model of the three tables under Project/Lib/Model.
This example does not involve verification and other functions, so you only need to define a test, such
<?php class UserModel extends Model { } ?>
In fact, the data table corresponding to the View model does not have to have the corresponding basic model. However, we recommend that you create a table so that both the single table and view can be operated.
4. create a view model. the code is as follows:
(Note: The dynamic extension model function has been added to the latest svn. to use the new version, you need to change the protected attribute to the public attribute. we recommend that you use the dynamic extension function to use view query, it is no longer an inheritance method of this tutorial. .)
<?phpimport('ViewModel');class UserViewModel extends ViewModel{ protected $viewFields = array( 'User' =>array('id','name','_as'=>'u','_type'=>'left'), 'UserInfo' =>array('email','mobile','_as'=>'ui','_on'=>'ui.user_id=u.id'), 'Dept' =>array('name'=>'dept','_on'=>'u.dept_id=Dept.id'), );}?>
The above code is interpreted as follows:
In the 2nd line of code, view query has been separated from the original Model class since TP1.6, so we need to use the import method to introduce the View Model class here.
In the code in line 3rd, the Model name UserViewModel is defined. The Model name before the Model is random, just to distinguish it from other models, we usually name it in the form of xxxViewModel. And must inherit the ViewModel. (For ThinkPHP1.6, you do not need to set the viewModel attribute of the model to true. you only need to inherit the ViewModel)
Line 4th code $ viewFields attribute indicates the fields contained in the view model. each element defines the fields required by each data table or model.
The format is
Protected $ viewFields = array ('Table name' => array ('required field', '_ as' => 'Alias defining ', '_ on' => 'filtering condition',' _ type' => 'specifies the join type. the right, inner, and left types are supported '),);
Note that the 'name' => 'dept' in the code of row 3 already has a name field in the User model, therefore, we map the name field of the Dept model to the dept field in this way. if there are multiple fields, you can add them in the same way.
After the definition is complete, we will test it in Action. The code is as follows:
<?phpclass IndexAction extends Action{ public function index(){ $dao = D('UserView'); $where['u.id'] = 1; dump($dao->where($where)->find()); dump($dao->getLastSql()); }}?>
Then, access this operation and you will see that we have successfully obtained the required query content:
Array (1) {[0] => array (5) {["id"] => string (1) "1" ["name"] => string (7) "zzguo28" ["email"] => string (17) "zzguo28@163.com" ["mobile"] => string (11) "12345678901" ["dept"] => string (9) "sales department "}}
The SQL statement used is as follows:
"SELECT u.id AS id,u.name AS name,ui.email AS email,ui.mobile AS mobile,Dept.name AS dept FROM think_user u LEFT JOIN think_user_info ui ON ui.user_id=u.id JOIN think_dept Dept ON u.dept_id=Dept.id WHERE ( u.id = 1 ) LIMIT 1 "
The View model does not have much difference between queries and common single tables. we can use various consistent operations that we are familiar.Such as order and limit.
...