在posts表中显示类别和标记(一到许多关系)

I am developing a CMS. For the post categories and tags, I looked at the WordPress database, adopted its style and made some changes.

我正在开发一个CMS。对于post类别和标签，我查看了WordPress数据库，采用了它的风格并做了一些修改。

I have 3 tables:

我有3个表:

table 1: posts (post_id,post_title)
table 2: terms (term_id,term_title,term_type)
table 3: term_relationships (tr_id,tr_post_id,tr_term_ir)

In table terms: term_type is either cat(which means it is a category) or tag(which means it is a tag).

在表项中:term_type要么是cat(表示它是一个类别)，要么是标记(表示它是一个标记)。

I want to show lists of posts for management, but I can't figure out the right way to show cats and tags for each post (like WordPress posts page in the admin panel).

我想要显示管理文章的列表，但是我不能找到正确的方式来显示每个文章的猫和标签(就像管理面板中的WordPress贴子页面)。

This is my attempt so far:

这是我迄今为止的尝试:

$posts_query=$db->query("SELECT `post_id`,`post_title` FROM `posts` ORDER BY `post_id` DESC");
while($post=mysqli_fetch_assoc($posts_query)){
     echo '<td>'.$post['post_title'].'</td>';
     $cats_query=$db->query("SELECT tr_term_id FROM term_relationships WHERE tr_post_id='{$post['post_id']}'") or die(mysqli_error($db->con));
     $term_ids='';
     while($cat=mysqli_fetch_assoc($cats_query)){
          $term_ids .=$cat['tr_term_id'].','; 
     }
     $term_ids= trim($term_ids,',');
     $cats=$db->query("SELECT `term_id`,`term_title` FROM `terms` WHERE `term_id` IN($term_ids) AND `term_type`='cat'");
          echo '<td>';
     while($cat= mysqli_fetch_assoc($cats)){
          echo $cat['term_title'].', ';
     }
     echo '</td>';
     }

It does the job but it gets so heavy and hard-to-understand. I don't think this is the right way to do it (I should add even one more while and query for tags!).

它做了工作，但它变得如此沉重和难以理解。我不认为这是正确的方法(我应该再添加一个，然后查询标签!)

Is there a better way to do this?

有没有更好的办法?

2 个解决方案

#1

try this:

试试这个:

      $posts_query=$db->query("SELECT p.`post_id`,p.`post_title`,GROUP_CONCAT((case when t.term_type='tag' then t.`term_title` end) SEPARATOR ',') as tags, GROUP_CONCAT((case when t.term_type='cat' then t.`term_title` end) SEPARATOR ',') as cats,
        GROUP_CONCAT((case when t.term_type not in('cat','tag') then t.`term_title` end) SEPARATOR ',') as others FROM `posts` p
        INNER JOIN  term_relationships  tr ON tr.tr_post_id=p.`post_id`
        INNER JOIN `terms` t ON  t.`term_id`=tr.tr_term_id
        GROUP BY p.`post_id` 
        ORDER BY p.`post_id` DESC");
        while($post=mysqli_fetch_assoc($posts_query)){
             echo '<td>'.$post['post_title'].'</td>';
             /*$cats_query=$db->query("SELECT tr_term_id FROM term_relationships WHERE tr_post_id='{$post['post_id']}'") or die(mysqli_error($db->con));
             $term_ids='';
             while($cat=mysqli_fetch_assoc($cats_query)){
                  $term_ids .=$cat['tr_term_id'].','; 
             }
             $term_ids= trim($term_ids,',');*/
             /*$term_ids= $post['term_ids'];
             $cats=$db->query("SELECT `term_id`,`term_title` FROM `terms` WHERE `term_id` IN($term_ids) AND `term_type`='cat'");
                  echo '<td>';
             while($cat= mysqli_fetch_assoc($cats)){
                  echo $cat['term_title'].', ';
             }*/
             echo '<td>';
             echo $post['tags'];
             echo '</td>';
             echo '<td>';
             echo $post['cats'];
             echo '</td>';
             echo '<td>';
             echo $post['others'];
             echo '</td>';
             }

#2

You could do all the select in one query ..

您可以在一个查询中完成所有的选择。

 SELECT `posts`.`post_id`, `posts`.`post_title` , `term_relationships`.`tr_term_id`, `terms`.`term_title`, 
 FROM `posts` as
 INNER JOIN `term_relationships` on `term_relationships`.`tr_post_id` = `posts`.`post_id`
 INNER JOIN `terms` on  `terms`.`term_id` = `term_relationships`.`tr_term_id`
 ORDER BY `posts`.`post_id` DESC

#1