Class: Queries::Otu::Filter

Inherits:
Query::Filter show all
Includes:
Concerns::Citations, Concerns::Confidences, Concerns::Conveyances, Concerns::DataAttributes, Concerns::Depictions, Concerns::Geo, Concerns::Notes, Concerns::Sounds, Concerns::Tags, Helpers
Defined in:
lib/queries/otu/filter.rb

Constant Summary collapse

PARAMS = 
[
  :ancestrify,
  :asserted_distributions,
  :biological_association_id,
  :biological_associations,
  :biological_relationship_id,
  :exclude_taxon_name_relationship,
  :collecting_event_id,
  :collection_objects,
  :common_names,
  :contents,
  :coordinatify,
  :descendants,
  :descriptor_id,
  :geo_shape_id,
  :geo_mode,
  :geo_shape_type,
  :genus_name,
  :geo_json,
  :name,
  :name_exact,
  :observations,
  :otu_id,
  :radius,
  :taxon_name,
  :taxon_name_id,
  :dwc_occurrences,
  :with_name,
  :wkt,

  biological_relationship_id: [],
  collecting_event_id: [],
  descriptor_id: [],
  geo_shape_id: [],
  geo_shape_type: [],
  name: [],
  otu_id: [],
  taxon_name_id: [],
].freeze

Constants inherited from Query::Filter

Query::Filter::FILTER_QUERIES, Query::Filter::SUBQUERIES

Instance Attribute Summary collapse

Attributes inherited from Query::Filter

#anatomical_part_query, #api, #asserted_distribution_query, #asserted_environment_query, #biological_association_query, #biological_associations_graph_query, #collecting_event_query, #collection_object_query, #content_query, #controlled_vocabulary_term_query, #conveyance_query, #data_attribute_query, #depiction_query, #descriptor_query, #document_query, #dwc_occurrence_query, #extract_query, #field_occurrence_query, #image_query, #loan_query, #object_global_id, #observation_query, #order_by, #otu_query, #page, #paginate, #params, #per, #person_query, #project_id, #recent, #recent_target, #roll_call, #sound_query, #taxon_name_query, #taxon_name_relationship_query, #venn, #venn_ignore_pagination, #venn_mode

Attributes inherited from Query

#query_string, #terms

Instance Method Summary collapse

Methods included from Helpers

#boolean_param, #integer_param, #negate_facet, #split_pairs, #split_repeated_pairs, #tri_value_array

Methods inherited from Query::Filter

#all_and_clauses, #all_merge_clauses, #annotator_and_clauses, #annotator_merge_clauses, annotator_params, api_except_params, api_excluded_params, #apply_venn, #attribute_exact_facet, base_filter, base_query_name, base_query_to_h, #deep_permit, #disable_paging, included_annotator_facets, instantiated_base_filter, inverted_subqueries, #model_id_facet, #object_global_id_facet, #only_project?, #only_project_or_less?, #paging_state, params, #permitted_params, #process_url_into_params, #project_id_facet, query_name, #set_nested_queries, #set_paging, set_paging, #shared_and_clauses, #subquery_vector, #target_and_clauses, #venn_query

Methods inherited from Query

#alphabetic_strings, #alphanumeric_strings, base_name, #base_name, #base_query, #build_terms, #cached_facet, #end_wildcard, #levenshtein_distance, #match_ordered_wildcard_pieces_in_cached, #no_terms?, referenced_klass, #referenced_klass, #referenced_klass_except, #referenced_klass_intersection, #referenced_klass_union, #start_and_end_wildcard, #start_wildcard, #table, #wildcard_pieces

Constructor Details

#initialize(query_params) ⇒ Filter

Returns a new instance of Filter.



207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/queries/otu/filter.rb', line 207

def initialize(query_params)
  super

  @ancestrify = boolean_param(params, :ancestrify)
  @asserted_distributions = boolean_param(params, :asserted_distributions)
  @biological_association_id = params[:biological_association_id]
  @biological_associations = boolean_param(params, :biological_associations)
  @biological_relationship_id = params[:biological_relationship_id]
  @exclude_taxon_name_relationship = boolean_param(params, :exclude_taxon_name_relationship)
  @collecting_event_id = params[:collecting_event_id]
  @collection_objects = boolean_param(params, :collection_objects)
  @common_names = boolean_param(params, :common_names)
  @contents = boolean_param(params, :contents)
  @coordinatify = boolean_param(params, :coordinatify)
  @descendants = boolean_param(params, :descendants)
  @dwc_occurrences = boolean_param(params, :dwc_occurrences)
  @descriptor_id = params[:descriptor_id]
  @genus_name = params[:genus_name]
  @geo_json = params[:geo_json]
  @historical_determinations = boolean_param(params, :historical_determinations)
  @name = params[:name]
  @name_exact = boolean_param(params, :name_exact)
  @observations = boolean_param(params, :observations)
  @otu_id = params[:otu_id]
  @radius = params[:radius].presence || 100.0
  @taxon_name = boolean_param(params, :taxon_name)
  @taxon_name_id = params[:taxon_name_id]
  @with_name = boolean_param(params, :with_name)
  @wkt = params[:wkt]

  set_confidences_params(params)
  set_conveyance_params(params)
  set_notes_params(params)
  set_citations_params(params)
  set_depiction_params(params)
  set_data_attributes_params(params)
  set_tags_params(params)
  set_geo_params(params)
end

Instance Attribute Details

#ancestrify ⇒ Object

!! This param is not like the others. !! See parallel in TaxonName filter 'ancestrify'.

Returns:

  • Boolean if true then, additionally, all coordinate otus for all inferred ancestors are included the param adds to the result by going "up" (to root) then "out"



61
62
63
 
# File 'lib/queries/otu/filter.rb', line 61

def ancestrify
  @ancestrify
end

#asserted_distributions ⇒ True, ...

Returns true - Otu has AssertedDistribution false - Otu without AssertedDistribution nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has AssertedDistribution false - Otu without AssertedDistribution nil - not applied



165
166
167
 
# File 'lib/queries/otu/filter.rb', line 165

def asserted_distributions
  @asserted_distributions
end

#biological_association_id ⇒ Object

Finds all OTUs that are the current determination of a CollectionObject that is in the BiologicalAssociation or are part of the Biological association itself. Altered by historical_determinations.

Parameters:

  • biological_association_id (Integer, Array)

Returns:

  • Array one or more biological_association_id



124
125
126
 
# File 'lib/queries/otu/filter.rb', line 124

def biological_association_id
  @biological_association_id
end

#biological_associations ⇒ True, ...

Returns true - Otu has BiologicalAssocation (subject or object) false - Otu without BiologicalAssociation nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has BiologicalAssocation (subject or object) false - Otu without BiologicalAssociation nil - not applied



189
190
191
 
# File 'lib/queries/otu/filter.rb', line 189

def biological_associations
  @biological_associations
end

#biological_relationship_id ⇒ Object

Finds all OTUs that are related to a BiologicalAssociation with the given BiologicalRelationship. See also exclude_taxon_name_relationship.

Parameters:

  • biological_relationship_id (Integer, Array)

Returns:

  • Array one or more biological_relationship_id



132
133
134
 
# File 'lib/queries/otu/filter.rb', line 132

def biological_relationship_id
  @biological_relationship_id
end

#collecting_event_id ⇒ Object

Finds all OTUs that are the current determination of a CollectionObject that was collecting in these Collecting events. Altered by historical_determinations.

Parameters:

  • collecting_event_id (Integer, Array)

Returns:

  • Array one or more collecting_event_id



106
107
108
 
# File 'lib/queries/otu/filter.rb', line 106

def collecting_event_id
  @collecting_event_id
end

#collection_objects ⇒ True, ...

Returns true - Otu has CollectionObjects false - Otu without CollectionObjects nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has CollectionObjects false - Otu without CollectionObjects nil - not applied



183
184
185
 
# File 'lib/queries/otu/filter.rb', line 183

def collection_objects
  @collection_objects
end

#common_names ⇒ True, ...

Returns true - Otu has common names false - Otu without common names nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has common names false - Otu without common names nil - not applied



171
172
173
 
# File 'lib/queries/otu/filter.rb', line 171

def common_names
  @common_names
end

#contents ⇒ True, ...

Returns true - Otu has Content false - Otu without Content nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has Content false - Otu without Content nil - not applied



177
178
179
 
# File 'lib/queries/otu/filter.rb', line 177

def contents
  @contents
end

#coordinatify ⇒ Object

See coordinatify_result for more.

!! This param is not like the others. !! See parallel in TaxonName filter 'validify'.

Returns:

  • Boolean if true then, additionally, all coordinate otus for the result are included



69
70
71
 
# File 'lib/queries/otu/filter.rb', line 69

def coordinatify
  @coordinatify
end

#descendants ⇒ Object

Returns Boolean.

Returns:

  • Boolean



97
98
99
 
# File 'lib/queries/otu/filter.rb', line 97

def descendants
  @descendants
end

#descriptor_id ⇒ Array

Parameters:

  • descriptor_id (String, Array)

Returns:

  • (Array) 


159
160
161
 
# File 'lib/queries/otu/filter.rb', line 159

def descriptor_id
  @descriptor_id
end

#dwc_occurrences ⇒ True, ...

Returns true - Otu is linked to one or more DwcOccurrences (via CollectionObject, FieldOccurrence, or AssertedDistribution) false - Otu has no DwcOccurrences nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu is linked to one or more DwcOccurrences (via CollectionObject, FieldOccurrence, or AssertedDistribution) false - Otu has no DwcOccurrences nil - not applied



195
196
197
 
# File 'lib/queries/otu/filter.rb', line 195

def dwc_occurrences
  @dwc_occurrences
end

#exclude_taxon_name_relationship ⇒ Object

When true, finds OTUs that are NOT related to a BiologicalAssociation with any of the given biological_relationship_id values.

Parameters:

  • exclude_taxon_name_relationship (Boolean) 


137
138
139
 
# File 'lib/queries/otu/filter.rb', line 137

def exclude_taxon_name_relationship
  @exclude_taxon_name_relationship
end

#genus_name ⇒ String?

Returns exact match against the cached name of the Otu's genus-rank TaxonName.

Returns:

  • (String, nil) —

    exact match against the cached name of the Otu's genus-rank TaxonName



155
156
157
 
# File 'lib/queries/otu/filter.rb', line 155

def genus_name
  @genus_name
end

#geo_json ⇒ Hash?

Returns in geo_json format (no Feature ...) ?!.

Returns:

  • (Hash, nil) —

    in geo_json format (no Feature ...) ?!



145
146
147
 
# File 'lib/queries/otu/filter.rb', line 145

def geo_json
  @geo_json
end

#historical_determinations ⇒ Boolean?

Where applicable:

true - include only historical determinations (ignores only determinations)
false - include both current and historical
nil - include only current determinations

Impacts all TaxonDetermination referencing queries

Parameters:

  • historical_determinations (Boolean)

Returns:

  • (Boolean, nil) 


115
116
117
 
# File 'lib/queries/otu/filter.rb', line 115

def historical_determinations
  @historical_determinations
end

#name ⇒ Object

Returns Array literal match against one or more Otu#name.

Parameters:

  • name (String, Array)

Returns:

  • Array literal match against one or more Otu#name



74
75
76
 
# File 'lib/queries/otu/filter.rb', line 74

def name
  @name
end

#name_exact ⇒ Object

Returns Boolean if true then match on name exactly.

Returns:

  • Boolean if true then match on name exactly



78
79
80
 
# File 'lib/queries/otu/filter.rb', line 78

def name_exact
  @name_exact
end

#observations ⇒ True, ...

Returns true - Otu has observations false - Otu without observations nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has observations false - Otu without observations nil - not applied



201
202
203
 
# File 'lib/queries/otu/filter.rb', line 201

def observations
  @observations
end

#otu_id ⇒ Object

Returns Array one or more Otu ids.

Parameters:

  • otu_id (Integer, Array)

Returns:

  • Array one or more Otu ids



89
90
91
 
# File 'lib/queries/otu/filter.rb', line 89

def otu_id
  @otu_id
end

#radius ⇒ Object

Integer in Meters !! defaults to 100m



205
206
207
 
# File 'lib/queries/otu/filter.rb', line 205

def radius
  @radius
end

#taxon_name ⇒ True, ...

Returns true - Otu has taxon name false - Otu without taxon name nil - not applied.

Returns:

  • (True, False, nil) —

    true - Otu has taxon name false - Otu without taxon name nil - not applied



151
152
153
 
# File 'lib/queries/otu/filter.rb', line 151

def taxon_name
  @taxon_name
end

#taxon_name_id ⇒ Object

Returns Array one or more Otu taxon_name_id.

Parameters:

  • taxon_name_id (Integer, Array)

Returns:

  • Array one or more Otu taxon_name_id



94
95
96
 
# File 'lib/queries/otu/filter.rb', line 94

def taxon_name_id
  @taxon_name_id
end

#with_name ⇒ Object

Returns Boolean true - has name false - has no name nil - both.

Returns:

  • Boolean true - has name false - has no name nil - both



84
85
86
 
# File 'lib/queries/otu/filter.rb', line 84

def with_name
  @with_name
end

#wkt ⇒ Object

Parameters:

  • wkt (String) —

    A Spatial area in WKT format



141
142
143
 
# File 'lib/queries/otu/filter.rb', line 141

def wkt
  @wkt
end

Instance Method Details

#all(nil_empty = false) ⇒ ActiveRecord::Relation

Returns:

  • (ActiveRecord::Relation) 


847
848
849
850
851
852
853
854
855
 
# File 'lib/queries/otu/filter.rb', line 847

def all(nil_empty = false)
  q = super

  # Order matters, coordinatify should be last as its "out"
  q = ancestrify_result(q) if ancestrify
  q = coordinatify_result(q) if coordinatify

  q
end

#anatomical_part_query_facet ⇒ Object 



709
710
711
712
713
714
715
716
717
 
# File 'lib/queries/otu/filter.rb', line 709

def anatomical_part_query_facet
  return nil if anatomical_part_query.nil?

  ::Otu
    .joins(:origin_relationships)
    .where(origin_relationships: { new_object_type: 'AnatomicalPart' })
    .where("origin_relationships.new_object_id IN (#{ anatomical_part_query.all.select(:id).to_sql })")
    .distinct
end

#ancestrify_result(q) ⇒ Object 



787
788
789
790
791
792
793
794
795
796
797
798
799
 
# File 'lib/queries/otu/filter.rb', line 787

def ancestrify_result(q)
  a = q

  s = 'WITH otu_base AS (' + a.all.to_sql + ') ' +
    ::Otu
    .joins('JOIN taxon_name_hierarchies tnh on tnh.ancestor_id = otus.taxon_name_id')
    .joins('JOIN otu_base as otu_base1 on otu_base1.taxon_name_id = tnh.descendant_id')
    .to_sql

  b = ::Otu.from('(' + s + ') as otus')

  referenced_klass_union([a, b])
end

#and_clauses ⇒ Object 



801
802
803
804
805
806
 
# File 'lib/queries/otu/filter.rb', line 801

def and_clauses
  [
    name_facet,
    taxon_name_facet,
  ]
end

#asserted_distribution_query_facet ⇒ Object 



574
575
576
577
578
579
 
# File 'lib/queries/otu/filter.rb', line 574

def asserted_distribution_query_facet
  return nil if asserted_distribution_query.nil?
  ::Otu
    .with(ad: asserted_distribution_query.all)
    .joins("JOIN ad ON ad.asserted_distribution_object_id = otus.id AND ad.asserted_distribution_object_type = 'Otu'").distinct
end

#asserted_distributions_facet ⇒ Object 



356
357
358
359
360
361
362
363
 
# File 'lib/queries/otu/filter.rb', line 356

def asserted_distributions_facet
  return nil if asserted_distributions.nil?
  if asserted_distributions
    ::Otu.joins(:asserted_distributions).distinct
  else
    ::Otu.where.missing(:asserted_distributions)
  end
end

#asserted_environment_query_facet ⇒ Object 



581
582
583
584
585
586
 
# File 'lib/queries/otu/filter.rb', line 581

def asserted_environment_query_facet
  return nil if asserted_environment_query.nil?
  ::Otu
    .with(ae: asserted_environment_query.all)
    .joins("JOIN ae ON ae.asserted_environment_object_id = otus.id AND ae.asserted_environment_object_type = 'Otu'").distinct
end

#biological_association_id_facet ⇒ Object 



458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
 
# File 'lib/queries/otu/filter.rb', line 458

def biological_association_id_facet
  return nil if biological_association_id.empty?

  q1 = ::Otu.joins(:biological_associations).where(biological_associations: { id: biological_association_id, biological_association_subject_type: 'Otu' })
  q2 = ::Otu.joins(:related_biological_associations).where(related_biological_associations: { id: biological_association_id, biological_association_object_type: 'Otu' })
  q3 = ::Otu.joins(collection_objects: [:biological_associations]).where(biological_associations: { id: biological_association_id, biological_association_subject_type: 'CollectionObject' })
  q4 = ::Otu.joins(collection_objects: [:related_biological_associations]).where(related_biological_associations: { id: biological_association_id, biological_association_object_type: 'CollectionObject' })

  if historical_determinations.nil?
    q3 = q3.where(taxon_determinations: { position: 1 })
    q4 = q4.where(taxon_determinations: { position: 1 })
  elsif historical_determinations
    q3 = q3.where.not(taxon_determinations: { position: 1 })
    q4 = q4.where.not(taxon_determinations: { position: 1 })
  end

  query = referenced_klass_union([q1, q2, q3, q4])

  ::Otu.from("(#{query.to_sql}) as otus").distinct
end

#biological_association_query_facet ⇒ Object 



598
599
600
601
602
603
604
605
606
607
608
609
610
611
 
# File 'lib/queries/otu/filter.rb', line 598

def biological_association_query_facet
  return nil if biological_association_query.nil?
  s = 'WITH query_ba_otu AS (' + biological_association_query.all.to_sql + ') '

  a = ::Otu
    .joins("JOIN query_ba_otu as query_ba_otu1 on otus.id = query_ba_otu1.biological_association_subject_id AND query_ba_otu1.biological_association_subject_type = 'Otu'")

  b = ::Otu
    .joins("JOIN query_ba_otu as query_ba_otu2 on otus.id = query_ba_otu2.biological_association_object_id AND query_ba_otu2.biological_association_object_type = 'Otu'")

  s << referenced_klass_union([a, b]).to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#biological_associations_facet ⇒ Object

UNION, NOT EXISTS example



394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
 
# File 'lib/queries/otu/filter.rb', line 394

def biological_associations_facet
  return nil if biological_associations.nil?

  a = ::Otu.joins(:biological_associations)
  b = ::Otu.joins(:related_biological_associations)

  c = ::Otu.from("((#{a.to_sql}) UNION (#{b.to_sql})) as otus")

  if biological_associations
    ::Otu.from("((#{a.to_sql}) UNION (#{b.to_sql})) as otus")
  else
    ::Otu.where('NOT EXISTS (' + ::Otu.select('1').from( # not exists ( a select(1) from <opposite query>  )
                                                        ::Otu.from(
                                                          "((#{a.to_sql}) UNION (#{b.to_sql})) as ba_otus_join"
                                                        ).where('otus.id = ba_otus_join.id')
                                                       ).to_sql + ')')  # where includes in/out link
  end
end

#biological_associations_table ⇒ Object 



247
248
249
 
# File 'lib/queries/otu/filter.rb', line 247

def biological_associations_table
  ::BiologicalAssociation.arel_table
end

#biological_relationship_id_facet ⇒ Object 



479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
 
# File 'lib/queries/otu/filter.rb', line 479

def biological_relationship_id_facet
  return nil if biological_relationship_id.empty?

  q1 = ::Otu.joins(:biological_associations)
    .where(biological_associations: { biological_relationship_id:, biological_association_subject_type: 'Otu' })

  q2 = ::Otu.joins(:related_biological_associations)
    .where(related_biological_associations: { biological_relationship_id: })

  q3 = ::Otu.joins(collection_objects: [:biological_associations])
    .where(biological_associations: { biological_relationship_id:, biological_association_subject_type: 'CollectionObject' })
    .where(taxon_determinations: { position: 1 })

  q4 = ::Otu.joins(collection_objects: [:related_biological_associations])
    .where(related_biological_associations: { biological_relationship_id: })
    .where(taxon_determinations: { position: 1 })

  q5 = ::Otu.joins(field_occurrences: [:biological_associations])
    .where(biological_associations: { biological_relationship_id:, biological_association_subject_type: 'FieldOccurrence' })
    .where(taxon_determinations: { position: 1 })

  q6 = ::Otu.joins(field_occurrences: [:related_biological_associations])
    .where(related_biological_associations: { biological_relationship_id: })
    .where(taxon_determinations: { position: 1 })

  q7 = ::Otu.joins(anatomical_parts: [:biological_associations])
    .where(biological_associations: { biological_relationship_id:, biological_association_subject_type: 'AnatomicalPart' })

  q8 = ::Otu.joins(anatomical_parts: [:related_biological_associations])
    .where(related_biological_associations: { biological_relationship_id: })

  matched = ::Otu.from("(#{referenced_klass_union([q1, q2, q3, q4, q5, q6, q7, q8]).to_sql}) as otus").distinct

  if exclude_taxon_name_relationship
    ::Otu.where.not(id: matched)
  else
    matched
  end
end

#collecting_event_id_facet ⇒ Object 



447
448
449
450
451
452
453
454
455
456
 
# File 'lib/queries/otu/filter.rb', line 447

def collecting_event_id_facet
  return nil if collecting_event_id.empty?
  if historical_determinations.nil?
    ::Otu.joins(:collection_objects).where(collection_objects: { collecting_event_id: }, taxon_determinations: { position: 1 }).distinct
  elsif historical_determinations
    ::Otu.joins(:collection_objects).where(collection_objects: { collecting_event_id: }).where.not(taxon_determinations: { position: 1 }).distinct
  else
    ::Otu.joins(:collection_objects).where(collection_objects: { collecting_event_id: })
  end
end

#collecting_event_query_facet ⇒ Object 



635
636
637
638
639
640
641
642
643
644
 
# File 'lib/queries/otu/filter.rb', line 635

def collecting_event_query_facet
  return nil if collecting_event_query.nil?
  s = 'WITH query_ce_otus AS (' + collecting_event_query.all.to_sql + ') ' +
    ::Otu
    .joins(:collecting_events)
    .joins('JOIN query_ce_otus as query_ce_otus1 on collecting_events.id = query_ce_otus1.id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#collection_object_query_facet ⇒ Object 



613
614
615
616
617
618
619
620
621
622
 
# File 'lib/queries/otu/filter.rb', line 613

def collection_object_query_facet
  return nil if collection_object_query.nil?
  s = 'WITH query_co_otus AS (' + collection_object_query.all.to_sql + ') ' +
    ::Otu
    .joins(:collection_objects)
    .joins('JOIN query_co_otus as query_co_otus1 on collection_objects.id = query_co_otus1.id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#collection_objects_facet ⇒ Object 



413
414
415
416
417
418
419
420
 
# File 'lib/queries/otu/filter.rb', line 413

def collection_objects_facet
  return nil if collection_objects.nil?
  if collection_objects
    ::Otu.joins(:collection_objects).distinct
  else
    ::Otu.where.missing(:collection_objects)
  end
end

#common_names_facet ⇒ Object 



383
384
385
386
387
388
389
390
391
 
# File 'lib/queries/otu/filter.rb', line 383

def common_names_facet
  return nil if @common_names.nil?

  if @common_names
    ::Otu.joins(:common_names).distinct
  else
    ::Otu.where.missing(:common_names)
  end
end

#content_query_facet ⇒ Object 



588
589
590
591
592
593
594
595
596
 
# File 'lib/queries/otu/filter.rb', line 588

def content_query_facet
  return nil if content_query.nil?
  s = 'WITH query_con_otus AS (' + content_query.all.to_sql + ') ' +
    ::Otu
    .joins('JOIN query_con_otus as query_con_otus1 on otus.id = query_con_otus1.otu_id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#contents_facet ⇒ Object 



374
375
376
377
378
379
380
381
 
# File 'lib/queries/otu/filter.rb', line 374

def contents_facet
  return nil if contents.nil?
  if contents
    ::Otu.joins(:contents).distinct
  else
    ::Otu.where.missing(:contents)
  end
end

#coordinatify_result(q) ⇒ Object

Expands result of OTU filter query in 2 ways: 1 - to include all valid OTUs by (proxy of TaxonName) if OTU is by proxy invalid 2 - to include all invalid OTUS (by proxy of TaxonName) if OTU is by proxy valid

In essence this creates full sets of TaxonConcepts from partial results. The result can be used to, for example, get a comprehensive list of Sources for the concept, or a comprehensive historical list of Specimens, etc.



761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
 
# File 'lib/queries/otu/filter.rb', line 761

def coordinatify_result(q)

  i = q.joins(:taxon_name).where('taxon_names.id != taxon_names.cached_valid_taxon_name_id').where(project_id:)
  v = q.joins(:taxon_name).where('taxon_names.id = taxon_names.cached_valid_taxon_name_id').where(project_id:)

  # Find all valids related to a q-invalid
  s = 'WITH invalid_otu_result AS (' + i.to_sql + ') ' +
    ::Otu
    .joins('JOIN taxon_names tn1 on otus.taxon_name_id = tn1.cached_valid_taxon_name_id')
    .joins('JOIN invalid_otu_result AS invalid_otu_result1 ON invalid_otu_result1.taxon_name_id = tn1.id') # invalid otus matching valid names
    .to_sql

  a = ::Otu.from('(' + s + ') as otus')

  # Find all invalids related to a q-valid
  t = 'WITH valid_otu_result AS (' + v.to_sql + ') ' +
    ::Otu
    .joins('JOIN taxon_names tn2 on otus.taxon_name_id = tn2.id')
    .joins('JOIN valid_otu_result AS valid_otu_result1 ON valid_otu_result1.taxon_name_id = tn2.cached_valid_taxon_name_id') # valid otus matching invalid names
    .to_sql

  b = ::Otu.from('(' + t + ') as otus')

  referenced_klass_union([a, b, q])
end

#descriptor_id_facet ⇒ Object 



565
566
567
568
569
570
571
572
 
# File 'lib/queries/otu/filter.rb', line 565

def descriptor_id_facet
  return nil if descriptor_id.empty?

  q1 = ::Otu.joins(:descriptors).where(descriptors: { id: descriptor_id })
  q2 = ::Otu.joins(collection_objects: [:descriptors]).where(descriptors: { id: descriptor_id })

  referenced_klass_union([q1, q2]).distinct
end

#descriptor_query_facet ⇒ Object 



668
669
670
671
672
673
674
675
676
677
 
# File 'lib/queries/otu/filter.rb', line 668

def descriptor_query_facet
  return nil if descriptor_query.nil?
  s = 'WITH query_de_otus AS (' + descriptor_query.all.to_sql + ') ' +
    ::Otu
    .joins(:observations)
    .joins('JOIN query_de_otus as query_de_otus1 on observations.descriptor_id = query_de_otus1.id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#dwc_occurrence_query_facet ⇒ Object

!! This is a soft-link (i.e. no otu_id FK directly), so latency between indexing has a very small proability !! of impacting the results.



732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
 
# File 'lib/queries/otu/filter.rb', line 732

def dwc_occurrence_query_facet
  return nil if dwc_occurrence_query.nil?

  a = dwc_occurrence_query.all.object_join('CollectionObject').select(dwc_occurrences: [:dwc_occurrence_object_id])
  b = dwc_occurrence_query.all.object_join('AssertedDistribution').select(dwc_occurrences: [:dwc_occurrence_object_id])
  c = dwc_occurrence_query.all.object_join('FieldOccurrence').select(dwc_occurrences: [:dwc_occurrence_object_id])

  d = ::Otu.with(co: a)
        .joins(:taxon_determinations).where(taxon_determinations: {position: 1})
        .joins('JOIN co on co.dwc_occurrence_object_id = taxon_determinations.taxon_determination_object_id')

  e = ::Otu.with(ad: b)
        .joins(:asserted_distributions)
        .joins('JOIN ad on ad.dwc_occurrence_object_id = asserted_distributions.id')

  f = ::Otu.with(fo: c)
        .joins(:taxon_determinations).where(taxon_determinations: {position: 1})
        .joins('JOIN fo on fo.dwc_occurrence_object_id = taxon_determinations.taxon_determination_object_id')

  ::Queries.union(::Otu, [d,e,f])
end

#dwc_occurrences_facet ⇒ Object 



718
719
720
721
722
723
724
725
726
727
728
 
# File 'lib/queries/otu/filter.rb', line 718

def dwc_occurrences_facet
  return nil if @dwc_occurrences.nil?

  q = ::Otu.where(id: ::DwcOccurrence.select(:otu_id).where.not(otu_id: nil))

  if @dwc_occurrences
    q
  else
    ::Otu.where.not(id: q.select(:id))
  end
end

#extract_query_facet ⇒ Object 



646
647
648
649
650
651
652
653
654
655
 
# File 'lib/queries/otu/filter.rb', line 646

def extract_query_facet
  return nil if extract_query.nil?
  s = 'WITH query_ex_otus AS (' + extract_query.all.to_sql + ') ' +
    ::Otu
    .joins(:origin_relationships)
    .joins("JOIN query_ex_otus as query_ex_otus1 on origin_relationships.new_object_id = query_ex_otus1.id and origin_relationships.new_object_type = 'Extract'")
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#field_occurrence_query_facet ⇒ Object 



624
625
626
627
628
629
630
631
632
633
 
# File 'lib/queries/otu/filter.rb', line 624

def field_occurrence_query_facet
  return nil if field_occurrence_query.nil?
  s = 'WITH query_fo_otus AS (' + field_occurrence_query.all.to_sql + ') ' +
    ::Otu
    .joins(:field_occurrences)
    .joins('JOIN query_fo_otus as query_fo_otus1 on field_occurrences.id = query_fo_otus1.id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#from_geographic_items(geographic_items_where_sql) ⇒ Object 



324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
 
# File 'lib/queries/otu/filter.rb', line 324

def from_geographic_items(geographic_items_where_sql)
  ces = collecting_events_for_geographic_item_condition(
    geographic_items_where_sql
  )

  q1 = ::Otu
    .joins(collection_objects: [:collecting_event])
    .where(collecting_events: ces.all, project_id:)

  ads = ::Queries::AssertedDistribution::Filter
    .from_geographic_items(geographic_items_where_sql)

  q2 = ::Otu
    .joins(:asserted_distributions)
    .where(asserted_distributions: ads.all, project_id:)

  referenced_klass_union([q1,q2])
end

#from_wkt(wkt_shape, wkt_geometry_type = nil) ⇒ Object 



306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
 
# File 'lib/queries/otu/filter.rb', line 306

def from_wkt(wkt_shape, wkt_geometry_type = nil)
  c = ::Queries::CollectingEvent::Filter.new(
    wkt: wkt_shape, wkt_geometry_type:, project_id:
  )
  a = ::Queries::AssertedDistribution::Filter.new(
    wkt: wkt_shape, project_id:
  )

  q1 = ::Otu
    .joins(collection_objects: [:collecting_event])
    .where(collecting_events: c.all, project_id:)
  q2 = ::Otu
    .joins(:asserted_distributions)
    .where(asserted_distributions: a.all, project_id:)

  referenced_klass_union([q1, q2]).distinct # Not needed, union should be distinct
end

#genus_name_facet ⇒ Object 



432
433
434
435
 
# File 'lib/queries/otu/filter.rb', line 432

def genus_name_facet
  return nil if genus_name.blank?
  ::Otu.joins(:taxon_name).where(taxon_names: { cached: genus_name, rank_class: ::GENUS_ONLY_RANK_NAMES })
end

#geo_json_facet ⇒ Object 



343
344
345
346
347
348
349
350
351
352
353
354
 
# File 'lib/queries/otu/filter.rb', line 343

def geo_json_facet
  return nil if geo_json.blank?
  return ::Otu.none if roll_call

  c = ::Queries::CollectingEvent::Filter.new(geo_json:, project_id:, radius:)
  a = ::Queries::AssertedDistribution::Filter.new(geo_json:, project_id:, radius:)

  q1 = ::Otu.joins(collection_objects: [:collecting_event]).where(collecting_events: c.all, project_id:)
  q2 = ::Otu.joins(:asserted_distributions).where(asserted_distributions: a.all, project_id:)

  referenced_klass_union([q1, q2]).distinct
end

#loan_query_facet ⇒ Object 



679
680
681
682
683
684
685
686
687
688
689
690
691
692
 
# File 'lib/queries/otu/filter.rb', line 679

def loan_query_facet
  return nil if loan_query.nil?
  s = 'WITH query_loan_otus AS (' + loan_query.all.to_sql + ') '

  a = ::Otu.joins(:loan_items)
    .joins('JOIN query_loan_otus as query_loan_otus1 on loan_items.loan_id = query_loan_otus1.id')

  b = ::Otu.joins(collection_objects: [:loan_items])
    .joins('JOIN query_loan_otus as query_loan_otus1 on loan_items.loan_id = query_loan_otus1.id')

  s << referenced_klass_union([a, b]).to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#merge_clauses ⇒ Object 



808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
 
# File 'lib/queries/otu/filter.rb', line 808

def merge_clauses
  [
    anatomical_part_query_facet,
    asserted_distribution_query_facet,
    asserted_distributions_facet,
    asserted_environment_query_facet,
    biological_association_query_facet,
    collecting_event_query_facet,
    collection_object_query_facet,
    dwc_occurrences_facet,
    dwc_occurrence_query_facet,
    field_occurrence_query_facet,
    content_query_facet,
    descriptor_query_facet,
    extract_query_facet,
    loan_query_facet,
    observation_query_facet,
    sound_query_facet,
    taxon_name_query_facet,

    biological_association_id_facet,
    biological_associations_facet,
    biological_relationship_id_facet,
    collecting_event_id_facet,
    collection_objects_facet,
    common_names_facet,
    contents_facet,
    descriptor_id_facet,
    genus_name_facet,
    geo_json_facet,
    observations_facet,
    otu_geo_facet,
    taxon_name_id_facet,
    with_name_facet,
    wkt_facet,
  ].compact
end

#name_facet ⇒ Object 



279
280
281
282
283
284
285
286
 
# File 'lib/queries/otu/filter.rb', line 279

def name_facet
  return nil if name.empty?
  if name_exact
    table[:name].in(name)
  else
    table[:name].matches_any(name.collect { |n| '%' + n.gsub(/\s+/, '%') + '%' })
  end
end

#observation_query_facet ⇒ Object 



694
695
696
697
698
699
700
701
702
703
 
# File 'lib/queries/otu/filter.rb', line 694

def observation_query_facet
  return nil if observation_query.nil?
  s = 'WITH query_obs_otus AS (' + observation_query.all.to_sql + ') ' +
    ::Otu
    .joins(:observations)
    .joins('JOIN query_obs_otus as query_obs_otus1 on observations.id = query_obs_otus1.id')
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#observations_facet ⇒ Object 



437
438
439
440
441
442
443
444
445
 
# File 'lib/queries/otu/filter.rb', line 437

def observations_facet
  return nil if observations.nil?

  if observations
    ::Otu.joins(:observations).distinct
  else
    ::Otu.where.missing(:observations)
  end
end

#otu_geo_facet ⇒ Object 



519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
 
# File 'lib/queries/otu/filter.rb', line 519

def otu_geo_facet
  return nil if geo_shape_id.empty? || geo_shape_type.empty? ||
    # TODO: this should raise an error(?)
    geo_shape_id.length != geo_shape_type.length
    return ::Otu.none if roll_call

  geographic_area_shapes, gazetteer_shapes = shapes_for_geo_mode

  a, b = otu_geo_facet_by_type('GeographicArea', geographic_area_shapes)

  c, _d = otu_geo_facet_by_type('Gazetteer', gazetteer_shapes)

  if geo_mode != true # exact or descendants
    return referenced_klass_union([a,b,c])
  end

  # Spatial.
  i = ::Queries.union(::GeographicItem, [a,c])

  from_geographic_items(
    ::GeographicItem.covered_by_geographic_items_sql(i)
  )
end

#otu_geo_facet_by_type(shape_string, shape_ids) ⇒ Object 



543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
 
# File 'lib/queries/otu/filter.rb', line 543

def otu_geo_facet_by_type(shape_string, shape_ids)
  b = nil # from AssertedDistributions
  c = nil # from CollectionObjects

  case geo_mode
  when nil, false # exact, descendants
    b = ::Otu.joins(:asserted_distributions)
     .where(asserted_distributions: {
        asserted_distribution_shape: shape_ids
     })
    if shape_string == 'GeographicArea'
      c = ::Otu.joins(collection_objects: [:collecting_event])
        .where(collecting_events: { geographic_area: shape_ids })
    end
  when true # spatial
    m = shape_string.tableize
    b = ::GeographicItem.joins(m.to_sym).where(m => shape_ids)
  end

  [b,c]
end

#sound_query_facet ⇒ Object 



705
706
707
 
# File 'lib/queries/otu/filter.rb', line 705

def sound_query_facet
  otus_from_sound_query
end

#taxon_name_facet ⇒ Object 



422
423
424
425
426
427
428
429
430
 
# File 'lib/queries/otu/filter.rb', line 422

def taxon_name_facet
  return nil if taxon_name.nil?

  if taxon_name
    table[:taxon_name_id].not_eq(nil)
  else
    table[:taxon_name_id].eq(nil)
  end
end

#taxon_name_id_facet ⇒ Object 



288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/queries/otu/filter.rb', line 288

def taxon_name_id_facet
  return nil if taxon_name_id.empty?
  if descendants
    h = Arel::Table.new(:taxon_name_hierarchies)
    j = table.join(h, Arel::Nodes::InnerJoin).on(table[:taxon_name_id].eq(h[:descendant_id]))
    z = h[:ancestor_id].in(taxon_name_id)

    ::Otu.joins(j.join_sources).where(z).distinct # Maybe not needed
  else
    ::Otu.where(taxon_name_id:)
  end
end

#taxon_name_query_facet ⇒ Object 



657
658
659
660
661
662
663
664
665
666
 
# File 'lib/queries/otu/filter.rb', line 657

def taxon_name_query_facet
  return nil if taxon_name_query.nil?
  s = 'WITH query_taxon_names AS (' + taxon_name_query.all.to_sql + ') ' +
    ::Otu
    .joins('JOIN query_taxon_names as query_taxon_names1 on otus.taxon_name_id = query_taxon_names1.id')
    .where.not(taxon_name_id: nil) # .joins(:taxon_name)
    .to_sql

  ::Otu.from('(' + s + ') as otus').distinct
end

#with_name_facet ⇒ Object 



365
366
367
368
369
370
371
372
 
# File 'lib/queries/otu/filter.rb', line 365

def with_name_facet
  return nil if with_name.nil?
  if with_name
    ::Otu.where.not(name: nil)
  else
    ::Otu.where(name: nil)
  end
end

#wkt_facet ⇒ Object 



301
302
303
304
 
# File 'lib/queries/otu/filter.rb', line 301

def wkt_facet
  return nil if wkt.nil?
  from_wkt(wkt)
end