tp3.2怎么用mysql视图_ThinkPHP 3.2.3 视图模型的使用

实例

需求：在博客列表页读取博客的(id、标题、摘要、发布时间、点击次数)等信息以及该篇博文所属分类的(分类名)等信息

数据表：

crm_blog

+---------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+----------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| title | varchar(30) | NO | | | |

| summary | varchar(255) | NO | | | |

| content | text | NO | | NULL | |

| time | int(10) unsigned | NO | | 0 | |

| click | smallint(6) unsigned | NO | | 0 | |

| cid | int(10) unsigned | NO | MUL | NULL | |

| del | tinyint(1) unsigned | NO | | 0 | |

+---------+----------------------+------+-----+---------+----------------+

crm_cate

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | char(20) | NO | | | |

| pid | int(10) unsigned | NO | MUL | 0 | |

| sort | smallint(6) | NO | | 100 | |

+-------+------------------+------+-----+---------+----------------+

crm_blog 和 crm_cate 是多对一的关系(BELONGS_TO)

在 Home 模块的 Model(./Application/Home/Model)下创建 BlogViewModel.class.php

namespace Home\Model;useThink\Model\ViewModel;/** 视图模型*/

class BlogViewModel extendsViewModel{protected $viewFields = array('blog'=>array('id','title','summary','click','time',

'_type'=>'LEFT' //关联方式，默认是 INNER

),

'cate'=>array('name'=>'cate_name',

'_on'=>'blog.cid = cate.id' //关联条件

)

);public function get_all($where,$limit) {return $this->where($where)->limit($limit)->select();

}

}

控制器 ./Application/Home/Controller/ListController.class.php

namespace Home\Controller;useThink\Controller;useAdmin\Common\Category;useThink\Page;class ListController extendsController{//列表页

public functionindex() {$id = (int)$_GET['id'];$cate = M('cate')->order('sort')->select();//分类名称

$this->cate_name = M('cate')->where(array('id'=>$id))->getField('name');//通过父id递归查出子id

$cids = Category::get_children_id($cate, $id);$cids[] = $id;//分页

$where = array('cid'=>array('IN',$cids));$count = M('blog')->where($where)->count();$page = new Page($count,1);$limit = $page->firstRow.','.$page->listRows;$this->page = $page->show();//使用试图模型

$this->blog = D('BlogView')->get_all($where,$limit);//echo D('BlogView')->getLastSql();//一条联合查询语句 SELECT blog.id AS id,blog.title AS title,blog.summary AS summary,blog.click AS click,blog.time AS time,cate.name AS cate_name FROM crm_blog blog LEFT JOIN crm_cate cate ON blog.cid = cate.id WHERE `cid` IN ('13','12','11',4)

$this->display();

}

}

缺陷：在列表页分页完成之后，分页的链接并不是 URL 重写之后的链接，待改进。