使用PHP CodeIgniter实现article_category与article_case父子关联求助

我有两张数据库表article_category和article_case，期望在网页中按以下结构展示：

文章分类[title]
文章分类[description]
文章案例[title]
文章案例[contents]
文章案例[title]
文章案例[contents]

需要通过foreach循环遍历所有文章分类，每个分类下的article_case需匹配其category_id与article_category表的id。目前无法过滤article_case表，使其仅显示对应父分类的数据。

当前PHP代码（仅生成两张表的数组）：

$this->db->select( "id,title,description" );
$this->db->from( 'article_category' );
$query_article_category = $this->db->get_compiled_select();

$query = $this->db->query( $query_sustainability_category . 'ORDER BY sort ASC ' );
foreach ($query->result_array() as $val) {
        $data['article_category_posts'][$val['id']] = $val;}

$this->db->select( "id,category_id,title,contents" );
$this->db->from( 'article_case' );
$query_article_case = $this->db->get_compiled_select();

$query = $this->db->query( $query_article_case);
$data[ 'article_cases' ] = $query->result_array();

HTML代码：

<?php foreach ($article_category_posts as $key => $val) { ?>
<section>
   <h2><?php echo $val['title']; ?></h2>
   <p><?php echo $val['description']; ?></p>
</section>
<?php foreach ($article_cases as $key => $val) { ?>
<section>
   <h3><?php echo $val['title']; ?></h3>
   <p><?php echo $val['contents']; ?></p>
</section>
<?php } ?>
<?php } ?>

当前代码会显示所有article_case数据，忽略category_id，请问如何修改实现article_case与对应article_category的关联展示？

方式一：PHP端重组数据（推荐） #

在获取文章案例后，将其按category_id分组，循环分类时可直接取出对应案例，效率更高：

修改PHP代码：

// 获取分类数据，直接在查询中添加排序
$this->db->select("id,title,description");
$this->db->from('article_category');
$this->db->order_by('sort', 'ASC');
$query = $this->db->get();
$data['article_category_posts'] = $query->result_array();

// 获取案例数据并按category_id分组
$this->db->select("id,category_id,title,contents");
$this->db->from('article_case');
$query = $this->db->get();
$article_cases = $query->result_array();

// 重组案例数组，key为category_id
$grouped_cases = [];
foreach ($article_cases as $case) {
    $category_id = $case['category_id'];
    if (!isset($grouped_cases[$category_id])) {
        $grouped_cases[$category_id] = [];
    }
    $grouped_cases[$category_id][] = $case;
}
$data['grouped_article_cases'] = $grouped_cases;

修改HTML代码：

<?php foreach ($article_category_posts as $category) { ?>
<section>
   <h2><?php echo $category['title']; ?></h2>
   <p><?php echo $category['description']; ?></p>
</section>
<?php 
// 取出当前分类对应的案例
$category_cases = isset($grouped_article_cases[$category['id']]) ? $grouped_article_cases[$category['id']] : [];
foreach ($category_cases as $case) { ?>
<section>
   <h3><?php echo $case['title']; ?></h3>
   <p><?php echo $case['contents']; ?></p>
</section>
<?php } ?>
<?php } ?>

方式二：循环时直接过滤（适合小数据量场景） #

无需重组数组，在循环案例时增加判断，仅显示当前分类的案例：

修改HTML代码：

<?php foreach ($article_category_posts as $category) { ?>
<section>
   <h2><?php echo $category['title']; ?></h2>
   <p><?php echo $category['description']; ?></p>
</section>
<?php foreach ($article_cases as $case) { 
// 判断案例的category_id是否匹配当前分类id
if ($case['category_id'] == $category['id']) { ?>
<section>
   <h3><?php echo $case['title']; ?></h3>
   <p><?php echo $case['contents']; ?></p>
</section>
<?php } } ?>
<?php } ?>

方式三：关联查询一次性获取数据 #

通过SQL关联查询直接获取分类与对应案例，减少数据库查询次数：

修改PHP代码：

$this->db->select("ac.id as category_id, ac.title as category_title, ac.description, acs.id as case_id, acs.title as case_title, acs.contents");
$this->db->from('article_category ac');
// left join保证无案例的分类也能正常显示
$this->db->join('article_case acs', 'ac.id = acs.category_id', 'left');
$this->db->order_by('ac.sort ASC, acs.id ASC');
$query = $this->db->get();
$results = $query->result_array();

// 重组为分类包含案例的结构
$category_cases = [];
foreach ($results as $row) {
    $cat_id = $row['category_id'];
    if (!isset($category_cases[$cat_id])) {
        $category_cases[$cat_id] = [
            'id' => $cat_id,
            'title' => $row['category_title'],
            'description' => $row['description'],
            'cases' => []
        ];
    }
    // 有案例数据时才添加
    if (!empty($row['case_id'])) {
        $category_cases[$cat_id]['cases'][] = [
            'id' => $row['case_id'],
            'title' => $row['case_title'],
            'contents' => $row['contents']
        ];
    }
}
$data['category_with_cases'] = $category_cases;

修改HTML代码：

<?php foreach ($category_with_cases as $category) { ?>
<section>
   <h2><?php echo $category['title']; ?></h2>
   <p><?php echo $category['description']; ?></p>
</section>
<?php foreach ($category['cases'] as $case) { ?>
<section>
   <h3><?php echo $case['title']; ?></h3>
   <p><?php echo $case['contents']; ?></p>
</section>
<?php } ?>
<?php } ?>

内容的提问来源于stack exchange，提问作者Toru Kawahata