Add support for querying on any tree table field from a chosen rank

[grantfitzsimmons]

Is your feature request related to a problem? Please describe.
Currently in Specify 6 and 7 there is no ability in the query builder to search on any arbitrary field in the tree table once you select a specific rank.

Current Behavior

At (any rank)

See that all fields and relationships are exposed in the list of fields to choose from

The stringids are constructed as you'd expect:

1,9-determinations,4-preferredTaxon.taxon.fullName

At {Rank} level

Only author and fullName can be chosen from the list of fields once you select a level in the tree.

The stringids associated with these rank-specific query items are unusual. They are formatted as follows:

1,9-determinations,4-preferredTaxon.taxon.Kingdom
1,9-determinations,4-preferredTaxon.taxon.Kingdom Author

It looks like there is special logic handling these treeNodeCols when constructing a string ID for these items.

Describe the solution you'd like

Currently, users can only choose two fields at any given tree rank:

• Author
• Full Name

Users should be able to select any rank in a tree (e.g. Family, Genus, Species, etc.) and select any field in the tree table at that level.

Instead of seeing only these two fields:

You should see this:

The list of fields should be comprehensive, including Author, Common Name, Full Name, GUID, Is Preferred, Name, Protected Status, Remarks, Source.

Reported By
Louisiana State University Museum of Natural Science, University of Washington, University of Florida, CSIRO, University of Kansas, many others

---

Initially reported in #2364

@grantfitzsimmons:

Currently in Specify 7 and 6 you are restricted to only seeing the taxon full name and author for each rank. This restriction causes issues when it comes to running a query from the collection object or creating reports and labels where you want to reference a specific field for a specific rank.

In this case, I am reworking the University of Washington's labels to reference the family number, stored in a text field at the family level.

There is no way to reference this number without using relationships through the "Parent Taxon" several times and hoping that the parent is the family. For their database, all specimen records are determined to species at the moment, but this is going to change in the future and the flexibility is necessary. I have to stack parent relationships to ensure that it will not be missed.

@maxpatiiuk said the following in this comment:

From what I remember Ben told me, this restriction was put in place artificially just to match the limitations of specify 6 (yeah, this is from the dark dark days where sp7 was limited just to not outcompete specify 6)

Here is the code for that:

specify7/specifyweb/stored_queries/queryfieldspec.py

Lines 27 to 30 in f4cd803

	TAXON_FIELD_RE = re.compile(r'(.*) ((Author)|(GroupNumber))$')
	# Look to see if we are dealing with a tree node ID.
	TREE_ID_FIELD_RE = re.compile(r'(.*) (ID)$')

(it currently allows only Author, ID and Group Number fields - and full name implicitly if field name was not provided). Modifying that code and lifting the restriction on the front-end should fix this

Last march I asked for that code to be removed but no forward movement has occurred since that discussion.

Issues to Resolve:

• Add support for 'Group Number' in Taxon queries #4723
• Add support for querying on any tree table field from a chosen rank

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/querying-taxon-group-number/1198/2

[melton-jason]

Duplicate of #2364?

[grantfitzsimmons]

@melton-jason Yes. You have no idea how many issue titles I searched on... going to merge the details of that into this one rather than the other way around.

[grantfitzsimmons]

This request has been approved by Taylor at the Florida Museum of Natural History. Here is the additional clarification of the issue they are facing:

I can show you the differences we’re experiencing more concisely:

In Sp6, this query, when filtered on Family Group Number:

Yields:

That same query in Sp7 won’t run, as the mapping doesn’t translate and results in an showstopper error. The Not Mapped is the Family Group Number in sp6:

So, if I eliminate that field from the query, and then add the Any -> Group Number, I get no results:

So, it seems that when you filter using Any, it returns results for, in this case, the Group Number on Family, only results for determinations that would be made to the Family that had the Group Number, not a child of it, like a determination made to a child Genus->Species.

So, making Group Number queryable directly on the Family would solve this. The issue you submitted encapsulates this and I thank you greatly for it.

[grantfitzsimmons]

The same issue reported by Taylor is causing an issue with this query defined in Specify 6:

{
  "tableList": "1,9-determinations,4",
  "stringId": "1,9-determinations,4.taxon.Family groupNumber",
  "fieldName": "Family groupNumber",
  "isRelFld": false,
  "sortType": 0,
  "position": 2,
  "isDisplay": true,
  "operStart": 11,
  "startValue": "",
  "isNot": false
}

As described in #4697 (comment), you can only search on "Author" and "Full Name" at a given rank, therefore Specify 7 is unable to successfully execute the query. If we extended support to searching on groupNumber, that would solve this case, but we should do it more generalized if possible.

Specify 7 Crash Report - 2024-03-27T14_37_45.508Z.txt

[grantfitzsimmons]

It looks like Name, Author, and Group Number are explicitly defined as special tree fields in Specify 6 here:

https://github.com/specify/specify6/blob/945d28f00288d2d64979848fb2fb63fda34bdd1b/src/edu/ku/brc/specify/tasks/QueryTask.java#L1978-L1993