Retrieve second table as subarray in codeigniter query

Question

I have two tables A & B, and B has a many:1 relationship with 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

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?

Answer 1

"... just perform a second query of B on the id from A and add that to the result array ..." -- that is the correct solution. SQL won't comprehend nested array structure.

Answer 2

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'];
    }
}