thinkphp View Query Detailed

thinkphp View Query Detailed reference http://www.jb51.net/article/51674.htm this article mainly introduces the thinkphp view query, the need for friends can refer to the following

Thinkphp provides a very powerful view query application function, the user can use the View query function to specify and filter the field contents of multiple data tables as needed, organized into a view model based on these data tables, and then can be directly through the model of multi-table joint query, very convenient and simple.

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

User base table,
User_info User Details table,
Dept Department Classification Table

Now we need to get a user information,
This information includes the user's account name and the relevant information and the name of the department in which it is located.
At this point we can use the view query for processing.

The following examples 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

?

12345678910

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 Form

?

123456789101112

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, ‘国‘, ‘[email protected]‘, ‘TP路think街1.6号‘, 1, ‘12345678901‘, ‘123456‘);

(3) Department classification table

?

123456789

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 base model for these three tables under Project/lib/model
This example does not involve other functions such as validation, so simply define the test, for example

?

1234	<?php classUserModel extendsModel { }?>

In fact, the data table of the view model does not necessarily have a corresponding base model, but it is recommended that you create it so that both the single table and the view can be manipulated.

4. Create a view model with the following code:

(Note: The latest SVN has added the dynamic extension model feature, using the new version needs to change the protected property to the public property, it is recommended to use the dynamic extension function to use the view query, rather than the way this tutorial inherits.) That would be more flexible to use. )

?

12345678910

<?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, because the view query has been detached from the original model class since version TP1.6, it is necessary to introduce the view model class using the Import method.

In the 3rd line of code, the name of the model is defined as Userviewmodel, and the name of the view model before model is arbitrary, but in order to differentiate it from other models, we usually name it in the Xxxviewmodel way. And be sure to inherit the ViewModel. (The ViewModel property of the ThinkPHP1.6 version does not have to be set to true, as long as the ViewModel is inherited)

The 4th line of Code $viewfields property represents the fields that the view model contains, and each element defines the fields that are required for each data table or model.
Format is

?

123	protected $viewFields = array (      => array ( "required Fields" ' _as ' => ' Alias definition ' ' _on ' => ' filter condition ' ' _type ' =>

Notice that ' name ' = ' dept ' in line 7th, because a name field already exists in the user model, so we map the name field of the Dept model to the Dept field in this way, and if there are more than one field, you can add it in the same way.

Once the definition is complete, we test it in action with the following code

?

12345678910

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

Then, you can see that we have successfully obtained the required query content by accessing the operation:

?

123456789

array(1) { [0] => array(5) {  ["id"] => string(1) "1"  ["name"] => string(7) "zzguo28"  ["email"] => string(17) "[email protected]"  ["mobile"] => string(11) "12345678901"  ["dept"] => string(9) "销售部" }}

And you can see the SQL used below

?

1	"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 differ much from the normal single table on the query, and can use the various coherent operations that we are familiar with, such as Order,limit and so on.

thinkphp View Query Detailed