<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<p>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.</p>
<p>Andrew.</p>
<p><br>
</p>
<div class="moz-cite-prefix">On 19/04/2022 15:38, Olivia S Solis
wrote:<br>
</div>
<blockquote type="cite" cite="mid:CAKu+i=18dXa2nOpoNP6A=1jOYcTSuDyeDebExFivs7gQyTq6qQ@mail.gmail.com">
<div dir="ltr">Hello all,
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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):</div>
<div><br>
</div>
<div><span id="gmail-docs-internal-guid-ab0eeb4c-7fff-0e73-2539-190641332320">
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">SELECT DISTINCT <a href="http://rr.id" moz-do-not-send="true">rr.id</a>, rr.title, t.indicator, r.top_container_id, t.barcode, l.title</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">FROM instance as i</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN sub_container as s</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://i.id" moz-do-not-send="true">i.id</a> = s.instance_id</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN archival_object as a</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://a.id" moz-do-not-send="true">a.id</a> = i.archival_object_id</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN top_container_link_rlshp as r</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://s.id" moz-do-not-send="true">s.id</a> = r.sub_container_id</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN top_container as t</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://t.id" moz-do-not-send="true">t.id</a> = r.top_container_id</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">LEFT JOIN resource as rr</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://rr.id" moz-do-not-send="true">rr.id</a> = a.root_record_id</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN top_container_housed_at_rlshp as tcha</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON tcha.top_container_id = <a href="http://t.id" moz-do-not-send="true">t.id</a></span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">JOIN location as l</span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:"Courier New";color:rgb(0,0,0);background-color:transparent;font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">ON <a href="http://l.id" moz-do-not-send="true">l.id</a> = tcha.location_id </span></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="background-color:transparent;color:rgb(0,0,0);font-family:"Courier New";font-size:11pt;white-space:pre-wrap">WHERE l.building LIKE "SRH2"</span><br>
</p>
</span><br>
</div>
<div>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:</div>
<div><br>
</div>
<div><span style="color:rgb(0,0,0);font-family:"Courier New";font-size:14.6667px;white-space:pre-wrap">SELECT id, resource_id, archival_object_id, notes FROM note WHERE notes LIKE '%local_access_restriction_type":["%';</span><br>
</div>
<div><br>
</div>
<div>Any help would be appreciated. Thank you!</div>
<div><br>
</div>
<div>-Olivia</div>
<div><br>
</div>
<div>-- <br>
<div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div style="font-size:12.8px"><font color="#000000">Olivia Solis, MSIS (she/her)</font></div>
<div style="font-size:12.8px"><font color="#000000">Metadata Coordinator</font></div>
<div style="font-size:12.8px"><font color="#000000">Dolph Briscoe Center for
American History</font></div>
<div style="font-size:12.8px"><font color="#000000">The University of Texas at
Austin</font></div>
<div style="font-size:12.8px"><font color="#000000">2300 Red River St. Stop D1100</font></div>
<div style="font-size:12.8px"><font color="#000000">Austin TX, 78712-1426</font></div>
<div style="color:rgb(136,136,136);font-size:12.8px"><span style="color:rgb(0,0,0);font-size:12.8px">(512) 232-8013</span></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
Archivesspace_Users_Group mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Archivesspace_Users_Group@lyralists.lyrasis.org">Archivesspace_Users_Group@lyralists.lyrasis.org</a>
<a class="moz-txt-link-freetext" href="http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group">http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group</a>
</pre>
</blockquote>
</body>
</html>