our application has a comment
table which looks like this(rough schema)
id(unique, primary, autoincrement)
post_id(20 varchar)
comment (160 varchar)
user_id(20 varchar)
created_at(timestamp)
how can we arrange unique posts in descending order of the comment posted on them, example
post 1 (date posted 16/03/16)(latest comment 04:00 PM)
post 2 (date posted 29/03/16)(latest comment 03:00 PM)
we are unable to formulate this, since our code is returning first comment not the latest
$this->db->select('*');
$this->db->distinct('post_id');
$this->db->order_by('created_at','DESC');
$this->db->limit(1);
$this->db->group_by('post_id');
$this->db->limit(10); // 10 unique post
$this->db->where('user_id',$user);
$query = $this->db->get('comments');
return $quer->result();