Ideas for a REDCapDataQuery class to convert queries to SQL

ideas.sql

-- main
set @PID = 19184;
set @EID = 115110;

set @F1 = 'rsp_prt_portal_email';
set @F2 = 'rsp_prt_portal_phone';
set @F3 = 'rsp_prt_portal_url';
set @F4 = 'rsp_prt_start_date';
set @F5 = 'rsp_prt_disable_email';
set @F6 = 'rsp_prt_disable_sms';

select
    r1.record,
    r1.event_id,
    r1.value as 'email',
    r2.value as 'phone',
    r3.value as 'url',
    r4.value as 'start_date'
from
    redcap_data r1
    join redcap_data r2 on r1.project_id = r2.project_id and r1.event_id = r2.event_id and r1.record = r2.record and r1.instance <=> r2.instance and r2.field_name = @F2
    join redcap_data r3 on r1.project_id = r3.project_id and r1.event_id = r3.event_id and r1.record = r3.record and r1.instance <=> r3.instance and r3.field_name = @F3
    join redcap_data r4 on r1.project_id = r4.project_id and r1.event_id = r4.event_id and r1.record = r4.record and r1.instance <=> r4.instance and r4.field_name = @F4
--    join redcap_data r5 on r1.project_id = r5.project_id and r1.event_id = r5.event_id and r1.record = r5.record and r1.instance <=> r5.instance and r5.field_name = @F5
where
    r1.project_id = @PID
and r1.event_id = @EID
and r1.field_name = @F1
limit 10
;


select
    distinct(r0.record),
    r0.event_id,
    r1.value as 'email',
    r2.value as 'phone',
    r3.value as 'url',
    r4.value as 'start_date',
    r5.value as 'disable_email',
    r6.value as 'disable_sms'
from
    redcap_data r0
    left join redcap_data r1 on r0.project_id = r1.project_id and r0.event_id = r1.event_id and r0.record = r1.record and r0.instance <=> r1.instance and r1.field_name = @F1
    left join redcap_data r2 on r0.project_id = r2.project_id and r0.event_id = r2.event_id and r0.record = r2.record and r0.instance <=> r2.instance and r2.field_name = @F2
    left join redcap_data r3 on r0.project_id = r3.project_id and r0.event_id = r3.event_id and r0.record = r3.record and r0.instance <=> r3.instance and r3.field_name = @F3
    left join redcap_data r4 on r0.project_id = r4.project_id and r0.event_id = r4.event_id and r0.record = r4.record and r0.instance <=> r4.instance and r4.field_name = @F4
    left join redcap_data r5 on r0.project_id = r5.project_id and r0.event_id = r5.event_id and r0.record = r5.record and r0.instance <=> r5.instance and r5.field_name = @F5
    left join redcap_data r6 on r0.project_id = r6.project_id and r0.event_id = r6.event_id and r0.record = r6.record and r0.instance <=> r6.instance and r6.field_name = @F6
where
    r0.project_id = @PID
and r0.event_id = @EID

-- and (r5.value is null or r5.value = '')

-- cross-column filters
and (
    (coalesce(r5.value,'') != '1') and (coalesce(r1.value,'') != '')
    or
    (coalesce(r6.value,'') != '1') and (coalesce(r2.value,'') != '')
)




--  and r1.value != '')


-- limit 10
;

/*
 -- within a single event:
addColumn($field, $filter, $required = false);
  $filter - ex: "<> ''" -> and (coalesce(rx.value,'') . $filter )
  $filter - ex: " ( [field_name] <> '' AND [fieldname] != '5' ) " => " ( coalesce(r0.value,'') <> '' AND coalesce(r0.value,'') != '5' ) "
  if ($required == true) => do a join instead of a left join...

addColumn($field, $filter, $required = false);
addColumn($field, $filter, $required = false);
 
addWhere( [field_name1] <> '' AND OR ... )

getSql ()  debug sql generated
query()    get results

 'field_name', "<> ''",    join_filter,  join_type='left | 'inner',
 

 */