Detailed Explanation of ThinkPHP View Query

The view query application function provided by ThinkPHP is powerful. Users can specify and filter the field contents of multiple data tables according to their needs by using the view query function, and organize them into a view model based on these data tables, and then they can directly query multiple tables through this model, which is very convenient and simple.

For example, in the project, we define three tables:

user user base table,
user_info user details table,
dept sector classification table

Now we need to get some user information,
The information should include the user's account name and related information and the name of the department.
At this time, we can use view query to process.

Here are some examples to illustrate:

1. Build a new project and configure it (refer to the previous tutorial, omitted here)
2. Create a database tpview and add these three tables
(1) User table

CREATE TABLE `think_user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID Numbering ',
 `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 state ',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' Membership list ' AUTO_INCREMENT=2 ;
INSERT INTO `think_user` (`id`, `name`, `password`, `dept_id`, `status`) VALUES
(1, 'zzguo28', '123456', 2, 1);

(2) User information table

CREATE TABLE `think_user_info` (
 `user_id` int(11) NOT NULL COMMENT ' Users 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 ',
 `telephone` varchar(100) NOT NULL COMMENT ' Telephone number ',
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=' User information table ';
INSERT INTO `think_user_info` (`user_id`, `nick_name`, `email`, `address`, `gender`, `mobile`, `telephone`) VALUES
(1, ' Countries ', 'zzguo28@163.com', 'TP Road think Street 1.6 No. ', 1, '12345678901', '123456');

(3) Sectoral classification 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, ' Development Department '),
(2, ' Sales department '),
(3, ' Finance Department ');

3. Create the basic model Model for these three tables under Project/Lib/Model
This example does not cover other functions such as verification, so simply define the test, such as

 <?php
  class UserModel extends Model {
  }
 ?>

In fact, the data table corresponding to the view model does not have to have a corresponding basic model, but it is recommended that you create it so that both single table and view can operate.

4. Create the view model with the following code, followed by detailed comments:

(Note: Dynamic extension model function has been added to the latest svn. To use the new version, protected attribute needs to be changed to public attribute. It is recommended to use dynamic extension function to use view query instead of inheritance method in this tutorial. It will be more flexible to use that way. )

<?php
import('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 explained as follows:

In Line 2, since the view query has been separated from the original Model class since TP version 1.6, the import method is used here to introduce the view model class.

In the third line of code, the model name is defined as UserViewModel, and the naming before the view model name Model is random, just to be different from other models, and we usually name it in the way of xxxViewModel. And 1 must inherit ViewModel. (ThinkPHP version 1.6 does not need to set the viewModel property of the model to true, but inherits ViewModel.)

Line 4: The $viewFields attribute represents the fields contained in the view model, and each element defines the fields required by each data table or model.
The format is

protected $viewFields = array(
    ' Table name '=>array(' Required fields ','_as'=>' Alias definition ','_on'=>' Screening criteria ','_type'=>' Specify join Type, support right,inner,left3 Species '),
);

Notice in line 7 that 'name' = > 'dept', because there is already one name field in User model, we map the name field of Dept model to dept field in this way. If there are multiple fields, we can add them in the same way.

After definition, we test it in Action, and the code is as follows

<?php
class IndexAction extends Action{
  public function index(){
    $dao = D('UserView');
    $where['u.id'] = 1;
    dump($dao->where($where)->find());
    dump($dao->getLastSql());
  }
}
?>

Then, by accessing the operation, we can see that we 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 "
 }
}

And you can see that the sql 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 is not much different in queries from ordinary single tables, and can use various coherent operations that we are familiar with, such as order, limit and so on.