在Codeigniter查询中将第二个表检索为子数组 [英] Retrieve second table as subarray in codeigniter query

问题描述

我有两个表A和B，并且B与A的关系为多:1.

I have two tables A & B, and B has a many:1 relationship with A.

从A查询行时，我还希望将对应的B记录作为数组返回并从A添加到结果数组中，所以最终得到这样的结果:

When querying rows from A I'd also like to have corresponding B records returned as an array and added to the result array from A, so I end up with something like this:

A-ROW
   field
   field
   B-ITEMS
      item1
      item2
      item3

是否有一种干净的方法来执行一个查询(也许是联接?)，还是我应该对A上的id执行B的第二次查询并将其添加到结果数组中?

Is there a clean way to do this with one query (perhaps a join?), or should I just perform a second query of B on the id from A and add that to the result array?

推荐答案

将表B连接到表A上会更有效.它不会为您提供所需形状的数据.但是您可以遍历此结果并将数据构建为所需的形状.

It would be more efficient to join table B on table A. It will not give you the data in the shape you are looking for. But you can iterate over this result and build the data into the desired shape.

这里有一些代码来说明这个想法:

Here is some code to illustrate the idea :

// Join table B on table A through a foreign key
$sql = 'select a.id, a.x, b.y
    from a
    left join b on b.a_id=a.id
    order by a.id';

// Execute query
$result = $this->db->query($sql)->result_array();

// Initialise desired result
$shaped_result = array();

// Loop through the SQL result creating the data in your desired shape
foreach ($result as $row)
{
    // The primary key of A
    $id = $row['id'];

    // Add a new result row for A if we have not come across this key before
    if (!array_key_exists($id, $shaped_result))
    {
        $shaped_result[$id] = array('id' => $id, 'x' => $row['x'], 'b_items' => array());
    }

    if ($row['y'] != null)
    {
        // Push B item onto sub array
        $shaped_result[$id]['b_items'][] = $row['y'];
    }
}

这篇关于在Codeigniter查询中将第二个表检索为子数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！