我有两个表，一个是post表，另一个是comment表。我使用post_id作为foreign_key。现在我想得到，一个有所有评论的帖子。并且响应应该是这种格式。
{
"posid":1,
"post_name":"testpost",
"comments":[{
"comment_id":1,
"comment_des":"testcoment"
},{
"comment_id":2,
"comment_des":"testcoment2"
}
]
}
任何人都可以为我编写这种类型的响应的简单SQL查询吗？
我在codeigniter中尝试了下面的查询，但这返回了多个结果，意味着一个帖子两次，因为一个帖子包含两个评论。
$this->db->select("p.post_id,p.post_desc,p.post_time ,c.id,c.comment_desc,c.comment_time");
$this->db->join("asoc_comments as c","p.post_id = c.post_id","INNER");
$response = $this->db->get("asgn_posts as p")->result();
活动记录示例，其中我们循环结果以正确格式化输出，而不为每个帖子获取多个结果行：
$q = $this->db->select('post_id,post_desc,post_time')->get('asgn_posts');
$data = array();
foreach ($q->results() as $p):
$qc = $this->db->select('id,comment_desc,comment_time')->where('post_id',$p->post_id)->get('asoc_comments');
$p->comments = $qc->results();
$data[] = $p;
endforeach;
return $data;
您可以编写此查询来显示您的结果：
SELECT p.post_id as 'posid',p.post_name,(SELECT c.id as 'comment_id' , c.comment_desc as 'comment_des' from asoc_comments c where c.post_id = p.post_id) as 'comments' from asgn_posts p
例如
$response=$this->db->query("SELECT p.post_id as 'posid',p.post_name,(SELECT c.id as 'comment_id' , c.comment_desc as 'comment_des' from asoc_comments c where c.post_id = p.post_id) as 'comments' from asgn_posts p")->result();
您可以通过使用行SQL或活动记录连接这两个表来完成此任务。
作为
public function your_model_method(){
$this->db->select('table_1.*,table_2.*')->from('table_1');
$this->db->join('table_2','table_2.key_field=table_1.key_field');
return $this->db->get()->result_array();
}
希望它能有所帮助。。
谢谢！