Record Queries can return invalid results if expedition name/id the same

[jdeck88]

Create two projects. Create an expedition with the same name within each project. Create an ID with the same name within each of the expeditions. A record query will return all results matching the expedition name and sample id name:

The SQL for a query on "JD1" within a particular expedition is:

SELECT Sample.data AS "Sample_data", Sample_entity_identifiers.identifier AS "Sample_rootIdentifier", expeditions.expedition_code AS "expeditionCode", expeditions.project_id AS "projectId", Event.data AS "Event_data", Event_entity_identifiers.identifier AS "Event_rootIdentifier", Tissue.data AS "Tissue_data", Tissue_entity_identifiers.identifier AS "Tissue_rootIdentifier", Sample_Photo.data AS "Sample_Photo_data", Sample_Photo_entity_identifiers.identifier AS "Sample_Photo_rootIdentifier" FROM network_1.Sample AS Sample JOIN expeditions ON expeditions.id = Sample.expedition_id JOIN network_1.Event AS Event ON Event.local_identifier = Sample.parent_identifier and Event.expedition_id = Sample.expedition_id LEFT JOIN network_1.Tissue AS Tissue ON Tissue.parent_identifier = Sample.local_identifier and Tissue.expedition_id = Sample.expedition_id LEFT JOIN network_1.Sample_Photo AS Sample_Photo ON Sample_Photo.parent_identifier = Sample.local_identifier and Sample_Photo.expedition_id = Sample.expedition_id LEFT JOIN entity_identifiers AS Sample_entity_identifiers ON Sample_entity_identifiers.expedition_id = Sample.expedition_id and Sample_entity_identifiers.concept_alias = 'Sample' LEFT JOIN entity_identifiers AS Event_entity_identifiers ON Event_entity_identifiers.expedition_id = Event.expedition_id and Event_entity_identifiers.concept_alias = 'Event' LEFT JOIN entity_identifiers AS Tissue_entity_identifiers ON Tissue_entity_identifiers.expedition_id = Tissue.expedition_id and Tissue_entity_identifiers.concept_alias = 'Tissue' LEFT JOIN entity_identifiers AS Sample_Photo_entity_identifiers ON Sample_Photo_entity_identifiers.expedition_id = Sample_Photo.expedition_id and Sample_Photo_entity_identifiers.concept_alias = 'Sample_Photo' WHERE expeditions.expedition_code = 'test' AND Sample.local_identifier = 'jd1' ORDER BY Sample.local_identifier, Sample.expedition_id

If there are two expeditions named "test" this will return all results.... A query on a specific expedition record, like: https://geome-db.org/record/ark:~2F21547~2FBnx2jd1

ends up returning the results for https://geome-db.org/record/ark:/21547/BkH2jd1 --- i'm assuming the record interface just picks the first returned record result if there is more than one result.

The solution appears to either query on the expedition identifier instead of the code OR add the project ID or code to the query. I think querying on the expedition identifier is the better solution....

[ewingrj]

fixed in 0b6b116b045537318e650333fb613be9f23c671d