优化 SQL 查询

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

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

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

除此之外，我还有一个page_option表（这样我可以存储与页面相关的选项，但我不想有有限的选项列表 - 如果需要，模块可以将自己的选项添加到页面。）

The 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 个怎么办？ ETC。

提前谢谢了！

使用 EAV (实体-属性-值 http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model）模型中您将始终需要处理此类问题。由于查询的复杂性，它们的效率也不是很高（大多数查询都需要旋转）。

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 http://sqlfiddle.com/#!2/e78d0/1.

然后只需加入page表，就是这样:)我省略了该部分，以便将查询集中在分组本身上。