附上SQL测试库:
//模型中代码==============================================
namespace Home\Model;
use Think\Model\ViewModel;
class MemberViewModel extends ViewModel{
protected $viewFields = array(
'Member'=>array('uid','uname','email','_type'=>'LEFT'),//默认用join,加上条件后成left join
'Usercard'=>array('code','_on'=>'Member.uid = Usercard.uid','_type'=>'LEFT'),//如果不加条件就是笛卡尔乘积
//发现一个BUG,如果只用:Member与Usermsgs表进行关联,则结果是正确的,如果三表连接,SQL语句没问题,在SQL查询器是查询OK,但返回结果错了,用户uid=1的留言数显示6条,实际只有3条.
'Usermsgs'=>array('msg','ctime','_on'=>'Member.uid = Usermsgs.uid','COUNT(Usermsgs.msg)'=>'count'),//它如果有多条留言,则会出现多条数据,如果我们只要首条,则查询时加上->group('uid'),还可以进行留言条数统计
);
}
//控制器中代码===========================================
namespace Home\Controller;
use Think\Controller;
header('content-type:text/html;charset=utf-8');
class IndexController extends Controller {
public function index(){
//$user = D('MemberView');
//$arr = $user->group('uid')->select();//注意模型中三表关联,结果统计uid=1的用户留言数是6,但下面SQL语句也是它生成的,在SQL查询分析器中得到结果是3,用它query()方法执行SQL语句也是3,无语啊......
$user= D('Member');
$arr = $user->query('SELECT Member.uid AS uid,Member.uname AS uname,Member.email AS email,Usermsgs.msg AS msg,Usermsgs.ctime AS ctime,COUNT(Usermsgs.msg) AS count FROM think_member Member LEFT JOIN think_usermsgs Usermsgs ON Member.uid = Usermsgs.uid GROUP BY Member.uid');
print_r($arr);
}
}
thinkphp32.zip
( 1.77 KB 下载：1 次 )
最佳答案
