mjy commented 4 years ago

It feels like we need to cache NOMEN order in a table to address a lot of sort/ordering outside of SQL. Something like this:

[x] Add a migration to build the table

table :nomenclatural_rank_order
:rank_class, :string
:position, :integer # just start each code at the next Int, don't start each from 1

[x] Build a way to re-generate the table based on inspection of NomenclaturalRank.descendants
[ ] Add a data migration that triggers the population of the table so that test databases include an index on build, not on spec run
[x] Add a deploy hook so that nomenclatural_rank_orders <- note plural is wiped and rebuilt on every deploy
[x] Add has_one :nomenclatural_rank_class, foreign_key: :rank_class ... to TaxonName

[x] Add scope :order_by_nomenclatural_rank to TaxonName:

joins(:nomenclatural_rank_class).order(nomenclatural_rank_class: :position)

Start recording .sort methods by just including the scope. Specs should catch that things are still OK.

mjy commented 4 years ago

Need to also ensure that test database does not wipe the table between runs (it shouldn't I think, but I'd have to think about this).

LocoDelAssembly commented 4 years ago

https://www.postgresql.org/docs/10/datatype-enum.html#id-1.5.7.15.6 <- Almost, but DETAIL: Labels must be 63 characters or less....

Thinking a bit more. Do you see problems doing this like https://stackoverflow.com/a/23620658 ?

mjy commented 4 years ago

Interesting re ENUM.

I did think about about the CASE solution. Things that I don't like, but they're not necessarily game breakers: 1) it will be called a lot, and there are 40(?) + case checks, this might expand logs a lot 2) Unsure about performance? Is CASE on par with a clean join? 3) We'd still need to generate code every deploy, to reflect possible changes in the models, so there is no win there.

LocoDelAssembly commented 4 years ago

Unsure about performance? Is CASE on par with a clean join?

Unsure as well, but sounds a bit more expensive to deal with an extra table than doing comparisons in-place.

We'd still need to generate code every deploy, to reflect possible changes in the models, so there is no win there.

I'd expect no trouble in this regard, build the arel case with memoization when order_by_nomenclatural_rank is used, generated by traversing NomenclaturalRank descendants in BFS order. ENUM would requiring watching the type is in sync with rails code, but given the 63 chars limit it is not an option.

It is not clear if I could use a function and then order by it. Only problem though is that rails db:schema:load won't create the function most likely.

mjy commented 4 years ago

If the model memoizes, and it only has to be called 1x, then we should at least try that route, as it requires the least work.

IIRC you can create functions with migrations.

Where do we actually use db:schema:load in practice? I dont' think we should be depending on it anywhere?

proceps commented 4 years ago

this works: scope :order_by_rank, -> (code) {order("position(taxon_names.rank_class in '#{code}')")}

call: Protonym.named('Zygina').order_by_rank(ICZN+ICN)

mjy commented 4 years ago

Nice @proceps. If that is memoized it should work as hard-coded scope I think?

 # Do we need to memoize?!
  attr_accessor :order_by_rank
  def order_by_rank
      @order_by_rank ||= order("position(taxon_names.rank_class in '#{code}')", ICZN+ICN)
  end

mjy commented 4 years ago

I do still wonder about performance of this vs. join BTW. Might be worth benchmarking.

LocoDelAssembly commented 4 years ago

(Just prototype to test idea, backwards order on purpose)

  scope :order_by_nomenclatural_rank, -> { 
    order(arel_table[:rank_class].
      when("NomenclaturalRank::Iczn::GenusGroup::Genus").then(1).
      when("NomenclaturalRank::Iczn::FamilyGroup::Subtribe").then(2).asc
    ) }

Cool idea @proceps. Does it work for all cases or requires ICZN+ICN to be BFS-sorted first?

proceps commented 4 years ago

You can scope to one code, or combine all four, not sure, but it could be little bit faster, if only one code is used, if there is no difference, all four codes could be hard coded into the scope

proceps commented 4 years ago

Protonym.named('Zygina').order_by_rank(ICZN+ICN) vs Protonym.named('Zygina').order_by_rank(ICZN)

proceps commented 4 years ago

ICZN just returns an array of all ranks for animals, ICN - same for plants. We have two more codes. May be we have a method which combines all 4 nomenclatural codes into a single array

proceps commented 4 years ago

Protonym.named('Zygina').order_by_rank(RANKS) should work for all four

LocoDelAssembly commented 4 years ago

@proceps @mjy benchmarked both ideas:

2.7.1 :024 > TaxonName.sort_with_position.first
DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "position(taxon_names.rank_class in '[\"NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder\", \"NomenclaturalRank::Iczn::FamilyGroup::Superfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Epifamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Family\", \"NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Infrafamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Supertribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Tribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Infratribe\", \"NomenclaturalRank::Iczn::GenusGroup::Supergenus\", \"NomenclaturalRank::Iczn::GenusGroup::Genus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Subgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Infragenus\", \"NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Superspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Species\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icn::FamilyGroup::Family\", \"NomenclaturalRank::Icn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icn::FamilyGroup::Tribe\", \"NomenclaturalRank::Icn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icn::GenusGroup::Genus\", \"NomenclaturalRank::Icn::GenusGroup::Subgenus\", \"NomenclaturalRank::Icn::GenusGroup::Section\", \"NomenclaturalRank::Icn::GenusGroup::Subsection\", \"NomenclaturalRank::Icn::GenusGroup::Series\", \"NomenclaturalRank::Icn::GenusGroup::Subseries\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icnp::FamilyGroup::Family\", \"NomenclaturalRank::Icnp::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icnp::FamilyGroup::Tribe\", \"NomenclaturalRank::Icnp::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icnp::GenusGroup::Genus\", \"NomenclaturalRank::Icnp::GenusGroup::Subgenus\", \"NomenclaturalRank::Icnp::SpeciesGroup::Species\", \"NomenclaturalRank::Icnp::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icvcn::Kingdom\", \"NomenclaturalRank::Icvcn::Order\", \"NomenclaturalRank::Icvcn::Family\", \"NomenclaturalRank::Icvcn::Subfamily\", \"NomenclaturalRank::Icvcn::Genus\", \"NomenclaturalRank::Icvcn::Species\"]')". Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from sort_with_position at /home/hernan/Projects/taxonworks/app/models/taxon_name.rb:274)
  TaxonName Load (562.7ms)  SELECT "taxon_names".* FROM "taxon_names" ORDER BY position(taxon_names.rank_class in '["NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom", "NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom", "NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom", "NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom", "NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum", "NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum", "NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum", "NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum", "NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass", "NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank", "NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass", "NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass", "NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort", "NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort", "NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Order", "NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder", "NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder", "NomenclaturalRank::Iczn::FamilyGroup::Superfamily", "NomenclaturalRank::Iczn::FamilyGroup::Epifamily", "NomenclaturalRank::Iczn::FamilyGroup::Family", "NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily", "NomenclaturalRank::Iczn::FamilyGroup::Subfamily", "NomenclaturalRank::Iczn::FamilyGroup::Infrafamily", "NomenclaturalRank::Iczn::FamilyGroup::Supertribe", "NomenclaturalRank::Iczn::FamilyGroup::Tribe", "NomenclaturalRank::Iczn::FamilyGroup::Subtribe", "NomenclaturalRank::Iczn::FamilyGroup::Infratribe", "NomenclaturalRank::Iczn::GenusGroup::Supergenus", "NomenclaturalRank::Iczn::GenusGroup::Genus", "NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus", "NomenclaturalRank::Iczn::GenusGroup::Supersubgenus", "NomenclaturalRank::Iczn::GenusGroup::Subgenus", "NomenclaturalRank::Iczn::GenusGroup::Infragenus", "NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies", "NomenclaturalRank::Iczn::SpeciesGroup::Superspecies", "NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies", "NomenclaturalRank::Iczn::SpeciesGroup::Species", "NomenclaturalRank::Iczn::SpeciesGroup::Subspecies", "NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom", "NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom", "NomenclaturalRank::Icn::HigherClassificationGroup::Phylum", "NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum", "NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank", "NomenclaturalRank::Icn::HigherClassificationGroup::Subclass", "NomenclaturalRank::Icn::HigherClassificationGroup::Order", "NomenclaturalRank::Icn::HigherClassificationGroup::Suborder", "NomenclaturalRank::Icn::FamilyGroup::Family", "NomenclaturalRank::Icn::FamilyGroup::Subfamily", "NomenclaturalRank::Icn::FamilyGroup::Tribe", "NomenclaturalRank::Icn::FamilyGroup::Subtribe", "NomenclaturalRank::Icn::GenusGroup::Genus", "NomenclaturalRank::Icn::GenusGroup::Subgenus", "NomenclaturalRank::Icn::GenusGroup::Section", "NomenclaturalRank::Icn::GenusGroup::Subsection", "NomenclaturalRank::Icn::GenusGroup::Series", "NomenclaturalRank::Icn::GenusGroup::Subseries", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form", "NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform", "NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom", "NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum", "NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank", "NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass", "NomenclaturalRank::Icnp::HigherClassificationGroup::Order", "NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder", "NomenclaturalRank::Icnp::FamilyGroup::Family", "NomenclaturalRank::Icnp::FamilyGroup::Subfamily", "NomenclaturalRank::Icnp::FamilyGroup::Tribe", "NomenclaturalRank::Icnp::FamilyGroup::Subtribe", "NomenclaturalRank::Icnp::GenusGroup::Genus", "NomenclaturalRank::Icnp::GenusGroup::Subgenus", "NomenclaturalRank::Icnp::SpeciesGroup::Species", "NomenclaturalRank::Icnp::SpeciesGroup::Subspecies", "NomenclaturalRank::Icvcn::Kingdom", "NomenclaturalRank::Icvcn::Order", "NomenclaturalRank::Icvcn::Family", "NomenclaturalRank::Icvcn::Subfamily", "NomenclaturalRank::Icvcn::Genus", "NomenclaturalRank::Icvcn::Species"]') LIMIT $1  [["LIMIT", 1]]
 => #<Protonym id: 1, name: "Root", parent_id: nil, cached_html: "Root", cached_author_year: nil, cached_higher_classification: "", created_at: "2016-04-26 16:59:29", updated_at: "2016-04-26 16:59:29", year_of_publication: nil, verbatim_author: nil, rank_class: "NomenclaturalRank", type: "Protonym", created_by_id: 1, updated_by_id: 1, project_id: 1, cached_original_combination_html: nil, cached_secondary_homonym: nil, cached_primary_homonym: "Root", cached_secondary_homonym_alternative_spelling: nil, cached_primary_homonym_alternative_spelling: "Root", cached_misspelling: nil, masculine_name: nil, feminine_name: nil, neuter_name: nil, cached_classified_as: nil, cached: "Root", verbatim_name: nil, cached_valid_taxon_name_id: 1, etymology: nil, cached_original_combination: nil>

Using Arel CASE:

2.7.1 :025 > TaxonName.sort_with_case.first
  TaxonName Load (75.9ms)  SELECT "taxon_names".* FROM "taxon_names" ORDER BY CASE "taxon_names"."rank_class" WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom' THEN 0 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom' THEN 1 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom' THEN 2 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom' THEN 3 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum' THEN 4 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum' THEN 5 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum' THEN 6 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum' THEN 7 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass' THEN 8 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank' THEN 9 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass' THEN 10 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass' THEN 11 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort' THEN 12 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort' THEN 13 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder' THEN 14 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder' THEN 15 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder' THEN 16 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Order' THEN 17 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder' THEN 18 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder' THEN 19 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder' THEN 20 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder' THEN 21 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Superfamily' THEN 22 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Epifamily' THEN 23 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Family' THEN 24 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily' THEN 25 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Subfamily' THEN 26 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Infrafamily' THEN 27 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Supertribe' THEN 28 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Tribe' THEN 29 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Subtribe' THEN 30 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Infratribe' THEN 31 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supergenus' THEN 32 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Genus' THEN 33 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus' THEN 34 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supersubgenus' THEN 35 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Subgenus' THEN 36 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Infragenus' THEN 37 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies' THEN 38 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Superspecies' THEN 39 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies' THEN 40 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Species' THEN 41 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Subspecies' THEN 42 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom' THEN 43 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom' THEN 44 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Phylum' THEN 45 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum' THEN 46 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank' THEN 47 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subclass' THEN 48 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Order' THEN 49 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Suborder' THEN 50 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Family' THEN 51 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Subfamily' THEN 52 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Tribe' THEN 53 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Subtribe' THEN 54 WHEN 'NomenclaturalRank::Icn::GenusGroup::Genus' THEN 55 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subgenus' THEN 56 WHEN 'NomenclaturalRank::Icn::GenusGroup::Section' THEN 57 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subsection' THEN 58 WHEN 'NomenclaturalRank::Icn::GenusGroup::Series' THEN 59 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subseries' THEN 60 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species' THEN 61 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies' THEN 62 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety' THEN 63 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety' THEN 64 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form' THEN 65 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform' THEN 66 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom' THEN 67 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum' THEN 68 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank' THEN 69 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass' THEN 70 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Order' THEN 71 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder' THEN 72 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Family' THEN 73 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Subfamily' THEN 74 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Tribe' THEN 75 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Subtribe' THEN 76 WHEN 'NomenclaturalRank::Icnp::GenusGroup::Genus' THEN 77 WHEN 'NomenclaturalRank::Icnp::GenusGroup::Subgenus' THEN 78 WHEN 'NomenclaturalRank::Icnp::SpeciesGroup::Species' THEN 79 WHEN 'NomenclaturalRank::Icnp::SpeciesGroup::Subspecies' THEN 80 WHEN 'NomenclaturalRank::Icvcn::Kingdom' THEN 81 WHEN 'NomenclaturalRank::Icvcn::Order' THEN 82 WHEN 'NomenclaturalRank::Icvcn::Family' THEN 83 WHEN 'NomenclaturalRank::Icvcn::Subfamily' THEN 84 WHEN 'NomenclaturalRank::Icvcn::Genus' THEN 85 WHEN 'NomenclaturalRank::Icvcn::Species' THEN 86 END ASC LIMIT $1  [["LIMIT", 1]]
 => #<Protonym id: 29, name: "Animalia", parent_id: 7, cached_html: "Animalia", cached_author_year: nil, cached_higher_classification: nil, created_at: "2020-10-02 05:10:10", updated_at: "2020-10-02 05:10:10", year_of_publication: nil, verbatim_author: nil, rank_class: "NomenclaturalRank::Iczn::HigherClassificationGroup...", type: "Protonym", created_by_id: 1, updated_by_id: 1, project_id: 7, cached_original_combination_html: nil, cached_secondary_homonym: nil, cached_primary_homonym: "Animalia", cached_secondary_homonym_alternative_spelling: nil, cached_primary_homonym_alternative_spelling: "Animalia", cached_misspelling: nil, masculine_name: nil, feminine_name: nil, neuter_name: nil, cached_classified_as: nil, cached: "Animalia", verbatim_name: nil, cached_valid_taxon_name_id: 29, etymology: nil, cached_original_combination: nil> 
2.7.1 :026 > TaxonName.sort_with_case.last
  TaxonName Load (76.5ms)  SELECT "taxon_names".* FROM "taxon_names" ORDER BY CASE "taxon_names"."rank_class" WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom' THEN 0 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom' THEN 1 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom' THEN 2 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom' THEN 3 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum' THEN 4 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum' THEN 5 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum' THEN 6 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum' THEN 7 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass' THEN 8 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank' THEN 9 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass' THEN 10 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass' THEN 11 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort' THEN 12 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort' THEN 13 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder' THEN 14 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder' THEN 15 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder' THEN 16 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Order' THEN 17 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder' THEN 18 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder' THEN 19 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder' THEN 20 WHEN 'NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder' THEN 21 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Superfamily' THEN 22 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Epifamily' THEN 23 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Family' THEN 24 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily' THEN 25 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Subfamily' THEN 26 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Infrafamily' THEN 27 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Supertribe' THEN 28 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Tribe' THEN 29 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Subtribe' THEN 30 WHEN 'NomenclaturalRank::Iczn::FamilyGroup::Infratribe' THEN 31 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supergenus' THEN 32 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Genus' THEN 33 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus' THEN 34 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Supersubgenus' THEN 35 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Subgenus' THEN 36 WHEN 'NomenclaturalRank::Iczn::GenusGroup::Infragenus' THEN 37 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies' THEN 38 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Superspecies' THEN 39 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies' THEN 40 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Species' THEN 41 WHEN 'NomenclaturalRank::Iczn::SpeciesGroup::Subspecies' THEN 42 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom' THEN 43 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom' THEN 44 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Phylum' THEN 45 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum' THEN 46 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank' THEN 47 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Subclass' THEN 48 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Order' THEN 49 WHEN 'NomenclaturalRank::Icn::HigherClassificationGroup::Suborder' THEN 50 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Family' THEN 51 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Subfamily' THEN 52 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Tribe' THEN 53 WHEN 'NomenclaturalRank::Icn::FamilyGroup::Subtribe' THEN 54 WHEN 'NomenclaturalRank::Icn::GenusGroup::Genus' THEN 55 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subgenus' THEN 56 WHEN 'NomenclaturalRank::Icn::GenusGroup::Section' THEN 57 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subsection' THEN 58 WHEN 'NomenclaturalRank::Icn::GenusGroup::Series' THEN 59 WHEN 'NomenclaturalRank::Icn::GenusGroup::Subseries' THEN 60 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species' THEN 61 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies' THEN 62 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety' THEN 63 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety' THEN 64 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form' THEN 65 WHEN 'NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform' THEN 66 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom' THEN 67 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum' THEN 68 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank' THEN 69 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass' THEN 70 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Order' THEN 71 WHEN 'NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder' THEN 72 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Family' THEN 73 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Subfamily' THEN 74 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Tribe' THEN 75 WHEN 'NomenclaturalRank::Icnp::FamilyGroup::Subtribe' THEN 76 WHEN 'NomenclaturalRank::Icnp::GenusGroup::Genus' THEN 77 WHEN 'NomenclaturalRank::Icnp::GenusGroup::Subgenus' THEN 78 WHEN 'NomenclaturalRank::Icnp::SpeciesGroup::Species' THEN 79 WHEN 'NomenclaturalRank::Icnp::SpeciesGroup::Subspecies' THEN 80 WHEN 'NomenclaturalRank::Icvcn::Kingdom' THEN 81 WHEN 'NomenclaturalRank::Icvcn::Order' THEN 82 WHEN 'NomenclaturalRank::Icvcn::Family' THEN 83 WHEN 'NomenclaturalRank::Icvcn::Subfamily' THEN 84 WHEN 'NomenclaturalRank::Icvcn::Genus' THEN 85 WHEN 'NomenclaturalRank::Icvcn::Species' THEN 86 END DESC LIMIT $1  [["LIMIT", 1]]
 => #<Protonym id: 1, name: "Root", parent_id: nil, cached_html: "Root", cached_author_year: nil, cached_higher_classification: "", created_at: "2016-04-26 16:59:29", updated_at: "2016-04-26 16:59:29", year_of_publication: nil, verbatim_author: nil, rank_class: "NomenclaturalRank", type: "Protonym", created_by_id: 1, updated_by_id: 1, project_id: 1, cached_original_combination_html: nil, cached_secondary_homonym: nil, cached_primary_homonym: "Root", cached_secondary_homonym_alternative_spelling: nil, cached_primary_homonym_alternative_spelling: "Root", cached_misspelling: nil, masculine_name: nil, feminine_name: nil, neuter_name: nil, cached_classified_as: nil, cached: "Root", verbatim_name: nil, cached_valid_taxon_name_id: 1, etymology: nil, cached_original_combination: nil>

Also, with position method there is no way for AR to reverse order if told to:

2.7.1 :027 > TaxonName.sort_with_position.last
DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "position(taxon_names.rank_class in '[\"NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder\", \"NomenclaturalRank::Iczn::FamilyGroup::Superfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Epifamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Family\", \"NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Infrafamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Supertribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Tribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Infratribe\", \"NomenclaturalRank::Iczn::GenusGroup::Supergenus\", \"NomenclaturalRank::Iczn::GenusGroup::Genus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Subgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Infragenus\", \"NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Superspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Species\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icn::FamilyGroup::Family\", \"NomenclaturalRank::Icn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icn::FamilyGroup::Tribe\", \"NomenclaturalRank::Icn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icn::GenusGroup::Genus\", \"NomenclaturalRank::Icn::GenusGroup::Subgenus\", \"NomenclaturalRank::Icn::GenusGroup::Section\", \"NomenclaturalRank::Icn::GenusGroup::Subsection\", \"NomenclaturalRank::Icn::GenusGroup::Series\", \"NomenclaturalRank::Icn::GenusGroup::Subseries\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icnp::FamilyGroup::Family\", \"NomenclaturalRank::Icnp::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icnp::FamilyGroup::Tribe\", \"NomenclaturalRank::Icnp::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icnp::GenusGroup::Genus\", \"NomenclaturalRank::Icnp::GenusGroup::Subgenus\", \"NomenclaturalRank::Icnp::SpeciesGroup::Species\", \"NomenclaturalRank::Icnp::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icvcn::Kingdom\", \"NomenclaturalRank::Icvcn::Order\", \"NomenclaturalRank::Icvcn::Family\", \"NomenclaturalRank::Icvcn::Subfamily\", \"NomenclaturalRank::Icvcn::Genus\", \"NomenclaturalRank::Icvcn::Species\"]')". Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from sort_with_position at /home/hernan/Projects/taxonworks/app/models/taxon_name.rb:274)
Traceback (most recent call last):
        1: from (irb):27
ActiveRecord::IrreversibleOrderError (Order "position(taxon_names.rank_class in '[\"NomenclaturalRank::Iczn::HigherClassificationGroup::Superkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infrakingdom\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraphylum\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraclass\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Supercohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Cohort\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Magnorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Superorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Mirorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Infraorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Parvorder\", \"NomenclaturalRank::Iczn::HigherClassificationGroup::Nanorder\", \"NomenclaturalRank::Iczn::FamilyGroup::Superfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Epifamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Family\", \"NomenclaturalRank::Iczn::FamilyGroup::Supersubfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Infrafamily\", \"NomenclaturalRank::Iczn::FamilyGroup::Supertribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Tribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Iczn::FamilyGroup::Infratribe\", \"NomenclaturalRank::Iczn::GenusGroup::Supergenus\", \"NomenclaturalRank::Iczn::GenusGroup::Genus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersupersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Supersubgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Subgenus\", \"NomenclaturalRank::Iczn::GenusGroup::Infragenus\", \"NomenclaturalRank::Iczn::SpeciesGroup::Supersuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Superspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subsuperspecies\", \"NomenclaturalRank::Iczn::SpeciesGroup::Species\", \"NomenclaturalRank::Iczn::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subkingdom\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subphylum\", \"NomenclaturalRank::Icn::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icn::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icn::FamilyGroup::Family\", \"NomenclaturalRank::Icn::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icn::FamilyGroup::Tribe\", \"NomenclaturalRank::Icn::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icn::GenusGroup::Genus\", \"NomenclaturalRank::Icn::GenusGroup::Subgenus\", \"NomenclaturalRank::Icn::GenusGroup::Section\", \"NomenclaturalRank::Icn::GenusGroup::Subsection\", \"NomenclaturalRank::Icn::GenusGroup::Series\", \"NomenclaturalRank::Icn::GenusGroup::Subseries\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Species\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subspecies\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Variety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subvariety\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Form\", \"NomenclaturalRank::Icn::SpeciesAndInfraspeciesGroup::Subform\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Kingdom\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Phylum\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::ClassRank\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Subclass\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Order\", \"NomenclaturalRank::Icnp::HigherClassificationGroup::Suborder\", \"NomenclaturalRank::Icnp::FamilyGroup::Family\", \"NomenclaturalRank::Icnp::FamilyGroup::Subfamily\", \"NomenclaturalRank::Icnp::FamilyGroup::Tribe\", \"NomenclaturalRank::Icnp::FamilyGroup::Subtribe\", \"NomenclaturalRank::Icnp::GenusGroup::Genus\", \"NomenclaturalRank::Icnp::GenusGroup::Subgenus\", \"NomenclaturalRank::Icnp::SpeciesGroup::Species\", \"NomenclaturalRank::Icnp::SpeciesGroup::Subspecies\", \"NomenclaturalRank::Icvcn::Kingdom\", \"NomenclaturalRank::Icvcn::Order\", \"NomenclaturalRank::Icvcn::Family\", \"NomenclaturalRank::Icvcn::Subfamily\", \"NomenclaturalRank::Icvcn::Genus\", \"NomenclaturalRank::Icvcn::Species\"]')" cannot be reversed automatically)

Code diff:

diff --git a/app/models/taxon_name.rb b/app/models/taxon_name.rb
index 2794590f8..e824f7aca 100644
--- a/app/models/taxon_name.rb
+++ b/app/models/taxon_name.rb
@@ -247,6 +247,8 @@ class TaxonName < ApplicationRecord
   accepts_nested_attributes_for :taxon_name_authors, :taxon_name_author_roles, allow_destroy: true
   accepts_nested_attributes_for :taxon_name_classifications, allow_destroy: true, reject_if: proc { |attributes| attributes['type'].blank?  }

+  scope :order_by_nomenclatural_rank, -> { sort_with_case }
+
   scope :that_is_valid, -> { where('taxon_names.id = taxon_names.cached_valid_taxon_name_id') }
   scope :that_is_invalid, -> { where.not('taxon_names.id = taxon_names.cached_valid_taxon_name_id') }

@@ -261,6 +263,17 @@ class TaxonName < ApplicationRecord
       .order('taxon_name_hierarchies.generations DESC') # root is at index 0
   }

+  def self.sort_with_case
+    @@sort_with_case ||= (ICZN+ICN+ICNP+ICTV).each_with_index.inject(arel_table[:rank_class]) do |acc, (rank, index)|
+      acc = acc.when(rank).then(index)
+    end
+    order(@@sort_with_case.asc)
+  end
+
+  def self.sort_with_position
+    order(@@sort_with_position ||= "position(taxon_names.rank_class in '#{ICZN+ICN+ICNP+ICTV}')")
+  end
+
   # LEAVE UNORDERED, if you want order:
   #   .order('taxon_name_hierarchies.generations DESC')
   scope :self_and_ancestors_of, -> (taxon_name) {

Important

What should be the ordering of NULLs?

PS: When tried with things like Protonym.named('Jivarus').order_by_nomenclatural_rank it takes 0.6ms.

proceps commented 4 years ago

Use RANKS instead of ICZN+ICN+ICNP+ICTV, it also includes root. RANKS is an array, if you want reverse order, just reverse the array.

mjy commented 4 years ago

When tried with things like Protonym.named('Jivarus').order_by_nomenclatural_rank it takes 0.6ms.`

This is not the hard case, trye Protonym.where(project_id: 13).order_by_nomenclatural_rank, or Protonym.ancestors_and_descendants_of(123).order_by_nomenclatural_rank.

Combinations have no rank (only things that should be nil), that might be an issue for composing catalogs quickly, not sure.

mjy commented 4 years ago

@LocoDelAssembly thanks for the benchmark. Doesn't surprise me that CASE is much slower. I think we should mock the table too. If it's an order of magnitude faster as well that will pay off in the long run.

LocoDelAssembly commented 4 years ago

@LocoDelAssembly thanks for the benchmark. Doesn't surprise me that CASE is much slower. I think we should mock the table too. If it's an order of magnitude faster as well that will pay off in the long run.

Actually it is the opposite, CASE is a lot faster than position.

mjy commented 4 years ago

Meh, sorry, the thoughts did not align with the words, I'm still thinking JOIN vs. CASE, you're right!

LocoDelAssembly commented 4 years ago

[ ] Add a data migration that triggers the population of the table so that test databases include an index on build, not on spec run

See comments in code. This cannot be done since occasionally rspec will wipe out the test database. However, the table is populated only once at before(:suite).

[x] Add a deploy hook so that nomenclatural_rank_orders <- note plural is wiped and rebuilt on every deploy

Not exactly this, it is built every time TW starts with an upsert (insert non-existing records, update position if exist, all in a single SQL statement)

Before merging https://github.com/SpeciesFileGroup/taxonworks/pull/1867 perhaps would be best if you @mjy and @proceps add some code in that branch using these new scopes to confirm usability? I mean other than replacing those in-memory sorts with the scope without adding more queries to the chain. We should be absolutely sure this is actually helpful since we are adding a new model just to implement this scope.

mjy commented 4 years ago

@LocoDelAssembly What happens in upsert for rank_class names that no longer are presented (changed name)? Are they left in with the old index? If so this may cause problems? I suppose not as two values with the same position, one of which is never hit, shouldn't be a problem.

mjy commented 4 years ago

Agreed with your assessment about testing "IRL". I will re-write some fo the sorts on this branch to test.

LocoDelAssembly commented 4 years ago

@LocoDelAssembly What happens in upsert for rank_class names that no longer are presented (changed name)? Are they left in with the old index? If so this may cause problems? I suppose not as two values with the same position, one of which is never hit, shouldn't be a problem.

Rank classes that are no longer available are kept, which is preferable so rolling deployments won't disrupt live instances and the scope won't break if unused classes are left there (they will simply not join). Deletion should happen at migration time, right after data-migrating taxon_names.rank_class with NomenclaturalRankOrder.where.not(rank_class: RANKS).delete_all.

SpeciesFileGroup / taxonworks

Model - NomenclaturalRankOrder #1866

Important