Codeigniter 4 Model Relationship Entity

Question

I have database tables that look like this : Table 1 : transaction

id|buyer_id|transaction_date
----------------------------
1 |   1    |2020-01-01
2 |   4    |2020-03-04
3 |   6    |2020-11-12
----------------------------

Table 2 : transaction_detail

id|transaction_id|item_id|qty
--------------------------------
 1|      1       |   1   |  3  |
 2|      1       |   4   |  1  |
 3|      1       |   6   |  2  |
--------------------------------

transaction_detai.transaction_id is a foreign key to transaction.id
How can I select data in transaction table but also get all the transaction_detail as a child ? If I use join, it will generate all data in one row. I need something just like this :

Array(
[0] => Master\Entities\Transaction Object
    (
        [id:protected] =>
        [buyer_id:protected] =>
        [transaction_date:protected] =>
        [transaction_detail:protected]=>
        Array(
        [0] => Master\Entities\TransactionDetail Object
             (
             [id:protected] =>
             [transaction_id:protected] =>
             [item_id:protected] =>
             [qty:protected] =>
              )
         )
   )
)

Answer 1

Your Context not clear whether you need it be done with model or with query builders. Using builders you can create a multidimensional array and place details accordingly and sample code for that is as:

$db = \Config\Database::connect();
 // Fetch all details from main table say `transaction`
$dataMain = $db->table('transaction')
            ->where('transaction.deleted_at',NULL)
            ->select('transaction.id,
                 transaction.buyer_id,transaction.transaction_date')
            ->get()
            ->getResult();
$result = [];
foreach($dataMain as $dataRow){
// For each master table row generate two sub array one to store main table row and other to hold details table data respective to main table row
if(!isset($result[$dataRow->id])){
  $result[$dataRow->id]['transaction'] = [];
  $result[$dataRow->id]['transaction_details'] = [];
  array_push($result[$dataRow->id]['transaction'],$dataRow);
  }
$dataSecondary = $db->table('transaction_detail')
                 ->where('transaction_detail.deleted_at',NULL)
                 ->select('transaction_detail.id, 
                    transaction_detail.transaction_id, 
                    transaction_detail.item_id,transaction_detail.qty')
                 ->where('transaction_detail.offer_id',$dataRow->id)
                 ->get()
                 ->getResult();
array_push($result[$dataRow->id]['transaction_details'],$dataSecondary);
   }
// $result[$id]['transaction'] - contains master table data
 // $result[$id]['transaction_details'] - contains sub table datas associated with respective master table data
print '<pre>';
var_dump($result);
exit;