We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

PostGres SQL query

Hi OpenClinica Developers

I've been using this rest query:
https://site/OpenClinica/rest/clinicaldata/xml/view/siteID/*/*/*
To get all the subjects in a study. However barring reindexing this is impossible to get to and keeps hitting the timeout limit. We awere wondering if there is a way of getting an active patient/subject list in a study using postgres (a query example?)? Does anyone else have any other ideas? Cheers.

Kevin

Comments

  • kristiakkristiak Posts: 1,338 ✭✭✭
    via Email
    Kevin,

    You can easily get a list of what you want via the reporting function in OC

    Regards

    Krister
  • toskriptoskrip Posts: 279 ✭✭✭
    With RESTfull URLs it is currently not possible to limit the detail (hierarchy) of data that you are retrieving (which is very unfortunate, I hope this will be covered in a future). The is the reason for your timeouts because you have a lots of subjects with a lots of data elements.

    If I were you I would stay away from directly querying database and stick with web services that OC has. You can use SOAP service to get subjects from defined study:

    https://docs.openclinica.com/3.1/technical-documents/openclinica-web-services-guide/studysubject-web-service#content-title-3005

    and than use rest to get details...

    but if you need performance here is an example of SQL:

    sql = "SELECT ss.study_subject_id AS Id,\n" +
    "ss.label AS StudySubjectLabel,\n" +
    "s.date_of_birth AS BirthDate,\n" +
    "s.gender AS Gender,\n" +
    "s.unique_identifier AS PersonId,\n" +
    "sed.name AS EventName,\n" +
    "sed.description AS EventDescription,\n" +
    "se.sample_ordinal AS ScheduledEventRepeatKey,\n" +
    "c.name AS CrfName,\n" +
    "cv.name AS CrfVersionName,\n" +
    "i.item_id AS ItemID,\n" +
    "i.oc_oid AS ItemOID,\n" +
    "i.name AS ItemName,\n" +
    "i.units AS ItemUnits,\n" +
    "i.phi_status AS ItemPhi,\n" +
    "idt.code AS ItemType,\n" +
    "id.ordinal AS RepeatItemRow,\n" +
    "i.description AS ItemDescription,\n" +
    "ifm.show_item AS ItemVisible,\n" +
    "ifm.left_item_text AS ItemLabel,\n" +
    "ifm.right_item_text AS ItemRightText,\n" +
    "ifm.required AS ItemRequired,\n" +
    "id.value AS ItemValue,\n" +
    "decode AS ItemDecode\n" +
    "FROM STUDY_SUBJECT ss\n" + // I want to see items for enrolled StudySubjects
    "INNER JOIN SUBJECT s\n" + // Associate StudySubjects with Subjects (relationship has to exists, each study subject is a subject)
    "ON ss.subject_id = s.subject_id\n" +
    "INNER JOIN STUDY_EVENT se\n" + // Associate StudySubjects with scheduled StudyEvents
    "ON ss.study_subject_id = se.study_subject_id\n" +
    "INNER JOIN STUDY_EVENT_DEFINITION sed\n" + // Associate scheduled StudyEvents with StudyEventDefinitions
    "ON se.study_event_definition_id = sed.study_event_definition_id\n" +
    "INNER JOIN EVENT_CRF ec\n" + // Associate scheduled StudyEvents with EventCRFs
    "ON se.study_event_id = ec.study_event_id\n" +
    "INNER JOIN CRF_VERSION cv\n" + // Associate EventCRFs with CRF verions
    "ON ec.crf_version_id = cv.crf_version_id\n" +
    "INNER JOIN CRF c\n" + // Associate CRF versions with CRF definition
    "ON cv.crf_id = c.crf_id\n" +
    "INNER JOIN EVENT_DEFINITION_CRF edc\n" + // Associate CRF versions with EventDefinitionCrfs
    "ON cv.crf_id = edc.crf_id\n" +
    "AND se.study_event_definition_id = edc.study_event_definition_id\n" +
    "INNER JOIN ITEM_FORM_METADATA ifm\n" +
    "ON cv.crf_version_id = ifm.crf_version_id\n" +
    "INNER JOIN ITEM i\n" +
    "ON ifm.item_id = i.item_id\n" +
    "INNER JOIN ITEM_DATA_TYPE idt\n" +
    "ON i.item_data_type_id = idt.item_data_type_id\n" +
    "LEFT JOIN ITEM_DATA id\n" + // Associate all of this with ItemData when exists (otherwise null)
    "ON ec.event_crf_id = id.event_crf_id\n" +
    "AND i.item_id = id.item_id\n" +
    "LEFT JOIN\n" + // Associate with decoded value for specific ItemTypes -> responseSetType (3, 5, 6, 7)
    "(\n" +
    "\tSELECT\n" + // Make union of ResponseSets (for decoding)
    "\t version_id as crf_version_id\n" +
    "\t, response_set_id as set_id\n" +
    "\t, label\n" +
    "\t, options_values as value\n" +
    "\t, options_text as decode\n" +
    "\tFROM RESPONSE_SET\n" +
    "\tWHERE response_type_id = 3\n" + // check box (select one from many options)
    "\t UNION\n" +
    "\tSELECT\n" +
    "\t version_id as crf_version_id\n" +
    "\t, response_set_id as set_id\n" +
    "\t, label\n" +
    "\t, trim (both from regexp_split_to_table(options_values, E',')) as value\n" +
    "\t, trim (both from regexp_split_to_table(options_text, E',')) as decode\n" +
    "\tFROM RESPONSE_SET\n" +
    "\tWHERE response_type_id = 5\n" + // radio (select one from many options)
    "\t UNION\n" +
    "\tSELECT\n" +
    "\t version_id as crf_version_id\n" +
    "\t, response_set_id as set_id\n" +
    "\t, label\n" +
    "\t, trim (both from regexp_split_to_table(options_values, E',')) as value\n" +
    "\t, trim (both from regexp_split_to_table(options_text, E',')) as decode\n" +
    "\tFROM RESPONSE_SET\n" +
    "\tWHERE response_type_id = 6\n" + // drop down list (select one from many options)
    "\t UNION\n" +
    "\tSELECT\n" +
    "\t version_id as crf_version_id\n" +
    "\t, response_set_id as set_id\n" +
    "\t, label\n" +
    "\t, trim (both from regexp_split_to_table(options_values, E',')) as value\n" +
    "\t, trim (both from regexp_split_to_table(options_text, E',')) as decode\n" +
    "\tFROM RESPONSE_SET\n" +
    "\tWHERE response_type_id = 7\n" + // list box (select one or more from many options)
    "\t) cls\n" +
    "ON ifm.response_set_id = cls.set_id\n" +
    "AND ifm.crf_version_id = cls.crf_version_id\n" +
    "AND id.value = cls.value\n" +
    "\n" +
    "WHERE ss.study_id IN (" + inStudy + ") AND i.oc_oid IN (" + inQuery + ") \n" +
    "ORDER BY\n" +
    "ss.study_subject_id,\n" +
    "s.unique_identifier,\n" +
    "sed.ordinal,\n" +
    "se.sample_ordinal,\n" +
    "edc.ordinal,\n" +
    "id.ordinal,\n" +
    "ifm.ordinal";

    it takes primary keys of study and OIDs of item definitions as parameters.

    Tomas
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Tomas, unfortunately simultaneous regexp split to table is not guaranteed
    to preserve row order, so randomly that query will produce shuffled
    response option pairings. Also straight union (not union all) appends
    distinct rows, which can be slow.

    There is a slightly more complex but accurate version in the sqldatamart
    code.

    Kevin, the info you're after + heaps more is in sqldatamart. If you do the
    basic setup on the same box as OC I think you'll get going faster than
    trying to make sense of SOAP and the bugs in the OC API.
  • toskriptoskrip Posts: 279 ✭✭✭
    Hey, I see... good to know. I have mostly used it for simple queries (never as a full data export). Still did not find time to give your sqldatamart a try, which is unfortunate. I believe that you spent your time to tweek all the OC DB query specialties :)

    T
This discussion has been closed.