Sequel query

sequel.rb

bq=db[:studies___s].select{[:s__id, :s__name, `"boolean" as type`, :bd__key___key, :b__value]}.left_join(:booleans___b, { :b__study_id => :s__id }).left_join(:boolean_definitions___bd, { :b__boolean_definition_id => :bd__id}).where(:bd__key => 'is_nihr').or(:bd__key => 'is_commercial')

tfq=db[:studies___s].select{[:s__id, :s__name, `"text" as type`, :tfd__key___key, :tf__value]}.left_join(:text_fields___tf, { :tf__study_id => :tf__id }).left_join(:text_field_definitions___tfd, { :tf__text_field_definition_id => :tfd__id}).where(:tfd__key => 'eudract_number').or(:tfd__key => 'portfolio_number')

eq=db[:studies___s].select{[:s__id, :s__name, `"enum" as type`, :e__key___key, :es__name___value]}.left_join(:enumeration_values___ev, { :ev__study_id => :s__id }).left_join(:enumerations___e, { :ev__enumeration_id => :e__id}).join(:enumeration_states___es, { :es__enumeration_id => :e__id }).where(:e__key => 'iras').or(:e__key => 'phase')

dq=db[:studies___s].select{[:s__id, :s__name, `"date" as type`, :sdd__key___key, :sd__date]}.left_join(:significant_dates___sd, { :sd__study_id => :s__id }).left_join(:significant_date_definitions___sdd, { :sd__significant_date_definition_id => :sdd__id}).where(:sdd__key => 'kpi_rd_approval').or(:sdd__key => 'kpi_first_patient_first_visit')

bq.union(tfq, all: true).union(eq, all: true).union(dq, all: true)

bm = Benchmark.measure do
  bq.union(tfq, all: true).union(eq, all: true).union(dq, all: true).all
end

# 0.330000   0.390000   0.720000 (  2.205034)

# ruby 2.3