<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">It’s possible, but I think to do it in one query requires recursive common table expressions – what version of MySQL/MariaDB are you on?
<br>
<br>
I wrote a thing that’s almost this except I was getting container info attached to the AOs rather than info from the AOs themselves, I can probably find and repurpose it, but it won’t run on MySQL prior to 8.0.<br>
<br>
<o:p></o:p></p>
<div>
<p class="MsoNormal">--<o:p></o:p></p>
<p class="MsoNormal">Dave Mayo (he/him)<o:p></o:p></p>
</div>
<p class="MsoNormal">Senior Digital Library Software Engineer<br>
Harvard University > HUIT > LTS<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:12.0pt;color:black">From: </span></b><span style="font-size:12.0pt;color:black"><archivesspace_users_group-bounces@lyralists.lyrasis.org> on behalf of Olivia S Solis <livsolis@utexas.edu><br>
<b>Reply-To: </b>Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Date: </b>Friday, September 20, 2019 at 9:48 AM<br>
<b>To: </b>Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Subject: </b>[Archivesspace_Users_Group] Database query to recreate inventory hierarchy?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Hi all, <o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I have been trying to figure out a database query that will list all the archival objects in a resource record's display_strings and their associated ids in the order that they appear in the resource record. This has proven more challenging
than I thought. I can't find the magical query that will leverage root_record_id (i.e. the resource record), parent_id, and position.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">If anyone else has figured this out, please let me know!<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">FYI, this is our use case, though I see many other uses for this query. We publish our EAD to TARO, a consortia of Texas archives. TARO's style is to only display the first container in a series of <c>s with the same container and omit
containers until there is a new container. A lot of our early EAD therefore has the first box in a series of <c>s with the same box. It looked fine in TARO! Not so much in ASpace. See screenshots below for an example:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">TARO:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black"><img width="624" height="251" style="width:6.5in;height:2.6145in" id="_x0000_i1026" src="https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U" alt="https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U"></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black"><br>
<br>
</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black">vs. EAD:</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black"><img width="624" height="363" style="width:6.5in;height:3.7812in" id="_x0000_i1025" src="https://lh3.googleusercontent.com/jqzk-JPuL819OETK2UZAJY_8ZFu9M56yeqYyydUOf7dF_YZJ90OHwdEjT6YU5637g6kEtAyIiv1zJpRpnh8mLRDkd8PjJKUr81iTwb08RfuagB3YqL0mFwfEQVUfCgtfIb2or8M" alt="https://lh3.googleusercontent.com/jqzk-JPuL819OETK2UZAJY_8ZFu9M56yeqYyydUOf7dF_YZJ90OHwdEjT6YU5637g6kEtAyIiv1zJpRpnh8mLRDkd8PjJKUr81iTwb08RfuagB3YqL0mFwfEQVUfCgtfIb2or8M"></span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black">This problem affects thousands of archival objects, so manually fixing this in the GUI would be extremely burdensome for us. I'd like to retrieve archival objects in the order they
appear in a resource record. In this particular case, I'd also join the boxes and their IDs so that I could either use fill down in OpenRefine or copy the box and paste is down in a spreadsheet. Then I'd use the API to add the missing container instances.
But the essential problem I am having is that I just can't figure out the database query to retrieve the AOs in order.</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black"><br>
<br>
</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black">Hopefully, this is clear. If anyone can help, let me know please!</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks!<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Olivia<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">-- <o:p></o:p></p>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">Olivia Solis, MSIS</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">Metadata Coordinator</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">Dolph Briscoe Center for American History</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">The University of Texas at Austin</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">2300 Red River St. Stop D1100</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">Austin TX, 78712-1426</span><span style="font-size:9.5pt"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:black">(512) 232-8013</span><span style="font-size:9.5pt;color:#888888"><o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>