Skip to first unread message
Miquel Àngel Serra
Jul 18, 2022, 4:48:05 AM7/18/22
to AtoM Users
Hello, my organization has asked me a report to know how many digital objects were uploaded during 2021 for each institution in our AtoM installation. I've seen that the system actually shows the date for every digital object but it doesn't appear in the reports that I've tried so far. Is there any way to get this kind of report from the web?
If not, I've tried to navigate inside the database via MySQL but I haven't been able to found the correct query to get this. So far, I've seen that the upload date is stored in the OBJECT table that is related with DIGITAL OBJECT via id. But how can I know to which institution a certain object belongs? The INFORMATION_OBJECT table has a column named "repository_id" but it's only used in certain objects (I think at fonds level) so I'm not sure how to list all the Items belonging to a given institution that have a Digital object linked to their descriptions. Could you help me to formulate the query to get the data I need?
Thanks in advance!
Dan Gillean
Jul 18, 2022, 1:04:40 PM7/18/22
to ICA-AtoM Users
Hi Miquel,
One of our developers has attempted to craft a query for you - apparently it was pretty difficult to assemble!
The following query uses Common Table Expressions (CTE), a feature only available in MySQL 8.0 and later - so this will only work for AtoM 2.6 or later. I have not personally tested this query - while it is a select and not something that should alter your data, we still encourage you to make backups before accessing the MySQL command prompt, and proceed at your own risk!
Try the following:
WITH RECURSIVE cte AS
(
SELECT io1.id, io1.parent_id, io1.repository_id, slug.slug
FROM information_object io1
JOIN slug ON io1.repository_id=slug.object_id
UNION ALL
SELECT io2.id, io2.parent_id, io2.repository_id, cte.slug
FROM information_object io2
JOIN cte ON cte.id=io2.parent_id
AND io2.repository_id IS NULL
)
SELECT cte.slug AS institution_slug, COUNT(*) AS digital_object_count
FROM cte
JOIN digital_object ON cte.id=digital_object.object_id
JOIN object ON digital_object.id=object.id
WHERE object.created_at BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY cte.slug;
(
SELECT io1.id, io1.parent_id, io1.repository_id, slug.slug
FROM information_object io1
JOIN slug ON io1.repository_id=slug.object_id
UNION ALL
SELECT io2.id, io2.parent_id, io2.repository_id, cte.slug
FROM information_object io2
JOIN cte ON cte.id=io2.parent_id
AND io2.repository_id IS NULL
)
SELECT cte.slug AS institution_slug, COUNT(*) AS digital_object_count
FROM cte
JOIN digital_object ON cte.id=digital_object.object_id
JOIN object ON digital_object.id=object.id
WHERE object.created_at BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY cte.slug;
This should output something like the following:
institution_slug,digital_object_count
art-gallery-of-ontario,1
wilfrid-laurier-university-archives,25
city-of-greater-sudbury-archives,115
artefactual-archives,47
art-gallery-of-ontario,1
wilfrid-laurier-university-archives,25
city-of-greater-sudbury-archives,115
artefactual-archives,47
etc....
Hope that helps!
he / him
--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/033abed1-eedc-4d99-9ce1-509530df2f78n%40googlegroups.com.
Miquel Àngel Serra
Jul 25, 2022, 5:22:25 AM7/25/22
to AtoM Users
Hello Dan, thank you very much for your answer. It actually helped me!.
And thanks to the developer that made the query as well, now that I see it I'm pretty sure I would never have found the solution by myself.
I don't know if thats something that you have planned to add in future updates, but I think that having reports of digital objects/archival descriptions created by year could be of interest for other institutions as well.
I don't know if thats something that you have planned to add in future updates, but I think that having reports of digital objects/archival descriptions created by year could be of interest for other institutions as well.
In any case, thanks again for your help!
El dia dilluns, 18 de juliol de 2022 a les 19:04:40 UTC+2, Dan Gillean va escriure:
Reply all
Reply to author
Forward
0 new messages