Search Synonyms only works on first tree in Query

[melton-jason]

Describe the bug
The Specify 7 "Search Synonyms" functionality in the QueryBuilder (see #5137) is limited to a single tree.
Specifically, Specify 7 uses the first tree present in the Query to determine which tree it searches synonyms on.
This is different in Specify 6, which searched on all trees when "Search Synonyms" was checked.

Consider the following video, which demonstrates the difference in behavior between Specify 6 and Specify 7:

Screen.Recording.2026-09-21.at.8.24.25.AM.mov

Test Query.json

In the video, the CollectingEvent -> Locality -> Geography- whose value is Angwilla-is a synonym of Anguilla and Determination -> Taxon-whose value is Acipenser baernotaccepted- is a synonym of Acipenser baerii.

So the first query field, Locality -> Geography -> FullName -> Contains -> "ui", should only match on the synonym of the Locality's Geography rather than the Locality's directly referenced Geography.
Likewise, the second field in the Query, Determination -> Taxon -> FullName -> Contains -> "ii" should only match on the synonym of the Determination's Taxon rather than the Determination's directly referenced Taxon.

Thus the Query will only return the correct CollectionObject when both trees synonyms are being searched in the Query (as both filters should only match on the tree records synonym).

The first tree field in the Query is on the Geography field, so Specify 7 will only (incorrectly) search on the Geography tree when looking at synonyms.

To Reproduce
Steps to reproduce the behavior:

1. Find, edit, or make a record that has a relationship to more than one tree

• CollectionObject is an example as there are:
  • determinations -> Taxon
  • collectingEvent -> locality -> Geography
  • preparations -> Storage
  • collectingEvent -> (locality -> ) paleoContext -> TectonicUnit
• CollectingEvent or Locality would also work as there are relationships to TectonicUnit and Geography

2. For at least two of the tree relationships, ensure the directly referenced tree record has a synonym

• e.g., As by the example, make sure that both of the Determinations -> Taxon and Locality -> Geography records are synonymized

3. In the QueryBuilder, add at least two of the relationships for which the record is referencing a synonymized tree record

• e.g., Locality -> Geography and Determinations -> Taxon (NOT preferred taxon)

4. Modify any but the first of the previously added Query filters such that they should return the accepted record, and not the synonymized record

• e.g., If Geography is first in the Query and Taxon follows, make sure the Taxon filter should only return the accepted record (not the direct Taxon record the Determination is referencing).

5. See that Specify 7 does not return the right record when the Query is ran

Expected behavior
Specify 7 should follow the behavior from Specify 6 and search on all included trees synonyms when "Search Synonyms" is enabled, or make clear when/where it differs from Specify 6 with features of the same name.

Please fill out the following information manually:

• OS: macOS Tahoe 26.3.1 (a) Apple M3 PRO
• Browser: Google Chrome 153.0.8010.53 (Official Build) (arm64)
• Specify 7 Version: v7.12.1.1 and main (2c3012d)

Additional context

For the above example Query, below is the resulting SQL that Specify 7 sends to the database:

sp7_search_synonym.sql

Note the "Search Synonymy" parts of the Query (which are limited to Geography):

WITH target_taxon AS (
    SELECT geography_1.`GeographyID` AS `TaxonID`,
        geography_1.`AcceptedID` AS `AcceptedID`
    FROM collectionobject
        LEFT OUTER JOIN collectingevent AS collectingevent_1 ON collectingevent_1.`CollectingEventID` = collectionobject.`CollectingEventID`
        LEFT OUTER JOIN locality AS locality_1 ON locality_1.`LocalityID` = collectingevent_1.`LocalityID`
        LEFT OUTER JOIN geography AS geography_1 ON geography_1.`GeographyID` = locality_1.`GeographyID`
        LEFT OUTER JOIN determination AS determination_1 ON collectionobject.`CollectionObjectID` = determination_1.`CollectionObjectID`
        LEFT OUTER JOIN taxon AS taxon_1 ON taxon_1.`TaxonID` = determination_1.`TaxonID`
    WHERE collectionobject.`CollectionID` = 4
        AND (
            geography_1.`FullName` LIKE concat(concat('%%', 'ui'), '%%')
        )
        AND (
            taxon_1.`FullName` LIKE concat(concat('%%', 'ii'), '%%')
        )
)
...
WHERE geography_1.`GeographyID` IN (
        SELECT ids.id
        FROM (
                SELECT accepted_roots.id AS id
                FROM (
                        SELECT coalesce(
                                target_taxon.`AcceptedID`,
                                target_taxon.`TaxonID`
                            ) AS id
                        FROM target_taxon
                    ) AS accepted_roots
                UNION
                SELECT geography.`GeographyID` AS id
                FROM geography
                WHERE geography.`AcceptedID` IN (
                        SELECT accepted_roots.id
                        FROM (
                                SELECT coalesce(
                                        target_taxon.`AcceptedID`,
                                        target_taxon.`TaxonID`
                                    ) AS id
                                FROM target_taxon
                            ) AS accepted_roots
                    )
            ) AS ids
    )

In the code, this restriction on a single tree seems intentional.
We explicitly stop at the first tree field in the Query:

specify7/specifyweb/backend/stored_queries/execution.py

Lines 1128 to 1129 in 227f23a

	if props.search_synonymy:
	synonymy_table = _pick_synonymy_table(field_specs, base_table)

specify7/specifyweb/backend/stored_queries/execution.py

Lines 89 to 95 in 227f23a

	for field_spec in field_specs:
	if field_spec.fieldspec.contains_tree_rank():
	return field_spec.fieldspec.table
	for field_spec in field_specs:
	if is_tree_table(field_spec.fieldspec.table):
	return field_spec.fieldspec.table

And the Search Synonyms feature in the backend code is restricted to just a single tree table.
Though I assume this was an oversight when developing the feature (i.e., in #5137) and not intentional.