FireDAC（Master-Detail 功能）主从表查询

主从表查询方案优点：

1. FIREDAC 原生支持，性能最优

2. 代码简洁，易于维护

3. 自动关联，减少手动处理错误

4. 内存效率高，只加载需要的数据
   
   JSON格式：
   
   

   {
       "data": [
           {
               "company": "DEMO",
               "creator": "DS",
               "usr_group": "",
               "create_date": "20110607161401513",
               "modifier": "DS",
               "modi_date": "20110607163936093",
               "flag": 6,
               "list": [
                   {
                       "company": "DEMO",
                       "creator": "DS",
                       "usr_group": "",
                       "create_date": "20110607161401623",
                       "modifier": "DS",
                       "modi_date": "20110607163936047",
                       "flag": 6,
       
                   }
               ]
           }
       ],
       "page": {
           "pageIndex": 1,
           "pageSize": 10,
           "totalCount": 5,
           "totalPages": 1,
           "hasPrevious": false,
           "hasNext": false
       }
   }

    

   procedure TDBDemoController.GetEmployeesWithDBHelperV9;
   var
     MasterDBHelper: TDBHelper;
     DetailDBHelper: TDBHelper;
     LPageNum, LPageSize, LTotalCount: Integer;
     LJsonResp: TJDOJsonObject;
     lSer: TMVCJsonDataObjectsSerializer;
   begin
     // 分页参数处理
     LPageNum := StrToIntDef(Context.Request.QueryStringParam('pageIndex'), 1);
     LPageSize := StrToIntDef(Context.Request.QueryStringParam('pageSize'), 10);
     LPageNum := Max(1, LPageNum);
     LPageSize := EnsureRange(LPageSize, 1, 1000);     //第页限制
   
     MasterDBHelper := TDBHelper.Create(ConnectionDef);
     DetailDBHelper := TDBHelper.Create(ConnectionDef);
     try
       // 先获取总记录数（使用COUNT查询更高效）
       MasterDBHelper.Query.SQL.Text := 'SELECT COUNT(*) as TotalCount FROM COPTC';
       MasterDBHelper.Query.Open;
       try
         LTotalCount := MasterDBHelper.Query.FieldByName('TotalCount').AsInteger;
       finally
         MasterDBHelper.Query.Close;
       end;
   
       // 如果没有数据，直接返回空结果
       if LTotalCount = 0 then
       begin
        // Render(Format('{"data":[],"totalCount":0,"pageIndex":%d,"pageSize":%d,"hasNext":false}', [LPageNum, LPageSize]));
         raise EMVCException.Create(HTTP_STATUS.NotFound, 'Not Found');
         Exit;
       end;
   
       // 设置主从关系
       DetailDBHelper.Query.MasterSource := TDataSource.Create(nil);
       try
         // 先设置主查询并打开
         MasterDBHelper.Query.SQL.Text := 'SELECT * FROM COPTC ORDER BY TC001, TC002';
         MasterDBHelper.Query.FetchOptions.RecsSkip := (LPageNum - 1) * LPageSize;
         MasterDBHelper.Query.FetchOptions.RecsMax := LPageSize;
         MasterDBHelper.Query.Open;
   
         // 设置主从关系（在主查询打开后）
         DetailDBHelper.Query.MasterSource.DataSet := MasterDBHelper.Query;
         DetailDBHelper.Query.MasterFields := 'TC001;TC002';
         DetailDBHelper.Query.IndexFieldNames := 'TD001;TD002';
   
         // 明细查询 - FIREDAC 会自动处理关联
         DetailDBHelper.Query.SQL.Text := 'SELECT * FROM COPTD WHERE TD001 = :TC001 AND TD002 = :TC002 ORDER BY TD001, TD002';
         DetailDBHelper.Query.Open;
   
         LJsonResp := TJDOJsonObject.Create;
         try
           lSer := TMVCJsonDataObjectsSerializer.Create;
           try
             // 序列化主数据
             lSer.DataSetToJsonArray(MasterDBHelper.Query, LJsonResp.A['data'], TMVCNameCase.ncLowerCase, [], nil);
   
             // 利用 FIREDAC 的主从关系自动关联数据
             MasterDBHelper.Query.First;
             var CurrentIndex := 0;
   
             while not MasterDBHelper.Query.Eof do
             begin
               // 当前主记录对应的明细记录会自动过滤
               lSer.DataSetToJsonArray(DetailDBHelper.Query, LJsonResp.A['data'].O[CurrentIndex].A['list'], TMVCNameCase.ncLowerCase, [], nil);
               MasterDBHelper.Query.Next;
               Inc(CurrentIndex);
             end;
   
             // 分页元数据
             LJsonResp.O['page'].I['pageIndex'] := LPageNum;
             LJsonResp.O['page'].I['pageSize'] := LPageSize;
             LJsonResp.O['page'].I['totalCount'] := LTotalCount;
             LJsonResp.O['page'].I['totalPages'] := Ceil(LTotalCount / LPageSize); // 计算总页数
             LJsonResp.O['page'].B['hasPrevious'] := (LPageNum > 1);                         //上一页
             LJsonResp.O['page'].B['hasNext'] := (LPageNum * LPageSize) < LTotalCount;       //下一页
             Render(LJsonResp, False);
           finally
             lSer.Free;
           end;
         finally
           // LJsonResp 由 Render 自动释放
           FreeAndNil(LJsonResp);
         end;
   
       finally
         DetailDBHelper.Query.MasterSource.Free;
       end;
   
     finally
       DetailDBHelper.Free;
       MasterDBHelper.Free;
     end;
   end;