[Archivesspace_Users_Group] Database query to recreate inventory hierarchy?

[Mayo, Dave]

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?

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.

--
Dave Mayo (he/him)
Senior Digital Library Software Engineer
Harvard University > HUIT > LTS

From: <archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of Olivia S Solis <livsolis at utexas.edu>
Reply-To: Archivesspace Users Group <archivesspace_users_group at lyralists.lyrasis.org>
Date: Friday, September 20, 2019 at 9:48 AM
To: Archivesspace Users Group <archivesspace_users_group at lyralists.lyrasis.org>
Subject: [Archivesspace_Users_Group] Database query to recreate inventory hierarchy?

Hi all,

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.

If anyone else has figured this out, please let me know!

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:

TARO:
[https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U]


vs. EAD:
[https://lh3.googleusercontent.com/jqzk-JPuL819OETK2UZAJY_8ZFu9M56yeqYyydUOf7dF_YZJ90OHwdEjT6YU5637g6kEtAyIiv1zJpRpnh8mLRDkd8PjJKUr81iTwb08RfuagB3YqL0mFwfEQVUfCgtfIb2or8M]
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.


Hopefully, this is clear. If anyone can help, let me know please!

Thanks!
Olivia

--
Olivia Solis, MSIS
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20190920/65388e5c/attachment.html>