Joining tags table to posts table in Codeigniter

Question

I need some help retrieving posts that belong to a specific group of tags. I am working with Codeigniter and MySQL.

My tables look like this:

posts - postid, title, date, etc

tags - tagid, tag

post_tags - postid, tagid

This is the function I am using in my model:

function get_posts($tags) {

    $this->db->select('*');
    $this->db->from('posts');
    $this->db->order_by('date', 'DESC');
    $this->db->join('post_tags', 'posts.postid = post_tags.postid');
    $this->db->join('tags', 'post_tags.tagid = tags.tagid');
    $this->db->where_in('tags.tag', $tags);
    $q = $this->db->get();
    return $q->result();
}

So far, the query is working the way I want and returning all the proper posts.

However, while looping through the result and setting up the specific post information, only the first tag is being listed per post.

Is it possible to restucture the query so that each post includes all the tags that match the original $tags array?

Thanks!

swatkins swatkins · Accepted Answer · 2011-10-17T18:50:40

I've not tested this, but I think you need to add a join type to the query:

function get_posts($tags) {
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->order_by('date', 'DESC');
    $this->db->join('post_tags', 'posts.postid = post_tags.postid');

    // add the "right" keyword to produce a RIGHT JOIN
    $this->db->join('tags', 'post_tags.tagid = tags.tagid', 'right'); 

    $this->db->where_in('tags.tag', $tags);
    $q = $this->db->get();
    return $q->result();
}

http://codeigniter.com/user_guide/database/active_record.html

Joining tags table to posts table in Codeigniter

1 Answers