Duplicate results for determiner querycombobox search

[emenslin]

Describe the bug
In the determination table if you search for a determiner the results multiply and show the same agent multiple times. This might happen in other places but I am not sure where

To Reproduce
Steps to reproduce the behavior:

1. Go to CO form
2. Add a determination
3. Start typing in the determiner field
4. See duplicate results

Expected behavior
Should not duplicate.

Screenshots

chrome_01-24_15.05.mp4

Please fill out the following information manually:

• OS: Windows 11
• Browser: Chrome
• Specify 7 Version: production
• Database Name: ojsmnh
• Collection name: fossil invertebrates
• User Name: spadmin
• URL: https://ojsmnh110242024production-production.test.specifysystems.org/specify/

[CarolineDenis]

NOTES:
When entering a value in the QCBX for determiner it calls this api => http://localhost/stored_query/ephemeral/
Payload is correct.
Response returns => {"results":[[1112,"G, D"],[1112,"G, D"],[1112,"G, D"],[1112,"G, D"],[1112,"G, D"],[1112,"G, D"],...]}

[acwhite211]

The query that is running in the QB API request is

SELECT
	agent.`AgentID`,
	agent.`LastName`
FROM
	agent,
	taxon
LEFT OUTER JOIN taxontreedef AS taxontreedef_1 ON
	taxontreedef_1.`TaxonTreeDefID` = taxon.`TaxonTreeDefID`
WHERE
	agent.`DivisionID` = 2
	AND agent.`LastName` LIKE 'ko%%'
	AND taxontreedef_1.`TaxonTreeDefID` = '5';

This is leading to a large amount of duplicate results being returned. This is due to the Cartesian product between the agent and taxon tables.

The query that we should want is something like this:

SELECT
    agent.`AgentID`,
    agent.`LastName`
FROM
    agent
WHERE
    agent.`DivisionID` = 2
    AND agent.`LastName` LIKE 'ko%%';

The simplest solution would be to remove the taxon tree filtering that the front-end adds to the API request. It doesn't seem like it is a needed filter for getting the determiner agents.

Here is the QB request being made

{
  "name": "Ephemeral QueryCBX query",
  "contextname": "Agent",
  "contexttableid": 5,
  "selectdistinct": false,
  "countonly": false,
  "specifyuser": "/api/specify/specifyuser/23/",
  "isfavorite": false,
  "fields": [
    {
      "isdisplay": false,
      "isnot": false,
      "startvalue": "ko",
      "operstart": 15,
      "sorttype": 0,
      "tablelist": "5",
      "stringid": "5.agent.lastName",
      "fieldname": "lastName",
      "isrelfld": false,
      "_tablename": "SpQueryField",
      "allownulls": null,
      "alwaysfilter": null,
      "columnalias": null,
      "contexttableident": null,
      "endvalue": null,
      "formatname": null,
      "isprompt": null,
      "isstrict": false,
      "operend": null,
      "position": 0,
      "timestampcreated": "2025-01-27",
      "timestampmodified": null,
      "version": 1,
      "createdbyagent": null,
      "modifiedbyagent": null,
      "query": null
    },
    {
      "isdisplay": true,
      "isnot": false,
      "startvalue": "",
      "operstart": 8,
      "sorttype": 1,
      "tablelist": "5",
      "stringid": "5.agent.",
      "fieldname": "",
      "isrelfld": false,
      "_tablename": "SpQueryField",
      "allownulls": null,
      "alwaysfilter": null,
      "columnalias": null,
      "contexttableident": null,
      "endvalue": null,
      "formatname": null,
      "isprompt": null,
      "isstrict": false,
      "operend": null,
      "position": 1,
      "timestampcreated": "2025-01-27",
      "timestampmodified": null,
      "version": 1,
      "createdbyagent": null,
      "modifiedbyagent": null,
      "query": null
    },
    {
      "isdisplay": false,
      "isnot": false,
      "startvalue": "5",
      "operstart": 1,
      "sorttype": 0,
      "tablelist": "4,76-definition",
      "stringid": "4,76-definition.taxontreedef.taxonTreeDefId",
      "fieldname": "taxonTreeDefId",
      "isrelfld": false,
      "_tablename": "SpQueryField",
      "allownulls": null,
      "alwaysfilter": null,
      "columnalias": null,
      "contexttableident": null,
      "endvalue": null,
      "formatname": null,
      "isprompt": null,
      "isstrict": false,
      "operend": null,
      "position": 2,
      "timestampcreated": "2025-01-27",
      "timestampmodified": null,
      "version": 1,
      "createdbyagent": null,
      "modifiedbyagent": null,
      "query": null
    }
  ],
  "_tablename": "SpQuery",
  "formatauditrecids": null,
  "ordinal": null,
  "remarks": null,
  "searchsynonymy": null,
  "smushed": null,
  "sqlstr": null,
  "timestampcreated": "2025-01-27",
  "timestampmodified": null,
  "version": 1,
  "createdbyagent": null,
  "modifiedbyagent": null,
  "limit": 1000
}

Removing the taxon field from the API request fixes this issue when getting the determiner agents.

[sharadsw]

Looks like the Taxon tree filter gets added to every Determination QCBX. I'll push a fix. Thanks @acwhite211!

[acwhite211]

If we need to filter agents by taxon tree, we could edit the query request to something like this:

{
  "name": "New Query",
  "contextname": "Agent",
  "contexttableid": 5,
  "selectdistinct": false,
  "countonly": false,
  "formatauditrecids": false,
  "specifyuser": "/api/specify/specifyuser/23/",
  "isfavorite": true,
  "ordinal": 32767,
  "fields": [
    {
      "tablelist": "5",
      "stringid": "5.agent.lastName",
      "fieldname": "lastName",
      "isrelfld": false,
      "sorttype": 0,
      "position": 0,
      "isdisplay": true,
      "operstart": 15,
      "startvalue": "ko",
      "isnot": false,
      "isstrict": false
    },
    {
      "tablelist": "5",
      "stringid": "5.agent.agentId",
      "fieldname": "agentId",
      "isrelfld": false,
      "sorttype": 0,
      "position": 1,
      "isdisplay": true,
      "operstart": 8,
      "startvalue": "",
      "isnot": false,
      "isstrict": false
    },
    {
      "tablelist": "5,96,26-disciplines,76",
      "stringid": "5,96,26-disciplines,76.taxontreedef.taxonTreeDefId",
      "fieldname": "taxonTreeDefId",
      "isrelfld": false,
      "sorttype": 0,
      "position": 2,
      "isdisplay": true,
      "operstart": 1,
      "startvalue": "5",
      "isnot": false,
      "isstrict": false
    }
  ],
  "_tablename": "SpQuery",
  "remarks": null,
  "searchsynonymy": null,
  "smushed": null,
  "sqlstr": null,
  "timestampcreated": "2025-01-27",
  "timestampmodified": null,
  "version": 1,
  "createdbyagent": null,
  "modifiedbyagent": null,
  "limit": 40,
  "offset": 8
}