If you need to ascend the hierarchy of archival objects within
collections, from the ones that link to containers, to the ones with the
access restrictions, then you are probably going to need to write a
stored procedure.
Andrew.
On 19/04/2022 15:38, Olivia S Solis wrote:
Hello all,
I'm spending an inordinately long time trying to form a query to
retrieve all boxes attached to restricted archival objects in ASpace.
I'm hoping someone in the group with more advanced database skills
than I have can help me out.
We want to identify any box in a certain building that has restricted
materials and the kind of local access restriction it has. Since we
use local access restrictions, the AO that is linked to the box may
not have the local access restriction applied through the
accessrestrict note, but a parent AO will. That parent may or may not
be its immediate parent AO. We only care about the collection title in
the box, not the AO title.
I've come up with this query, which is already a mouthful, to retrieve
all the boxes + their collections in the building I want (SRH2):
SELECT DISTINCT rr.id <http://rr.id>, rr.title, t.indicator,
r.top_container_id, t.barcode, l.title
FROM instance as i
JOIN sub_container as s
ON i.id <http://i.id> = s.instance_id
JOIN archival_object as a
ON a.id <http://a.id> = i.archival_object_id
JOIN top_container_link_rlshp as r
ON s.id <http://s.id> = r.sub_container_id
JOIN top_container as t
ON t.id <http://t.id> = r.top_container_id
LEFT JOIN resource as rr
ON rr.id <http://rr.id> = a.root_record_id
JOIN top_container_housed_at_rlshp as tcha
ON tcha.top_container_id = t.id <http://t.id>
JOIN location as l
ON l.id <http://l.id> = tcha.location_id
WHERE l.building LIKE "SRH2"
On top of this I want the archival objects in the query to reflect
only AOs that have a local access restriction or who have an immediate
or distant parent AO with one. This is the query I have that will ID
AOs with a local access restriction, which I realize I'll have to
extract from the JSON that is in the notes column of the note table:
SELECT id, resource_id, archival_object_id, notes FROM note WHERE
notes LIKE '%local_access_restriction_type":["%';
Any help would be appreciated. Thank you!
-Olivia
--
Olivia Solis, MSIS (she/her)
Metadata Coordinator
Dolph Briscoe Center for American History
The University of Texas at Austin
2300 Red River St. Stop D1100
Austin TX, 78712-1426
(512) 232-8013
_______________________________________________
Archivesspace_Users_Group mailing list
[email protected]
http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
_______________________________________________
Archivesspace_Users_Group mailing list
[email protected]
http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
