[Archivesspace_Users_Group] Database query to recreate inventory hierarchy?

[Olivia S Solis]

Hi Dave,
Thanks for your response! Looks like we're running MariaDB 5.5.60.

On Fri, Sep 20, 2019 at 8:58 AM Mayo, Dave <dave_mayo at harvard.edu> wrote:

> 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:
>
> [image:
> https://lh4.googleusercontent.com/5P7wiGVFjswktn_sCsZ6TLIBzFP5jt29GKSGis3r52QMUUhNHlu0VKoNWOUwAerE-D4Qx4x5kY3tSG7zkENI4qIG3bdsrWzITbKCwBB4qBvmisiumoNv4nCe1IWO9uMkRq_3P7U]
>
>
>
> vs. EAD:
>
> [image:
> 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
> _______________________________________________
> Archivesspace_Users_Group mailing list
> Archivesspace_Users_Group at lyralists.lyrasis.org
> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
>


-- 
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/718c104f/attachment.html>