gistfile1.sql

SELECT "events"."id" AS t0_r0, "events"."imported_from_id" AS t0_r1, "events"."state_case_status_id" AS t0_r2, "events"."event_name" AS t0_r3, "events"."event_onset_date" AS t0_r4, "events"."created_at" AS t0_r5, "events"."updated_at" AS t0_r6, "events"."outbreak_associated_id" AS t0_r7, "events"."outbreak_name" AS t0_r8, "events"."investigation_LHD_status_id" AS t0_r9, "events"."investigation_started_date" AS t0_r10, "events"."investigation_completed_LHD_date" AS t0_r11, "events"."review_completed_by_state_date" AS t0_r12, "events"."first_reported_PH_date" AS t0_r13, "events"."results_reported_to_clinician_date" AS t0_r14, "events"."record_number" AS t0_r15, "events"."MMWR_week" AS t0_r16, "events"."MMWR_year" AS t0_r17, "events"."lhd_case_status_id" AS t0_r18, "events"."type" AS t0_r19, "events"."event_queue_id" AS t0_r20, "events"."sent_to_cdc" AS t0_r21, "events"."age_at_onset" AS t0_r22, "events"."age_type_id" AS t0_r23, "events"."investigator_id" AS t0_r24, "events"."sent_to_ibis" AS t0_r25, "events"."acuity" AS t0_r26, "events"."other_data_1" AS t0_r27, "events"."other_data_2" AS t0_r28, "events"."deleted_at" AS t0_r29, "events"."parent_id" AS t0_r30, "events"."cdc_updated_at" AS t0_r31, "events"."ibis_updated_at" AS t0_r32, "events"."parent_guardian" AS t0_r33, "events"."workflow_state" AS t0_r34, "events"."participations_contact_id" AS t0_r35, "events"."participations_place_id" AS t0_r36, "events"."participations_encounter_id" AS t0_r37, "events"."undergone_form_assignment" AS t0_r38, "participations"."id" AS t1_r0, "participations"."primary_entity_id" AS t1_r1, "participations"."secondary_entity_id" AS t1_r2, "participations"."participation_status_id" AS t1_r3, "participations"."comment" AS t1_r4, "participations"."created_at" AS t1_r5, "participations"."updated_at" AS t1_r6, "participations"."event_id" AS t1_r7, "participations"."type" AS t1_r8, "entities"."id" AS t2_r0, "entities"."record_number" AS t2_r1, "entities"."entity_url_number" AS t2_r2, "entities"."created_at" AS t2_r3, "entities"."updated_at" AS t2_r4, "entities"."entity_type" AS t2_r5, "people"."id" AS t3_r0, "people"."entity_id" AS t3_r1, "people"."race_id" AS t3_r2, "people"."birth_gender_id" AS t3_r3, "people"."current_gender_id" AS t3_r4, "people"."ethnicity_id" AS t3_r5, "people"."primary_language_id" AS t3_r6, "people"."first_name" AS t3_r7, "people"."middle_name" AS t3_r8, "people"."last_name" AS t3_r9, "people"."birth_date" AS t3_r10, "people"."date_of_death" AS t3_r11, "people"."food_handler_id" AS t3_r12, "people"."healthcare_worker_id" AS t3_r13, "people"."group_living_id" AS t3_r14, "people"."day_care_association_id" AS t3_r15, "people"."age_type_id" AS t3_r16, "people"."risk_factors" AS t3_r17, "people"."risk_factors_notes" AS t3_r18, "people"."approximate_age_no_birthday" AS t3_r19, "people"."first_name_soundex" AS t3_r20, "people"."last_name_soundex" AS t3_r21, "people"."person_type" AS t3_r22, "people"."created_at" AS t3_r23, "people"."updated_at" AS t3_r24, "people"."vector" AS t3_r25, "disease_events"."id" AS t4_r0, "disease_events"."event_id" AS t4_r1, "disease_events"."disease_id" AS t4_r2, "disease_events"."hospitalized_id" AS t4_r3, "disease_events"."died_id" AS t4_r4, "disease_events"."disease_onset_date" AS t4_r5, "disease_events"."date_diagnosed" AS t4_r6, "disease_events"."created_at" AS t4_r7, "disease_events"."updated_at" AS t4_r8 FROM "events" LEFT OUTER JOIN "participations" ON participations.event_id = events.id AND ("participations"."type" = 'InterestedParty' ) LEFT OUTER JOIN "entities" ON "entities".id = "participations".primary_entity_id AND ("entities"."entity_type" = 'PersonEntity' ) LEFT OUTER JOIN "people" ON people.entity_id = entities.id LEFT OUTER JOIN "disease_events" ON disease_events.event_id = events.id WHERE (people.vector @@ to_tsquery('joans | j520')) AND ( ("events"."type" = 'HumanEvent' OR "events"."type" = 'MorbidityEvent' OR "events"."type" = 'ContactEvent' ) ) ORDER BY ts_rank(people.vector, to_tsquery('joans | j520')) DESC, people.last_name, people.first_name, entities.id, events.event_onset_date ASC;