ThinkPHP执行原生sql，实现一些复杂的业务需求
1）事情起因：写php的同事做了社区消息接口，主要返回几个方面的消息，如我的主贴的点赞、我的层帖的点赞、我的主贴的评论、我的评论的评论，
数据因为关联了5张以上的表，返回的格式不一：
如原来的thinkphp：
1 //社区消息 2 public function mess() 3 { 4 if(IS_POST){ 5 $da['username'] = $_POST['username']; 6 $db1 = M('postmessage'); 7 $postid = $db1->field('postid')->order('time desc')->where($da)->select(); 8 $db2 = M('postlikes'); 9 foreach($postid as $k => $v){ 10 $th['postid'] = $postid[$k]['postid']; 11 $db5 = M('postmessage'); 12 $postmessage = $db5->where($th)->select(); 13 $postlikes[] = $db2->order('time desc')->where($th)->select(); 14 foreach($postlikes as $m=>$n){ 15 foreach($n as $d=>$f){ 16 foreach($postmessage as $a=>$s){ 17 // print_r($postmessage[$a]['title']);exit; 18 $n[$d]['title'] = $postmessage[$a]['title']; 19 $n[$d]['portrait'] = $postmessage[$a]['portrait']; 20 $n[$d]['sex'] = $postmessage[$a]['sex']; 21 $n[$d]['nickname'] = $postmessage[$a]['nickname']; 22 } 23 $postlikes[$m] = $n; 24 } 25 } 26 } 27 $db3 = M('postcomments'); 28 foreach($postid as $ke => $va){ 29 $thi['postid'] = $postid[$ke]['postid']; 30 $comments[] = $db3->order('time desc')->where($thi)->select(); 31 $db5 = M('postmessage'); 32 $postmessage = $db5->where($th)->select(); 33 foreach($comments as $m=>$n){ 34 foreach($n as $d=>$f){ 35 foreach($postmessage as $a=>$s){ 36 // print_r($postmessage[$a]['title']);exit; 37 $n[$d]['title'] = $postmessage[$a]['title']; 38 $n[$d]['portrait'] = $postmessage[$a]['portrait']; 39 $n[$d]['sex'] = $postmessage[$a]['sex']; 40 $n[$d]['nickname'] = $postmessage[$a]['nickname']; 41 } 42 $comments[$m] = $n; 43 } 44 } 45 } 46 $db4 = M('postdouble'); 47 foreach($postid as $k => $v){ 48 $thu['postid'] = $postid[$k]['postid']; 49 $double = $db4->order('time desc')->where($thu)->select(); 50 // print_r($double);exit; 51 /*foreach($likes as $l => $i){ 52 $the['postid'] = $likes[$l]['postid']; 53 $the['floor'] = $likes[$l]['floor']; 54 $double = $db4->order('time desc')->where($the)->select(); 55 }*/ 56 } 57 // print_r($double);exit; 58 if(true){ 59 $response['status'] = 'Y'; 60 $response['msg'] = '成功'; 61 $response['data1'] = $postlikes; 62 $response['data2'] = $comments; 63 $response['data3'] = $double; 64 echo json_encode($response); 65 }else{ 66 $response['status'] = 'N'; 67 $response['msg'] = '失败'; 68 echo json_encode($response); 69 } 70 } 71 }
返回的数据结果：
1 { 2 "status": "Y", 3 "msg": "成功", 4 "data1": [ 5 [ 6 { 7 "id": "20", 8 "postid": "23", 9 "username": "13760272643", 10 "tag": "1", 11 "uname": "", 12 "floor": "0", 13 "time": "1465973899", 14 "title": "stupid", 15 "portrait": "uploads/image/portrait/1465783454.png", 16 "sex": "0", 17 "nickname": "Rick " 18 }, 19 { 20 "id": "19", 21 "postid": "23", 22 "username": "876D276C34C51EC381D98A4959F10EA3", 23 "tag": "1", 24 "uname": "", 25 "floor": "0", 26 "time": "1465969540", 27 "title": "stupid", 28 "portrait": "uploads/image/portrait/1465783454.png", 29 "sex": "0", 30 "nickname": "Rick " 31 } 32 ], 33 [ 34 { 35 "id": "26", 36 "postid": "24", 37 "username": "13760272643", 38 "tag": "1", 39 "uname": "", 40 "floor": "0", 41 "time": "1465981250", 42 "title": "stupid", 43 "portrait": "uploads/image/portrait/1465783454.png", 44 "sex": "0", 45 "nickname": "Rick " 46 }, 47 { 48 "id": "25", 49 "postid": "24", 50 "username": "876D276C34C51EC381D98A4959F10EA3", 51 "tag": "1", 52 "uname": "", 53 "floor": "0", 54 "time": "1465981246", 55 "title": "stupid", 56 "portrait": "uploads/image/portrait/1465783454.png", 57 "sex": "0", 58 "nickname": "Rick " 59 } 60 ], 61 [ 62 { 63 "id": "27", 64 "postid": "25", 65 "username": "13760272643", 66 "tag": "1", 67 "uname": "", 68 "floor": "0", 69 "time": "1465987326", 70 "title": "stupid", 71 "portrait": "uploads/image/portrait/1465783454.png", 72 "sex": "0", 73 "nickname": "Rick " 74 } 75 ] 76 ], 77 "data2": [ 78 [ 79 { 80 "id": "28", 81 "postid": "23", 82 "username": "13760272643", 83 "nickname": "Rick ", 84 "contents": "就看看", 85 "likes": "0", 86 "comments": "0", 87 "time": "1465981162", 88 "floor": "4", 89 "title": "stupid", 90 "portrait": "uploads/image/portrait/1465783454.png", 91 "sex": "0" 92 }, 93 { 94 "id": "27", 95 "postid": "23", 96 "username": "13760272643", 97 "nickname": "Rick ", 98 "contents": "回家看看", 99 "likes": "0", 100 "comments": "0", 101 "time": "1465981158", 102 "floor": "3", 103 "title": "stupid", 104 "portrait": "uploads/image/portrait/1465783454.png", 105 "sex": "0" 106 }, 107 { 108 "id": "26", 109 "postid": "23", 110 "username": "13760272643", 111 "nickname": "Rick ", 112 "contents": "很可靠快结婚", 113 "likes": "0", 114 "comments": "0", 115 "time": "1465974612", 116 "floor": "2", 117 "title": "stupid", 118 "portrait": "uploads/image/portrait/1465783454.png", 119 "sex": "0" 120 }, 121 { 122 "id": "19", 123 "postid": "23", 124 "username": "876D276C34C51EC381D98A4959F10EA3", 125 "nickname": "Rick ", 126 "contents": "不知道为什么，突然就想评论一下！！！", 127 "likes": "0", 128 "comments": "0", 129 "time": "1465969568", 130 "floor": "1", 131 "title": "stupid", 132 "portrait": "uploads/image/portrait/1465783454.png", 133 "sex": "0" 134 } 135 ], 136 [ 137 { 138 "id": "30", 139 "postid": "24", 140 "username": "13760272643", 141 "nickname": "Rick ", 142 "contents": "吃v", 143 "likes": "0", 144 "comments": "0", 145 "time": "1465981260", 146 "floor": "2", 147 "title": "stupid", 148 "portrait": "uploads/image/portrait/1465783454.png", 149 "sex": "0" 150 }, 151 { 152 "id": "29", 153 "postid": "24", 154 "username": "13760272643", 155 "nickname": "Rick ", 156 "contents": "回家开门", 157 "likes": "0", 158 "comments": "0", 159 "time": "1465981255", 160 "floor": "1", 161 "title": "stupid", 162 "portrait": "uploads/image/portrait/1465783454.png", 163 "sex": "0" 164 } 165 ], 166 [] 167 ], 168 "data3": [ 169 { 170 "id": "6", 171 "postid": "22", 172 "floor": "4", 173 "content": "滚滚滚", 174 "time": "1465971570", 175 "username": "13760272643", 176 "nickname": "Joker" 177 } 178 ] 179 }
而要实现的UI为：
这里导致的情况就是，返回的json有几个数据集（data1，data2，data3等），里面包含的数据集也字段不一样，取到之后要再次解析（Android、ios、html5都要）加工才可以用，显然不符合规范的。
2）经过讨论，决定可以采用原生sql的形式去返回数据，这里涉及到5张表：
分别为点赞记录表，主贴表，评论表，楼中楼表，还有一张用户表（menber）
经过修改如下：
1 public function mess2() 2 { 3 $da['username'] = $_POST['username']; 4 $db1 = M('postmessage'); 5 $Dao = M(); 6 7 $index = 0; 8 $all[] = array(); //二维数组 9 10 $postid = $db1->field('postid')->order('time desc')->where($da)->select(); 11 //查看主贴的点赞和评论 12 foreach($postid as $k => $v) { 13 $th = $postid[$k]['postid']; 14 $list1 = $Dao->query("select pl.id,pl.postid,pl.username,u.enname,u.sexnew,u.portrait,pl.time,concat(u.enname,'点赞了我的帖子') as title, 15 concat('赞了我的帖子：',pm.title) as content,pl.floor as floor from yne_postlikes as pl left join yne_postmessage as pm 16 on pl.postid = pm.postid right join yne_member as u on u.username = pl.username where pl.postid ="."$th"); 17 $list2 = $Dao->query("select pc.id,pc.postid,pc.username,u.enname,u.sexnew,u.portrait,pc.time,pc.contents as title, 18 concat('评论了我的帖子：',pm.title) as content,pc.floor as floor from yne_postcomments as pc left join yne_postmessage as pm on pc.postid = pm.postid 19 right join yne_member as u on u.username = pc.username where pm.postid ="."$th"); 20 21 addArray($all,$list1,&$index); 22 addArray($all,$list2,&$index); 23 } 24 25 //查看层帖的点赞和层帖的回复（楼中楼） 26 $db2 = M('postcomments'); 27 $id = $db2->field('id')->order('time desc')->where($da)->select(); 28 foreach($id as $k => $v){ 29 $ids = $id[$k]['id']; 30 $list3 = $Dao->query("SELECT pm.id, pm.postid, pm.username, u.enname, u.sexnew, u.portrait, pl.time, 31 concat( u.enname, '点赞了我的评论' ) AS title,concat( '赞了我的评论：', pm.contents ) AS content, pm.floor AS floor FROM yne_postcomments AS pm 32 LEFT JOIN yne_postlikes AS pl ON pl.postid = pm.postid AND pl.floor = pm.floor RIGHT JOIN yne_member AS u ON u.username = pl.uname 33 WHERE pm.id ="."$ids"); 34 $list4 = $Dao->query("select pm.id,pm.postid,pm.username,u.enname,u.sexnew,u.portrait,pm.time,pd.content as title, 35 concat('回复了我的评论：',pm.contents) as content,pm.floor as floor from yne_postcomments as pm left join yne_postdouble as pd 36 on pd.postid = pm.postid and pd.floor = pm.floor right join yne_member as u on u.username = pd.username where pm.id = "."$ids"); 37 // addArray($all,$list3,&$index); 38 addArray($all,$list4,&$index); 39 } 40 //最后根据time进行二维数组排序 41 print_r($all);exit; 42 } 43 44 45 //index的引用传递 46 function addArray($all[] = arrray(),$list = arrray(),$index) { 47 //遍历list1(list1,可能为一维，也可能是二维) 48 foreach($list1 as $key =>$value) { 49 if(is_array($value)) { //二维 50 //解析每一维数组 51 $all[$index] = array( 52 'id' => $value['id'], 53 'postid' => $value['postid'], 54 'username' => $value['username'], 55 'enname' => $value['enname'], 56 'sexnew' => $value['sexnew'], 57 'portrait' => $value['portrait'], 58 'time' => $value['time'], 59 'title' => $value['title'], 60 'content' => $value['content'], 61 'floor' => $value['floor'] 62 ); 63 index++; 64 65 } 66 } 67 }
这里，需要注意几点，每次sql取得的是一个二维数组如：
1 Array 2 ( 3 [0] => Array 4 ( 5 [id] => 20 6 [postid] => 23 7 [username] => 13760272643 8 [enname] => Joker 9 [sexnew] => 男 10 [portrait] => uploads/image/portrait/default.png 11 [time] => 1465973899 12 [title] => Joker点赞了我的帖子 13 [content] => 赞了我的帖子：greyer 14 [floor] => 0 15 ) 16 17 [1] => Array 18 ( 19 [id] => 19 20 [postid] => 23 21 [username] => 876D276C34C51EC381D98A4959F10EA3 22 [enname] => っ 丶 LNR 23 [sexnew] => 0 24 [portrait] => http://qzapp.qlogo.cn/qzapp/1105262825/876D276C34C51EC381D98A4959F10EA3/100 25 [time] => 1465969540 26 [title] => っ 丶 LNR点赞了我的帖子 27 [content] => 赞了我的帖子：greyer 28 [floor] => 0 29 ) 30 )
需要综合每次sql返回的结果，经过多次尝试，直接使用array_merge()，array_merge_recursive()，都不太理想，比较对于php使用较少。
这里换了一种思路，就是类似java上面的二维数组，根据索引去解析每次返回sql结果集的二维数据，并记录当前解析得到的一维数组的个数（index），
最后再添加到一个新的二维数组中。再将二维数组转为json返回：
1 { 2 "status": "Y", 3 "msg": "成功", 4 "data": [ 5 { 6 "id": "27", 7 "postid": "25", 8 "username": "13760272643", 9 "enname": "Joker", 10 "sexnew": "男", 11 "portrait": "uploads/image/portrait/default.png", 12 "time": "1465987326", 13 "title": "Joker点赞了我的帖子", 14 "content": "赞了我的帖子：stupid", 15 "floor": "0" 16 }, 17 { 18 "id": "30", 19 "postid": "24", 20 "username": "13760272643", 21 "enname": "Joker", 22 "sexnew": "男", 23 "portrait": "uploads/image/portrait/default.png", 24 "time": "1465981260", 25 "title": "吃v", 26 "content": "评论了我的帖子：qwertyuop ", 27 "floor": "2" 28 }, 29 { 30 "id": "29", 31 "postid": "24", 32 "username": "13760272643", 33 "enname": "Joker", 34 "sexnew": "男", 35 "portrait": "uploads/image/portrait/default.png", 36 "time": "1465981255", 37 "title": "回家开门", 38 "content": "评论了我的帖子：qwertyuop ", 39 "floor": "1" 40 }, 41 { 42 "id": "26", 43 "postid": "24", 44 "username": "13760272643", 45 "enname": "Joker", 46 "sexnew": "男", 47 "portrait": "uploads/image/portrait/default.png", 48 "time": "1465981250", 49 "title": "Joker点赞了我的帖子", 50 "content": "赞了我的帖子：qwertyuop ", 51 "floor": "0" 52 }, 53 { 54 "id": "25", 55 "postid": "24", 56 "username": "876D276C34C51EC381D98A4959F10EA3", 57 "enname": "っ 丶 LNR", 58 "sexnew": "0", 59 "portrait": "http://qzapp.qlogo.cn/qzapp/1105262825/876D276C34C51EC381D98A4959F10EA3/100", 60 "time": "1465981246", 61 "title": "っ 丶 LNR点赞了我的帖子", 62 "content": "赞了我的帖子：qwertyuop ", 63 "floor": "0" 64 }, 65 { 66 "id": "28", 67 "postid": "23", 68 "username": "13760272643", 69 "enname": "Joker", 70 "sexnew": "男", 71 "portrait": "uploads/image/portrait/default.png", 72 "time": "1465981162", 73 "title": "就看看", 74 "content": "评论了我的帖子：greyer", 75 "floor": "4" 76 }, 77 { 78 "id": "27", 79 "postid": "23", 80 "username": "13760272643", 81 "enname": "Joker", 82 "sexnew": "男", 83 "portrait": "uploads/image/portrait/default.png", 84 "time": "1465981158", 85 "title": "回家看看", 86 "content": "评论了我的帖子：greyer", 87 "floor": "3" 88 }, 89 { 90 "id": "25", 91 "postid": "22", 92 "username": "15072475892", 93 "enname": "Joker", 94 "sexnew": "男", 95 "portrait": "uploads/image/portrait/default.png", 96 "time": "1465978020", 97 "title": "很健康", 98 "content": "回复了我的评论：Hjvf ", 99 "floor": "6" 100 }, 101 { 102 "id": "25", 103 "postid": "22", 104 "username": "15072475892", 105 "enname": "Rick ", 106 "sexnew": "0", 107 "portrait": "uploads/image/portrait/1465783454.png", 108 "time": "1465975417", 109 "title": "Rick 点赞了我的评论", 110 "content": "赞了我的评论：Hjvf ", 111 "floor": "6" 112 }, 113 { 114 "id": "23", 115 "postid": "22", 116 "username": "15072475892", 117 "enname": "Rick ", 118 "sexnew": "0", 119 "portrait": "uploads/image/portrait/1465783454.png", 120 "time": "1465975415", 121 "title": "Rick 点赞了我的评论", 122 "content": "赞了我的评论：Wgyii ", 123 "floor": "4" 124 }, 125 { 126 "id": "25", 127 "postid": "22", 128 "username": "15072475892", 129 "enname": "Joker", 130 "sexnew": "男", 131 "portrait": "uploads/image/portrait/default.png", 132 "time": "1465975057", 133 "title": "Joker点赞了我的评论", 134 "content": "赞了我的评论：Hjvf ", 135 "floor": "6" 136 }, 137 { 138 "id": "23", 139 "postid": "22", 140 "username": "15072475892", 141 "enname": "Joker", 142 "sexnew": "男", 143 "portrait": "uploads/image/portrait/default.png", 144 "time": "1465975035", 145 "title": "Joker点赞了我的评论", 146 "content": "赞了我的评论：Wgyii ", 147 "floor": "4" 148 }, 149 { 150 "id": "26", 151 "postid": "23", 152 "username": "13760272643", 153 "enname": "Joker", 154 "sexnew": "男", 155 "portrait": "uploads/image/portrait/default.png", 156 "time": "1465974612", 157 "title": "很可靠快结婚", 158 "content": "评论了我的帖子：greyer", 159 "floor": "2" 160 }, 161 { 162 "id": "20", 163 "postid": "23", 164 "username": "13760272643", 165 "enname": "Joker", 166 "sexnew": "男", 167 "portrait": "uploads/image/portrait/default.png", 168 "time": "1465973899", 169 "title": "Joker点赞了我的帖子", 170 "content": "赞了我的帖子：greyer", 171 "floor": "0" 172 }, 173 { 174 "id": "25", 175 "postid": "22", 176 "username": "15072475892", 177 "enname": "Joker", 178 "sexnew": "男", 179 "portrait": "uploads/image/portrait/default.png", 180 "time": "1465971596", 181 "title": "哈哈哈", 182 "content": "回复了我的评论：Hjvf ", 183 "floor": "6" 184 }, 185 { 186 "id": "23", 187 "postid": "22", 188 "username": "15072475892", 189 "enname": "Joker", 190 "sexnew": "男", 191 "portrait": "uploads/image/portrait/default.png", 192 "time": "1465971570", 193 "title": "滚滚滚", 194 "content": "回复了我的评论：Wgyii ", 195 "floor": "4" 196 }, 197 { 198 "id": "19", 199 "postid": "23", 200 "username": "876D276C34C51EC381D98A4959F10EA3", 201 "enname": "っ 丶 LNR", 202 "sexnew": "0", 203 "portrait": "http://qzapp.qlogo.cn/qzapp/1105262825/876D276C34C51EC381D98A4959F10EA3/100", 204 "time": "1465969568", 205 "title": "不知道为什么，突然就想评论一下！！！", 206 "content": "评论了我的帖子：greyer", 207 "floor": "1" 208 }, 209 { 210 "id": "19", 211 "postid": "23", 212 "username": "876D276C34C51EC381D98A4959F10EA3", 213 "enname": "っ 丶 LNR", 214 "sexnew": "0", 215 "portrait": "http://qzapp.qlogo.cn/qzapp/1105262825/876D276C34C51EC381D98A4959F10EA3/100", 216 "time": "1465969540", 217 "title": "っ 丶 LNR点赞了我的帖子", 218 "content": "赞了我的帖子：greyer", 219 "floor": "0" 220 } 221 ] 222 }
