Module: TaxonName::Hierarchy::ClassMethods

Defined in:
app/models/taxon_name/hierarchy.rb

Instance Method Summary collapse

Instance Method Details

#ranked_otus(otu_scope: Otu.none, ranks: ['order', 'family', 'genus', 'species']) ⇒ Object

Use ‘.attributes to directly return Hash

A bit of a hybrid method, might also fit as an Otu class method

Parameters:

  • otu_scope (defaults to: Otu.none) —

    Otu::ActiveRecordRelation distinct, no order, no select

Returns:

  • Array of Objects [ { “id” => 926908,

    "name" => "Diestrammena (Atachycines) horazumi",
"taxon_name_id" => 946156,
"order" => "Orthoptera",
"family" => "Rhaphidophoridae",
"genus" => "Atachycines",
"species" => "apicalis" } ]



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'app/models/taxon_name/hierarchy.rb', line 81

def ranked_otus(otu_scope: Otu.none, ranks: ['order', 'family', 'genus', 'species'])
  return Otu.none if ranks.blank? || otu_scope.blank?

  tns = ::Queries::TaxonName::Filter.new({})
  tns.otu_query = otu_scope.unscope(:order).unscope(:select)

  s = 'WITH tn_anc AS (' + taxon_name_ancestors_sql(taxon_name_scope: tns.all, ranks: ) + '), otu_limit AS (' + otu_scope.select(:id).to_sql + ')' +
    ::Otu
    .joins('LEFT JOIN taxon_names tn_ca on otus.taxon_name_id = tn_ca.id')
    .joins('JOIN otu_limit AS ol on ol.id = otus.id')
    .select("otus.id, otus.name, tn_ca.cached, tn_ca.cached_author_year, otus.taxon_name_id, #{ranks.collect{|r| "tn_anc1.#{r}"}.join(', ')}").joins('JOIN tn_anc as tn_anc1 ON otus.taxon_name_id = tn_anc1.id')
    .distinct
    .to_sql

  ::Otu.find_by_sql(s)
end

#ranked_taxon_names(taxon_name_scope: TaxonName.none, ranks: ['order', 'family', 'genus', 'species']) ⇒ Object

Return summaries of TaxonName with their requested ranks/names

Parameters:

  • taxon_name_scope (defaults to: TaxonName.none) —

    TaxonName::ActiveRecordRelation

Returns:

  • Array of TaxonName (not a relation!)



16
17
18
19
20
 
# File 'app/models/taxon_name/hierarchy.rb', line 16

def ranked_taxon_names(taxon_name_scope: TaxonName.none, ranks: ['order', 'family', 'genus', 'species'])
  TaxonName.find_by_sql(
    taxon_name_ancestors_sql(taxon_name_scope:, ranks:)
  )
end

#taxon_name_ancestors_sql(taxon_name_scope: TaxonName.none, ranks: ['order', 'family', 'genus', 'species']) ⇒ Object

A SQL string that builds a crosstab query, selecting names at the requested ranks into columns, i.e. summarizing ranks in a single row.

The ‘unnest` was critical in getting this correct, as was distinct, and ordering.

TODO: we’d have to ?UNION? in the cached/cached_author_year fields?

Parameters:

  • taxon_name_scope (defaults to: TaxonName.none) —

    TaxonName::ActiveRecordRelation required

Returns:

  • String



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'app/models/taxon_name/hierarchy.rb', line 32

def taxon_name_ancestors_sql(taxon_name_scope: TaxonName.none, ranks: ['order', 'family', 'genus', 'species'])
  ranks = RANKS_BY_NAME.keys if ranks.blank?

  target_ranks = []

  # Note that we need to single quote the first query in the crosstab,
  # thuse the $$ and other quote weirdness

  # !!! scoping query *must* be distinct results, and without order
  s = "SELECT * from crosstab( 'WITH tnq AS (" + taxon_name_scope.unscope(:select, :order).select(:id).all.distinct.to_sql.gsub(/'/,"''") + 
    ' ) SELECT h.descendant_id id, CASE '

  ord = []

  # translate `rank_class` to common name, 
  # works across nomenclature codes, could be optimized
  # to target a specific code at some point
  ranks.each_with_index do |r, i|
    l = RANKS_BY_NAME[r].collect{ |x| "''#{x}''" }
    target_ranks += l  
    ord.push "\n WHEN rank_class IN (#{ l.join(', ') }) THEN ''#{r}'' "
  end

  s << ord.join("\n ")
  s << 'ELSE null END cat, t.name value FROM taxon_names t ' +  
    "JOIN taxon_name_hierarchies h on t.id = h.ancestor_id 
     JOIN tnq as tnq1 on tnq1.id = h.descendant_id
     WHERE 
       t.rank_class IN (#{target_ranks.join(', ')}) 
     AND t.name != ''Root'' " +
  "GROUP BY 1,2,3',
   $$SELECT unnest('{#{ranks.join(', ')}}'::character varying[])$$) 
   AS ct(id integer, " + ranks.collect{|r| "\"#{r}\" character varying"}.join(', ') + ')'
end