codeigniter-4 - Codeigniter 4 模型关系实体

Question

我有如下所示的数据库表:表 1:事务

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

表 2:交易明细

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

transaction_detai.transaction_id 是 transaction.id 的外键
我怎样才能在交易表中选择数据，同时将所有 transaction_detail 作为 child 获取？如果我使用 join，它将在一行中生成所有数据。我需要这样的东西:

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

您的上下文不清楚您是否需要使用模型或查询构建器来完成。使用构建器，您可以创建一个多维数组并相应地放置详细信息，示例代码如下:

$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;