<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)">
<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;}
@font-face
{font-family:"Calibri Light";
panose-1:2 15 3 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:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
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.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle20
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@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="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Danielle,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’ve had to do something similar recently, so I’ve pared down the query that I used to just grab a few bits of data in the Resource table and then combine that with any extent information associated with those Resource records. The output
is one row per Resource. If the Resource has multiple extent statements, then those multiple extent statements will be separated in the same cell with a “; “ (that’s the “group_concat” part in the SQL query). For readability, I’ll include a link to the query
that I used here: <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><a href="https://gist.github.com/fordmadox/ebb36454bcbcca6f0dfff5829c948506">https://gist.github.com/fordmadox/ebb36454bcbcca6f0dfff5829c948506</a><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">There are lots of other tables that you might want to join, though, so as Alex mentions the queries can become quite complex.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I hope that helps, but let us know either way!<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Mark<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b>From:</b> archivesspace_users_group-bounces@lyralists.lyrasis.org [mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org]
<b>On Behalf Of </b>Alexander Duryee<br>
<b>Sent:</b> Tuesday, 26 February, 2019 10:11 AM<br>
<b>To:</b> Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Subject:</b> Re: [Archivesspace_Users_Group] Extent Total<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Danielle,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">It's probably possible to do this via SQL queries, but it would require a fairly complex query to effectively rebuild the Resource record. It would likely be easier to do this via requests against the API, which can return a list of all
Resources in a repository; from there, you can query those Resources and extract their Extents.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">After logging in via the API, you can use this endpoint to get a list of all Resources in a given repository:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="https://[your-ASpace-API-address-here]/repositories/%5brepository_id%5d/resources?all_ids=true">https://[your-ASpace-API-address-here]/repositories/[repository_id]/resources?all_ids=true</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(removing the brackets when substituting in your API address and repo ID)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">This will return a list of Resource IDs. You can pass each one to the Resource endpoint:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="https://[your-ASpace-API-address-here]/repositories/%5brepository_id%5d/resources/%5bresource_id">https://[your-ASpace-API-address-here]/repositories/[repository_id]/resources/[resource_id</a>]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(again, removing brackets)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">This will return the full JSON representation of the Resource. The extent metadata lives in the 'extents' field; from there, you can extract the information that you need (e.g. the extent number, container summary, extent type, etc.).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Hope this helps - if you haven't worked with the API before, I'll be happy to send along tutorials/resources.<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">--Alex<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>
<p class="MsoNormal">On Mon, Feb 25, 2019 at 6:05 PM Danielle Butler <<a href="mailto:dbutler@cals.org" target="_blank">dbutler@cals.org</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">I am trying to query our MySQL database to get a list of resource identifiers with corresponding extent numbers.
I have been fighting with it for a while and I can get a list of extent numbers with no associated resource information with included extents for accessions. With no apparent way to discern which is which (I don’t want both accessions and resource extent data
because the data with be duplicative for processed collections). I can get a list of resources, but without extent info. Does anyone know of a way to get both out of the database?
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Thanks,</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif;color:#1F4E79">Danielle Butler, CA | Archivist</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Butler Center for Arkansas Studies | Central Arkansas Library System
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"><a href="https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.butlercenter.org%2F&data=02%7C01%7Cmark.custer%40yale.edu%7C393121aea1104e5be47c08d69bfc9b55%7Cdd8cbebb21394df8b4114e3e87abeb5c%7C0%7C1%7C636867906595412302&sdata=5p5HX4pwFb2Ycke66gKceSAcAXJfuapkZUJ0w8oWM9Q%3D&reserved=0" target="_blank">www.butlercenter.org</a>
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">100 Rock Street</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Little Rock, AR 72201</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">501-320-5724</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
<p class="MsoNormal">_______________________________________________<br>
Archivesspace_Users_Group mailing list<br>
<a href="mailto:Archivesspace_Users_Group@lyralists.lyrasis.org" target="_blank">Archivesspace_Users_Group@lyralists.lyrasis.org</a><br>
<a href="https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Flyralists.lyrasis.org%2Fmailman%2Flistinfo%2Farchivesspace_users_group&data=02%7C01%7Cmark.custer%40yale.edu%7C393121aea1104e5be47c08d69bfc9b55%7Cdd8cbebb21394df8b4114e3e87abeb5c%7C0%7C1%7C636867906595412302&sdata=e6YILSUwgmeO%2F%2BPUk%2BImcOJhIAD4Bsap5T%2FN4N2JseY%3D&reserved=0" target="_blank">http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group</a><o:p></o:p></p>
</blockquote>
</div>
<p class="MsoNormal"><br clear="all">
<br>
-- <o:p></o:p></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal">Alexander Duryee<o:p></o:p></p>
<div>
<p class="MsoNormal">Metadata Archivist<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">New York Public Library<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">(917)-229-9590<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="mailto:alexanderduryee@nypl.org" target="_blank">alexanderduryee@nypl.org</a><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>