根据帖子ID检索标签 [英] Retrieving tags based on post ID

问题描述

我有三个表： posts ， post_tags 和标签。一个帖子可以有很多标签，一个标签可以属于许多帖子。由于这种多对多的关系，我做了一个 post_tags 表。它有两个字段： p_id 和 t_id 。它们都是posts表和tags表的外键。现在，当我运行我的PHP方法来获取最新的帖子，我想在一个查询中检索属于该帖子的标签。仅供参考，以下是这三个表：

帖子

  p_id | c_id | u_id |标题|身体|发表| 
 ---------------------------------------------- ------------------------ 
 | 1 | 1 | 1 |第一篇| lorem ipsum | 2012-01-27 18:37:47 | 
  

post_tags

  | p_id | t_id | 
 --------------- 
 | 1 | 3 | 
  

标签

  | t_id |名称| lug | 
 ------------------------------------ 
 | 3 |编程|编程| 
 

这里是我现在使用的PHP代码来获取没有标签的最新帖子：

  public function getLatestPosts（）
 {
 $ query = $ this-> db-> query SELECT title，clean_title，body，published FROM posts ORDER BY published DESC'）; 
 $ blogPosts = array（）; 
 foreach（$ query-> result（）as $ row）
 {
 $ blogPosts [] = array（'title'=> $ row-> title，
'clean_title'=> $ row-> clean_title，
'body'=> $ row-> body，
'published'=> $ row-> 
} 
 
 return $ blogPosts; 
} 
  

如何调整我的查询以获取标签的名称和

解决方案

隐含加入：

  SELECT title，clean_title，body，published，name，slug 
 FROM posts，posts_tags，tags 
 WHERE posts.p_id = posts_tags.p_id AND posts_tags.t_id = tags.t_id 
 ORDER BY published DESC 
  

显式连接：

  SELECT title，clean_title，body，published，name，slug 
 FROM posts 
 LEFT JOIN posts_tags ON posts.p_id = posts_tags.p_id 
 LEFT JOIN标签on posts_tags.t_id = tags.t_id 
 ORDER BY已发布DESC 
  

一次性查看正确的规范化数据库模式会更新。

I have three tables: posts, post_tags and tags. One post can have many tags, and one tag can belong to many posts. Because of that many-to-many relationship, I made a post_tags table. It has two fields: p_id and t_id. They're both foreign keys to the posts table and tags table respectively. Now, when I run my PHP method to get the latest posts, I want to also retrieve the tags belonging to that post in one query. Just for reference, here's those three tables:

posts

| p_id | c_id | u_id |   title   |     body    |      published      |
----------------------------------------------------------------------
|  1   |  1   |   1  | first post| lorem ipsum | 2012-01-27 18:37:47 |

post_tags

| p_id | t_id |
---------------
|  1   |  3   |

tags

| t_id |     name    |     slug    |
------------------------------------
|  3   | programming | programming |

Here's the PHP code I'm using now to get the latest posts without the tags:

public function getLatestPosts()
{
    $query = $this->db->query('SELECT title, clean_title, body, published FROM posts ORDER BY published DESC');
    $blogPosts = array();
    foreach ($query->result() as $row)
    {
        $blogPosts[] = array('title' => $row->title,
                             'clean_title' => $row->clean_title,
                             'body' => $row->body,
                             'published' => $row->published);
    }

    return $blogPosts;
}

How can I adapt my query to get the name and slug of the tags belonging to each post?

Thanks for any help!

解决方案

Implicit join:

SELECT title, clean_title, body, published, name, slug
FROM posts, posts_tags, tags
WHERE posts.p_id=posts_tags.p_id AND posts_tags.t_id=tags.t_id
ORDER BY published DESC

Explicit join:

SELECT title, clean_title, body, published, name, slug
FROM posts
LEFT JOIN posts_tags ON posts.p_id=posts_tags.p_id
LEFT JOIN tags ON posts_tags.t_id=tags.t_id
ORDER BY published DESC

It's refreshing to see a correct, normalized database schema for once.

这篇关于根据帖子ID检索标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！