从codeigniter中的多个表中获取数据？

Question

i have two table, one is post table and other one is comment table.in which i am using post_id as foreign_key. Now i want to get, a post with all its comments. and response should be in this format.

 { 
    "posid":1,
    "post_name":"testpost",
    "comments":[{
                    "comment_id":1,
                    "comment_des":"testcoment"
                },{
                    "comment_id":2,
                    "comment_des":"testcoment2"
                }
            ]
    }

Can any one write simple SQL query for me for this type of response?

I tried below query in codeigniter , but this return multiple result, mean one post two time, because one post contains two comments.

  $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();

Answer 1

Active record example where we loop the results in order to format the output right without getting multiple result rows for each post:

$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;