在同一查询中查询帖子和标签 [英] Query for post and tags in same query

问题描述

我有3个带有相关字段的表

I have 3 tables with those relevent fields

帖子(编号，标题)
标签(ID，名称)
tags_map (id，id_post，id_tag)

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

我正在尝试这样:

$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

看起来像这样，但是即使有多个tag_map记录，它也只会返回一个，

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?

-编辑-

我已经解决了，但是需要php处理

I kind of solved it, but requires php handling

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

这将重新运行一个字段thethags ='tag1，tag2，tag3'

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

但是我想在标签表上获取其他文件.

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

例如，

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

推荐答案

如果不使用手册，但是您不能完全依靠已定义的限制，而可以像在获取数据一样在应用程序层(php)中处理它，就像要显示帖子及其相关标签的地方一样，您仍然可以对帖子按顺序使用联接查询，因为每个帖子可以具有多个标签，因此结果集将具有重复的帖子数据，这是因为每个帖子行将具有一个标签，而同一帖子的另一行将具有第二个标签，依此类推，以此类推，现在当您遍历结果时，通过添加一些检查仅显示一次帖子

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

在上面的查询中，我添加了t.*来选择所有帖子及其循环中的相关标签$currentParent将在循环中具有先前的帖子ID，如果条件相同则返回false，然后显示其标签，如果条件返回false，则显示其标签一个使用标记(h1)的新帖子显示帖子标题，我已经从两个表中选择了所有列，但是您应该仅从标记表中添加所需的列，并且如果帖子和标记表具有相同名称的列，则在查询中使用不同的别名定义它们

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>'; 
    ...
}

结果标记将类似于

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

如果您不显示数据，而在其他地方(Web服务)使用它，则仍然使用一个联合查询并通过创建数组/对象来转换数据，每个帖子都有其相关标签的数组，如下所示

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.

现在，如果您真的想坚持使用group_concat方法来回答问题 I'm not sure if I can trust the order? ，那么还有一种方法可以在group_concat中添加order by，从而使结果级联标签将被订购，第二栏您可以输入相同的订购条件

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');

这篇关于在同一查询中查询帖子和标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！