Query for post and tags in same query

Question

I have 3 tables with those relevent fields

posts (id,title)
tags (id,name)
tags_map (id,id_post,id_tag)

I'm trying like this:

$this->db->select('posts.*,tags.name');
$this->db->from('posts');
$this->db->join('tags_map', 'tags.id_post = posts.id');
$this->db->join('tags', 'tags.id = tags_map.id_tag');
$this->db->group_by('posts.id'); // I can comment this line and same result

This looks like it, but even when are more than one tags_map records, it only returns one,

How can I return them all, in one query?

-edit-

I kind of solved it, but requires php handling

$this->db->select('posts.*,group_concat(tags.name) as thetags');

This would retrun a field thethags = 'tag1,tag2,tag3'

But I would like to get other fileds on the tags table..

And, with for example:

$this->db->select('posts.*,group_concat(tags.name) as 'thetags',group_concat(tags.relevance) as 'therelevances');

I'm not sure if I can trust the order?

Answer 1

Its better if you don't go for a group_concat solution because its not reliable due to character length it has a default limit of 1024 characters to concatenate but it can be increased which is defined in manual but you cannot fully rely on defined limit instead you can handle it in your application layer (php) like you are fetching data and somewhere you want to display posts and its related tags you can still use your join query with order by posts now each post can have more than one tags so the result set will have duplicate posts data due to each post row will have one tag and another row with same post will have second tag and so on now when you loop through your results display each post only once by adding some check

$this->db->select('p.*,t.*');
$this->db->from('posts p');
$this->db->join('tags_map tm', 't.id_post = p.id');
$this->db->join('tags t', 't.id = tm.id_tag');
$this->db->order_by('p.id asc,t.id asc');
$results = $this->db->get();

In above query i have added t.* to select all post and its related tags now in loop $currentParent will have a previous post id in loop check if its a same post then display its tags if condition returns false then its a new post display post heading using markup (h1), i have selected all columns from both table but you should add only needed columns from tags table and if post and tag table have columns of same name then in query defined them with different alias

$currentParent = false;
foreach ($results as $post) {
    if ($currentParent != $post->id) {
        echo '<h1>' . $post->title . '</h1>';
        $currentParent = $post->id;
    }
    echo '<p>' . $post->name . '</p>'; /** here list tags info*/
    echo '<p>' . $post->tag_other_details . '</p>'; 
    ...
}

Resultant markup will be like

<h1>Post title 1</h1>
    <p>tag 1</p>
    <p>tag 2</p>
<h1>Post title 2</h1>
    <p>tag 3</p>...

If you are not displaying data and you are using it in some where else (web service) then still use one joined query and transform your data by creating array/object each post has an array of its related tags something like below

$posts =array();
foreach ($results as $post) {
 $posts[$post->id]['title'] = $post->title;
 $posts[$post->id]['tags'][] =array('id' => $tag->id ,'name' =>$post->name);
}

there is another way which is highly not recommended get posts and in loop get tags by running query now this solution will work it but will involve unwanted queries so that there will be an extra query executed for each post.

Now if your really want to stick with group_concat approach to answer your question I'm not sure if I can trust the order? there is also a way you can add order by in group_concat so that the resultant concatenated tags will be ordered and for second column you can put same ordering criteria

$this->db->select('p.*,group_concat(t.name order by t.id) as thetags,
                       group_concat(t.relevance order by t.id) as therelevances');
$this->db->from('posts p');
$this->db->join('tags_map tm', 't.id_post = p.id');
$this->db->join('tags t', 't.id = tm.id_tag');
$this->db->group_by('p.id');