ThinkPHP view query

This article mainly introduces ThinkPHP view query. Friends who need it can refer to it

The view query application function provided by ThinkPHP is very powerful. Users can use the view query function to sort the field contents of multiple data tables by It needs to be specified and filtered, organized into a view model based on these data tables, and then multi-table joint queries can be directly performed through the model, which is very convenient and simple.

For example, in the project, we have three tables defined:

##user User basic table,

user_info User details table,
dept Department classification table

Now we need to obtain 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 query to process.

The following examples are given to illustrate:

1. Build a new project and perform related configurations (refer to the previous tutorial, omitted here)

2. Create a database tpview, and add this 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

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. In project/Lib/ Create the basic model Model

of these three tables under Model. This example does not involve verification and other functions, so it can be tested simply by defining it, for example

 <?php
  class UserModel extends Model {
  }
 ?>

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

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

(Note: The latest svn has added the function of dynamically extending the model. To use the new version, you need to change the protected attribute to 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.)

<?php
import(&#39;ViewModel&#39;);
class UserViewModel extends ViewModel{
  protected $viewFields = array(
    &#39;User&#39;   =>array(&#39;id&#39;,&#39;name&#39;,&#39;_as&#39;=>&#39;u&#39;,&#39;_type&#39;=>&#39;left&#39;),
    &#39;UserInfo&#39; =>array(&#39;email&#39;,&#39;mobile&#39;,&#39;_as&#39;=>&#39;ui&#39;,&#39;_on&#39;=>&#39;ui.user_id=u.id&#39;),
    &#39;Dept&#39;   =>array(&#39;name&#39;=>&#39;dept&#39;,&#39;_on&#39;=>&#39;u.dept_id=Dept.id&#39;),
  );
}
?>

The above code is explained as follows:

In Chapter 2 In the 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 each data table or Fields required by the model.

The format is

protected $viewFields = array(
    '表名'=>array('所需字段','_as'=>'别名定义','_on'=>'筛选条件','_type'=>'指定join类型，支持right,inner,left三种'),
);

Note that 'name'=>'dept' in the 7th line of code, because there is already a name field in the User model, so we use this method to add the Dept model The name field is mapped to the dept field. If there are multiple fields, they can be added in the same way.

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

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

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 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 from the ordinary single table in querying. You can use various coherent operations that we are familiar with, such as order, limit etc.

Related recommendations:

Behavior extensions and plug-ins in ThinkPHP

ThinkPHP implementation update data example detailed explanation (demo)

The above is the detailed content of ThinkPHP view query. For more information, please follow other related articles on the PHP Chinese website!