php - 优化 SQL 查询

Question

我目前正在开发一个内容管理系统，我想听听您对以下方面的看法:

我有一张表，page。让我们假设它看起来像这样

ID | Title | Content
1  | Test  | This is a test

除此之外，我还有一个 page_option 表(因此我可以存储与页面相关的选项，但我不希望有一个有限的选项列表 - 模块可以添加自己的选项如果需要，页面的选项。)

page_option 表可能如下所示:

page_id | option_key  | option_value
1       | background  | red
1       | module1_key | chicken

现在要检索页面对象，我使用 Active Record 类执行以下操作(这是针对此问题的伪编码):

function get_by_id($page_id) {

 $this->db->where('id', $page_id);
 $page_object = $this->db->get('page');

 if($page_object->num_rows() > 0) {

   $page = $page_object->row();
   $this->db->where('page_id', $page_id);
   $options_object = $this->db->get('option');

   if($options_object->num_rows() > 0) {
     $page->options = $options_object->result();
   }

   return $page;

 }

 return $page_object->row();

}

我想知道的是，有没有一种方法可以在一个查询中执行此操作，以便选项键成为我选择中的虚拟列，所以我会得到:

ID | Title | Content        | background | module1_key
1  | Test  | This is a test | red        | chicken

在我的结果中，而不是对每一行进行单独的查询。如果有 10,000 个呢？等等

非常感谢!

Answer 1

使用 EAV ( Entity-Attribute-Value ) 模型，您将始终不得不处理此类问题。由于查询的复杂性，它们的效率也不是很高(其中大多数都需要旋转)。

SELECT page_id,
  MAX(CASE WHEN option_key = 'background' THEN option_value END) background,
  MAX(CASE WHEN option_key = 'module1_key' THEN option_value END) module1_key,
  MAX(CASE WHEN option_key = 'module2_key' THEN option_value END) module2_key
FROM page_option
GROUP BY page_id

例如，给定这张表:

| PAGE_ID |  OPTION_KEY | OPTION_VALUE |
|---------|-------------|--------------|
|       1 |  background |          red |
|       1 | module1_key |      chicken |
|       2 | module1_key |         duck |
|       3 | module1_key |          cow |
|       4 |  background |         blue |
|       4 | module2_key |        alien |
|       4 | module1_key |      chicken |

您将得到以下输出:

| PAGE_ID | BACKGROUND | MODULE1_KEY | MODULE2_KEY |
|---------|------------|-------------|-------------|
|       1 |        red |     chicken |      (null) |
|       2 |     (null) |        duck |      (null) |
|       3 |     (null) |         cow |      (null) |
|       4 |       blue |     chicken |       alien |

fiddle here .

然后只需加入 page 表就可以了 :) 我省略了那部分以便将查询集中在分组本身。