我有三个表:posts,post_tags和tags。一个帖子可以有多个标签,而一个标签可以属于多个帖子。由于这种多对多关系,我做了一张post_tags桌子。它有两个字段:p_id和t_id。它们都是帖子表和标签表的外键。现在,当我运行PHP方法以获取最新帖子时,我还想在一个查询中检索属于该帖子的标签。仅供参考,这是这三个表:
posts
post_tags
tags
p_id
t_id
帖子
| p_id | c_id | u_id | title | body | published | ---------------------------------------------------------------------- | 1 | 1 | 1 | first post| lorem ipsum | 2012-01-27 18:37:47 |
| p_id | t_id | --------------- | 1 | 3 |
标签
| t_id | name | slug | ------------------------------------ | 3 | programming | programming |
这是我现在用来获取不带标签的最新帖子的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->published); } 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 tags ON posts_tags.t_id=tags.t_id ORDER BY published DESC
一次看到正确的,标准化的数据库架构令人耳目一新。