CodeIgniter中PostgreSQL关联表按日期分组生成JSON数组问题

表结构说明 #

• tagger表：id(主键), userid, 其他字段
• user表：userid(主键), 其他字段
• tagger assets表：id(主键), userid, date_code, assetid, task1, task2（注：原描述中task1重复，此处修正为task2）

需求目标 #

获取标注员数组，每个标注员的assigned_titles字段为按date_code分组的对象数组：每个对象以date_code为键，对应值为该日期下的任务数组，目标结构示例：

Array
(
    [id] => 1
    [userid] => 519560
    [timezone] => -7
    [fname] => Gregory
    [status] => 99
    [assigned_titles] => Array (
        {"202307" : [ [1, 1, 1],[777, 1, 0]]},
        {"202306" : [[2, 1, 1], [543, 0, 1]]}
      )
)

同时需支持按过去12个月顺序循环访问数据：

foreach($output_array as $o) {
  // 访问外层数据
  for($i = 0; $i <=12; $i++  ) {
    $targetDate = date("Ym",strtotime("-$i month"));
    foreach($o['assigned_titles'][$targetDate] as $title) {
      // 处理任务数据
    }
  }
}

当前问题 #

现有查询生成的assigned_titles未按日期分组，同一日期的任务被拆分为多个独立对象：

[assigned_titles] => [{"202307" : [1, 1, 1]}, {"202306" : [2, 1, 1]}, {"202307" : [777, 1, 1]}]

现有CodeIgniter查询代码 #

$query = 'json_agg( 
  json_build_object(
    frl_tagger_assets.date_code, json_build_array(
      frl_tagger_assets.title_id,frl_tagger_assets.breakdown,frl_tagger_assets.breakdown
      )
    ) 
  )  as assigned_titles ';


$this->db
  ->select('frl_taggers.*, frl_users.userid, frl_users.fname, frl_users.lname, frl_users.status, '.$query)
  ->from('frl_taggers')
  ->join('frl_tagger_assets', 'frl_taggers.userid = frl_tagger_assets.userid', 'left')
  ->join('frl_users', 'frl_taggers.userid = frl_users.userid', 'left')
  ->where('frl_users.status >= 80',NULL, FALSE )
  ->where('date_code >='.date("Ym",strtotime("-1 month")), NULL, FALSE)
  ->group_by('frl_taggers.id')
  ->group_by('frl_users.userid');
$query = $this->db->get();
$res = $query->result_array();

核心思路是先按userid和date_code分组，聚合该日期下的所有任务数组；再外层按标注员分组，聚合按日期键组织的对象。修改后的查询代码如下：

// 定义子查询：按userid和date_code聚合任务数组
$subQuery = '(
  SELECT 
    userid,
    json_build_object(
      date_code, 
      json_agg(json_build_array(title_id, breakdown, breakdown))
    ) as date_tasks
  FROM frl_tagger_assets
  WHERE date_code >= '.date("Ym",strtotime("-1 month")).'
  GROUP BY userid, date_code
) as asset_groups';

// 主查询：聚合每个标注员的date_tasks为assigned_titles
$this->db
  ->select('frl_taggers.*, frl_users.userid, frl_users.fname, frl_users.lname, frl_users.status, json_agg(asset_groups.date_tasks) as assigned_titles')
  ->from('frl_taggers')
  ->join('frl_users', 'frl_taggers.userid = frl_users.userid', 'left')
  ->join($subQuery, 'frl_taggers.userid = asset_groups.userid', 'left')
  ->where('frl_users.status >= 80', NULL, FALSE)
  ->group_by('frl_taggers.id, frl_users.userid');

$query = $this->db->get();
$res = $query->result_array();

关键修改说明 #

1. 子查询asset_groups：

   • 按userid和date_code分组，用json_agg(json_build_array(...))把该日期下的所有任务合并成一个二维数组
   • 用json_build_object(date_code, ...)将日期作为键，任务数组作为值，生成单日期的任务对象

2. 主查询聚合：

   • 关联子查询结果，用json_agg(asset_groups.date_tasks)把每个标注员的所有日期任务对象聚合为一个数组，即assigned_titles
   • 生成的结构会自动按日期分组，同一日期的任务会被合并到同一个键下

验证输出 #

修改后assigned_titles会符合目标结构：

[assigned_titles] => [{"202307" : [[1,1,1],[777,1,0]]}, {"202306" : [[2,1,1],[543,0,1]]}]

完全支持需求中的循环访问逻辑。

内容的提问来源于stack exchange，提问作者g-ulrich