如何通过分组两列来防止重复？ MYSQL /笨

Question

I have a predicament where I'm trying to make a facebook-style wall where you have the wall table, users table, wall post table and images table (for this scenario). To simplify the process, the image is posted in the images table, and an entry is made as a wall post. It groups by wall id but when comments are made, it repeats the wall post for every new comment. Essentially I just want ONE post per wall post.

MYSQL Query (Codeigniter):

    $this->db->select('*');
    $this->db->from('wall_posts AS p');
    $this->db->join('users AS k', 'p.wall_poster = k.user_id AND p.wall_owner = k.user_id', 'left');
    $this->db->group_by('p.wall_id');
    $this->db->where('p.wall_poster', $pid); // $pid = given user id
    $this->db->or_where('p.wall_owner', $pid);
    $this->db->order_by('p.wall_id', 'desc');
    $this->db->limit($lim); // $lim = limit number

    return $this->db->get();

Table example

    wall_id|owner_id|reference_id|wall_poster
    1      |0        |55         | 2
    2      |0        |30         | 1
    3      |0        |32         | 2
    4      |2        |19         | 3
    5      |0        |0          | 4
    6      |0        |0          | 2
    7      |0        |32         | 3
    8      |0        |18         | 4

Now, you can see that ID 3 and ID 7 has the same reference_id (image in this case), so later when the result is displayed, any posts referencing that image will be displayed multiple times instead of just once. I also have a column called post_type to help afterwards in the controller which determines if it's a normal post, comment, an image post, comment of an image etc...

Thanks ahead of time! This is bugging me to no end and I'd like to keep this as a single query if possible.

Answer 1

you could try select distinct, but you'd need to narrow it down to just that field. In other words, try something like this:

Edited for codeigniter method for distinct()

$this->db->distinct();
$this->db->select ('reference_id');
$this->db->from('wall_posts AS p');
$this->db->join('users AS k', 'p.wall_poster = k.user_id AND p.wall_owner = k.user_id', 'left');
$this->db->group_by('p.wall_id');
$this->db->where('p.wall_poster', $pid); // $pid = given user id
$this->db->or_where('p.wall_owner', $pid);
$this->db->order_by('p.wall_id', 'desc');

Alternately you could try:

$this->db->select('DISTINCT(reference_id)');