Querying Locality Detail via Collection Object

[phb]

Specify 7 Version:
v7.11.3
Git SHA:
dcecec6c3ada3c8bdb40705e08e82170cc3a5f71
(opens in a new tab)
Build Date:
3 weeks ago
Specify 6 Version:
6.8.03
Database Version:
6.8.03
DB Schema Version:
2.10
Database Name:
beaty
DB Creation Date:
Thursday, March 23, 2023 at 10:59:16 AM
Institution:
Beaty Biodiversity Museum
Discipline:
Botany 1
Collection:
Vascular Plants
ISA Number:
N/A
Browser:
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/142.0.0.0 Safari/537.36

An issue brought up by one of my collection curators is that they cannot query Island or Island Group (both string fields) from Locality Detail via a query with Collection Object as the base table. I tested, and it is indeed not possible to query a Collection Object that has a particular string in locality detail, however, if Locality Detail is the base table, it is possible to query those fields. It is also possible to query via Collecting Event, so it’s a bit unclear why one level of indirection is possible, but not two.

Although the workaround is possible, this breaks the mental model that the Collection Object query will find all collection objects with a particular property. Further, it is not clear how one might go about getting a full list of Collection Object records associated with the Island or Island Group string from Location Detail.

[nfshoobs]

Paul,
Interesting. Could you export the query and post it?
We routinely query on localityDetail through the CO table (for example, https://invertebrates.osu.edu/specify/query/159/), and this query setup also works in sp7demofish, so it could be a problem with your database specifically.
Are the collecting events embedded or unembedded? Could have something to do with that, too. Ours are embedded.

[phb]

@nfshoobs sure, I’ve attached an example query here. When you say “embedded or unembedded” what do you mean? I haven’t encountered the term before.

Example Island Group Query.json (2.3 KB)

[phb]

Just looked up the discussion here on the forum on embedding, and I don’t know how to check in a clear way. Regardless, I think the expected behaviour should be the same, even if the underlying mechanism is different.

[nfshoobs]

huh! That’s weird. What exactly happens when you run this query? It completes and shows 0 results? I think it is safe to say that this is a problem with your database in particular, and is not a generalized problem. The json you uploaded works for me.

@Grant or @bhumikagupta will have to figure this one out.

[phb]

Yes, exactly, completes with 0 results. Good to know we’re special

[nfshoobs]

One other thing that comes to mind – are you 100% certain that you’re signed in to the same collection as the one which has the COs linked to those localitydetail fields?
I find it’s easy to forget that I’m switched into a different collection view sometimes, which would produce the results you’re seeing (no results in CO, but queries of related tables work). This would only be true in collections with embedded collectingEvent records, though.

[phb]

Yup, 100% certain, both in Mammals collection

[Grant]

Hi @phb,

Thanks for reaching out!

I am able to build a query that returns results when searching via ‘Collection Object → Collecting Event → Locality → Locality Detail’ in the Query Builder in a copy of your database.

For example, you can see this query here (Locality Detail Test) where I searched for any cases where “Locality Detail → Island = Mo'orea” in the Algae collection and 11 results are returned.

I believe the issue may be a matter of how scoping impacts query behavior. Your database follows this institutional scoping hierarchy:

Divisions	Disciplines	Collection Name
Natural History	Botany 1	Vascular Plants
Natural History	Botany 1	Algae
Natural History	Botany 2	Bryophytes
Natural History	Botany 2	Lichen
Natural History	Botany 2	Fungi
Natural History	Tetrapods	Birds
Natural History	Tetrapods	Herpetology
Natural History	Tetrapods	Mammals
Natural History	Marine Invertebrates	Marine Invertebrates
Natural History	Fishes	Fishes
Natural History	Paleontology	Paleontology
Natural History	Entomology	Entomology
Teaching and learning	Teaching and Learning	Teaching and Learning

Locality, Collecting Event, and Locality Detail are scoped to the Discipline level, meaning querying any of these tables directly will return all records that exist within that discipline.

Querying for all cases where ‘Island’ is not empty from one of those base tables will display results, even if no Collection Objects in the ‘Mammals’ collection are connected.

I created the Locality with Detail query to demonstrate this behavior, showing the collection name of the associated Collection Object with each Locality (via the Collecting Event relationship)

You can test this by running the Mammals Island Query that I created, showing this result when running that query in the ‘Mammals’ collection:

I hope this clarifies things a bit! If you start from the Collecting Event or Locality side of things, you can return all records associated with a particular island or island group across collections, but querying Collection Object simply returns records from the current collection.

[phb]

Thanks for the explanation