如何在ReportPortal v5 PostgreSQL中查询test_item对应的project_id与project_name

修正后的查询语句 #

select
    tir.result_id, 
    ti.start_time, 
    tir.end_time, 
    tir.duration, 
    ti.item_id as test_item_id,
    ti.project_id,
    cq.name as project_name,
    cq.issue_name,
    STRING_AGG(l.log_message, '\t') as log_message 
from test_item_results tir
left join test_item ti
    on tir.result_id = ti.item_id 
left join issue i
    on tir.result_id = i.issue_id    
join (select 
        p.id as project_id,
        p."name", 
        p.project_type, 
        itp.issue_type_id, 
        it.issue_group_id, 
        it.issue_name 
      from project p 
        join issue_type_project itp
            on p.id = itp.project_id 
        join issue_type it 
            on itp.issue_type_id = it.id 
        where p.project_type = 'INTERNAL') cq
    on i.issue_type = cq.issue_type_id 
    and ti.project_id = cq.project_id
left join log l
    on tir.result_id = l.item_id
where tir.status = 'FAILED'
and ti.type IN ('STEP')
and cq.issue_name <> 'To Investigate'
and cq.issue_name in ('Product Bug', 'Automation Bug', 'System Issue', 'No Defect')
and l.log_message is not NULL
group by 
    tir.result_id, 
    ti.start_time, 
    tir.end_time, 
    ti.item_id, 
    ti.project_id,
    cq.name,
    i.issue_id, 
    cq.issue_name
order by ti.start_time desc

核心修改说明 #

你遇到的重复问题根源是原查询中cq子查询返回了所有INTERNAL项目的问题类型映射，关联时仅匹配了问题类型ID，未关联测试项所属的项目ID，导致同一条测试项只要匹配到问题类型，就会和所有包含该类型的INTERNAL项目产生笛卡尔积，出现重复数据。

1. 不需要使用你提到的pattern_template系列表，test_item表本身自带project_id字段可以直接关联所属项目，绕模板表的路径反而更复杂。
2. 新增ti.project_id = cq.project_id关联条件，直接限定每个测试项只会匹配自身所属项目的问题类型配置，从根源避免重复，不需要使用distinct即可拿到测试项真实的所属项目信息。
3. 额外在返回结果中增加了project_id和project_name，满足你需要把项目信息和测试项放在同一行的需求。

内容的提问来源于stack exchange，提问作者Eric