在PHP中使用codeigniter活动记录实现这样的结果集的最有效方法是什么？
[2] => stdClass Object
(
[id] => 12
[title] => 2 sections
[layout_id] => 1
[layout] => stdClass Object
(
[id] => 1
[file] => 3_column.php
[title] => 3 Column
)
[sections] => Array
(
[0] => stdClass Object
(
[module_id] => 12
[section_id] => 1
)
[1] => stdClass Object
(
[module_id] => 12
[section_id] => 2
)
)
)
这里有一些东西可以给我想要的嵌套结果，但我发现这种方法效率低下（忽略每个查询的select *）
public function all()
{
$rows = $this->db->get('modules')->result();
foreach($rows as &$row)
{
$row->layout = $this->db->get_where('layouts', array('id' => $row->layout_id), 1)->first_row();
$row->sections = $this->db->get_where('modules_sections', array('module_id' => $row->id))->result();
}
return $rows;
}
您可以使用一些联接和GROUP_BY子句轻松地将这几个查询转换为一个查询。这种方法最大的缺点是它需要对数据进行分组和定界，但PHP擅长"分解"定界的数据。试试这个：
function all()
{
$this->db->select("`modules`.*, CONCAT(`layouts`.`id`,'".DATA_SUBDELIMITER."',`layouts`.`title`,'".DATA_SUBDELIMITER."',`layouts`.`file`) AS layout, CAST(GROUP_CONCAT(`sections`.`id`,'".DATA_SUBDELIMITER."',`sections`.`title` SEPARATOR '".DATA_DELIMITER."') AS CHAR) AS sections", false);
$this->db->from('modules');
$this->db->join('layouts', 'layouts.id = modules.layout_id');
$this->db->join('modules_sections', 'modules_sections.module_id = modules.id');
$this->db->join('sections', 'sections.id = modules_sections.section_id');
$this->db->group_by('modules.id');
$rows = $this->db->get()->result_array();
foreach($rows as &$row)
{
foreach($row as $k=>&$r)
{
if($k=='layout' || $k=='sections')
{
$new_r = explode(DATA_DELIMITER, $r);
foreach($new_r as &$c)
{
$e = explode(DATA_SUBDELIMITER,$c);
$c = array();
list($c['id'],$c['title']) = $e;
if(!empty($e[2])) $c['file'] = $e[2];
}
if($k=='layout') $new_r = $new_r[0];
$r = $new_r;
}
}
}
return $rows;
}
本例使用DATA_DELIMITER和DATA_SUBDELIMITER作为分隔数据的字符。如果您使用这些常量，那么您可能应该在application/config/contents.php文件中定义它们。也许是这样的：
define('DATA_DELIMITER','||');
define('DATA_SUBDELIMITER','##');
该代码经过测试，结果如下：
Array
(
[0] => Array
(
[id] => 1
[title] => Module 1
[layout_id] => 1
[layout] => Array
(
[title] => 3 Column
[id] => 1
[file] => 3_column.php
)
[sections] => Array
(
[0] => Array
(
[title] => Section 1
[id] => 1
)
[1] => Array
(
[title] => Section 2
[id] => 2
)
)
)
[1] => Array
(
[id] => 2
[title] => Module 2
[layout_id] => 2
[layout] => Array
(
[title] => 2 Column
[id] => 2
[file] => 2_column.php
)
[sections] => Array
(
[0] => Array
(
[title] => Section 1
[id] => 1
)
[1] => Array
(
[title] => Section 3
[id] => 3
)
)
)
[2] => Array
(
[id] => 3
[title] => Module 3
[layout_id] => 1
[layout] => Array
(
[title] => 3 Column
[id] => 1
[file] => 3_column.php
)
[sections] => Array
(
[0] => Array
(
[title] => Section 3
[id] => 3
)
)
)
[3] => Array
(
[id] => 4
[title] => Module 4
[layout_id] => 2
[layout] => Array
(
[title] => 2 Column
[id] => 2
[file] => 2_column.php
)
[sections] => Array
(
[0] => Array
(
[title] => Section 1
[id] => 1
)
[1] => Array
(
[title] => Section 2
[id] => 2
)
[2] => Array
(
[title] => Section 3
[id] => 3
)
[3] => Array
(
[title] => Section 4
[id] => 4
)
)
)
)
高效在这里是一个相对术语。
如果您希望最小化sql server上的查询，您可以始终序列化对象并将其存储在缓存表中，那么您将只有一次查找，但您必须在任何类型的写操作中维护这些对象。除此之外，您可能只需要进行负载测试，看看瓶颈在哪里。有时，多个查询可能比大型多表联接更快。