ThinkPHP view query detailed explanation_PHP tutorial

The view query application function provided by ThinkPHP is very powerful. Users can use the view query function to specify and filter the field contents of multiple data tables as needed, organize them into a view model based on these data tables, and then use the view query function to The model directly performs joint queries on multiple tables, 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 Department classification table

Now we need to get a certain user information,
This information should include the user’s account name and related information and the name of the department,
At this time we can use view queries for processing.

The following examples illustrate:

1. Build a new project and perform relevant configurations (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编号',
 `name` varchar(20) NOT NULL COMMENT '帐户',
 `password` varchar(32) NOT NULL COMMENT '密码',
 `dept_id` smallint(6) unsigned NOT NULL,
 `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '开放状态',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='会员表' 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 '用户id',
 `nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
 `email` varchar(100) NOT NULL COMMENT '邮箱地址',
 `address` varchar(100) NOT NULL COMMENT '详细地址',
 `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别',
 `mobile` varchar(100) NOT NULL COMMENT '手机号码',
 `telephone` varchar(100) NOT NULL COMMENT '电话号码',
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户信息表';
INSERT INTO `think_user_info` (`user_id`, `nick_name`, `email`, `address`, `gender`, `mobile`, `telephone`) VALUES
(1, '国', 'zzguo28@163.com', 'TP路think街1.6号', 1, '12345678901', '123456');

(3) Department 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, '开发部'),
(2, '销售部'),
(3, '财务部');

3. Create the basic model Model of these three tables under project/Lib/Model
This example does not involve other functions such as verification, so you only need to simply define the test, such as

 <&#63;php
  class UserModel extends Model {
  }
 &#63;>

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

4. Create a view model, the code is as follows, detailed annotations are shown below:

(Note: The latest svn has added a dynamic expansion model function. To use the new version, you need to change the protected attribute to a public attribute. It is recommended to use the dynamic expansion function to use view queries instead of the inheritance method in this tutorial. That way it will be more flexible. )

<&#63;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'),
  );
}
&#63;>

The above code is explained as follows:

In the second line of code, since the view query has been separated from the original Model class since TP version 1.6, the view model class needs to be introduced here using the import method.

In the third line of code, the model name is defined as UserViewModel. The name before the view model Model is arbitrary, just to distinguish it from other models. Usually we will name it in such a way as xxxViewModel. And must inherit ViewModel. (ThinkPHP version 1.6 no longer needs to set the viewModel attribute of the model to true, as long as it inherits ViewModel)

The $viewFields attribute in line 4 of the code represents 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(
    '表名'=>array('所需字段','_as'=>'别名定义','_on'=>'筛选条件','_type'=>'指定join类型，支持right,inner,left三种'),
);

Notice the 'name'=>'dept' in the 7th line of code. Because there is already a name field in the User model, we map the name field of the Dept model to the dept field in this way. If There are multiple fields that can be added in the same way.

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

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

Then access the operation and you 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) "销售部"
 }
}

And you can see the sql used 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 query of the view model is not much different from that of an ordinary single table. You can use various coherent operations that we are familiar with, such as order, limit, etc.