Inefficient Tree JOINS causing Too Many Tables Error in Queries

[bronwyncombs]

Describe the bug
A clear and concise description of what the bug is.

Users are running into the following issue much more in v7.12.1+: #3369
For Auburn and PRI, the queries are for reports/labels and had worked previously. All the queries contain complex mappings.

KU Ento: (entomology.ku)
query includes host taxon relationship

Brzoska Tiger beetles.json | Specify 7 Crash Report - 2026-09-14T16_33_24.434Z.txt

dev comment:

There's 63 JOINs: two over the 61 limit.

44 of those JOINs are for Taxon alone.

Their Taxon has 22 ranks, so when they include a Taxon node/relationship in their query, Specify includes 22 JOINs: one for each rank of the tree.
The problem with this query for Ento is that they have two relationships to Taxon mapped:

CO -> determinations -> taxon
CO -> collectingEvent -> collectingEventAttribute -> hostTaxon

For each of the mapped relationships, Specify will add the 22 JOINs (one for each rank of their tree).
So there's 22 JOINs for determination and 22 for host taxon 😿

---

Auburn Inverts: (aumnh-inverts)

query includes Taxon>FullName and PreferredTaxon>FullName fields for each rank in tree

Label Generator.json | Specify 7 Crash Report - 2026-09-14T16_55_45.453Z.txt

user comment:

When I take out all the 'Taxon' fields, it still won't work. When I take out the 'Preferred Taxon' fields, it works. I like to keep the 'Preferred Taxon' information on labels because it helps people who use the collection find what they're looking for.

---

PRI: (pricollections)

query includes 7 chrono and litho strat fields with four joins each; also a few taxon>fullName and preferredTaxon>fullName fields

Label Query1.json | Specify 7 Crash Report - 2026-09-14T16_54_46.303Z.txt

Label Query1 in v7.12.0.7:

Label Query1 in v7.12.1.1:

Please fill out the following information manually:

• OS: macOS
• Browser: chrome
• Specify 7 Version: v7.12.1.1

Reported By
KU Entomology 1325
Auburn Inverts 809
PRI Collections 1321

Related

PR: #8510
Issue: #3369

[bronwyncombs]

@rijulpoudel is reviewing this issue to confirm whether there is a regression or if there was just a UX coincidence