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'], project_id: nil) ⇒ 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" } ]



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

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

  tn = Arel.sql(taxon_name_ancestors_sql(taxon_name_scope: TaxonName.where(project_id:).all, ranks:))

  s =  otu_scope.with(tn_anc: tn)
    .joins('LEFT JOIN tn_anc ON otus.taxon_name_id = tn_anc.id')
    .joins('LEFT JOIN taxon_names tn on tn.id = tn_anc.id')
    .select("otus.id, otus.name, tn.cached, tn.cached_author_year, otus.taxon_name_id, #{ranks.collect{|r| "tn_anc.#{r}"}.join(', ')}")
    .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

Raises:

  • (ArgumentError) 


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
66
67
68
69
 
# 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?

  # Validate all ranks are known
  invalid_ranks = ranks.reject { |r| RANKS_BY_NAME.key?(r) }
  raise ArgumentError, "Invalid rank names: #{invalid_ranks.join(', ')}" if invalid_ranks.any?

  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