gistfile1.sql

SELECT *
FROM (
  SELECT
    entities.id AS entity_id,
    people.first_name AS first_name,
    people.last_name AS last_name,
    people.birth_date AS birth_date,
    people.vector AS vector,
    external_codes.code_description AS birth_gender
  FROM entities
  INNER JOIN people ON people.entity_id = entities.id AND (entities.entity_type = 'PersonEntity')
  LEFT OUTER JOIN external_codes ON people.birth_gender_id = external_codes.id AND (external_codes.code_name = 'gender')
) as people

LEFT OUTER JOIN (
  SELECT
    events.id AS id,
    events.type AS type,
    events.event_onset_date AS event_onset_date,
    events."type" AS event_type,
    events.deleted_at AS deleted_at,
    participations.primary_entity_ID as entity_id,
    diseases.disease_name AS disease_name,
    jurisdiction_entities.id AS jurisdiction_entity_id,
    jurisdiction_places.short_name AS jurisdiction_short_name,
    sec_juris.secondary_jurisdiction_entity_ids AS secondary_jurisdictions
  FROM events
  INNER JOIN participations ON participations.event_id = events.id AND (participations."type" = 'InterestedParty' )
  LEFT OUTER JOIN disease_events ON disease_events.event_id = events.id
  LEFT OUTER JOIN diseases ON disease_events.disease_id = diseases.id
  INNER JOIN participations AS jurisdictions ON jurisdictions.event_id = events.id AND (jurisdictions.type = 'Jurisdiction')
  INNER JOIN entities AS jurisdiction_entities ON jurisdiction_entities.id = jurisdictions.secondary_entity_id AND (jurisdiction_entities.entity_type = 'PlaceEntity')
  INNER JOIN places AS jurisdiction_places ON jurisdiction_places.entity_id = jurisdiction_entities.id
  LEFT JOIN (
    SELECT
      events.id AS event_id,
      ARRAY_ACCUM(p.secondary_entity_id) AS secondary_jurisdiction_entity_ids
    FROM
      events
    LEFT JOIN participations p ON (p.event_id = events.id AND p.type = 'AssociatedJurisdiction')
    GROUP BY events.id
  ) sec_juris ON (sec_juris.event_id = events.id)
) as events
ON people.entity_id = events.entity_id

WHERE (people.vector @@ to_tsquery('lacey | peter | l200 | p360') 
AND ( people.birth_date = '09/14/1964' OR people.birth_date IS NULL))
AND ( (events."type" = 'MorbidityEvent' OR events."type" = 'ContactEvent') )
ORDER BY people.birth_date, ts_rank(people.vector, to_tsquery('lacey | peter | l200 | p360')) DESC, events.id DESC
LIMIT 50 OFFSET 0