Collection number and name mapping

[Benneth]

Please which table in dspace postgreqsl can i find the mapping of all collection names and their numbers.

ie Collection number Collection Name

              -                               -

               -                              -

               -                              -

[Mark H. Wood]

Collection names are metadata and are stored in the 'metadatavalue'
table.

First, you need to find the 'metadata_field_id' for the field 'dc.title'
in your repository. (It can differ between repo.s.) I've bundled it
all together:

SELECT v.dspace_object_id AS "Collection number",
v.text_value AS "Collection Name"
FROM metadatavalue AS v
JOIN metadatafieldregistry AS f USING(metadata_field_id)
JOIN metadataschemaregistry AS s USING(metadata_schema_id)
WHERE s.short_id = 'dc'
AND f.element = 'title' AND f.qualifier IS NULL;

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu

[Benneth Obilor]

Thank you Mark.

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/ZL_vBrmKUfFptZtz%40IUPUI.Edu.

[Mark H. Wood]

On Tue, Jul 25, 2023 at 12:27:21PM -0400, Benneth Obilor wrote:
> Thank you Mark.

Don't thank me too soon: I forgot to filter out other types of
objects. Try this instead:

SELECT v.dspace_object_id AS "Collection number",
v.text_value AS "Collection Name"
FROM metadatavalue AS v
JOIN metadatafieldregistry AS f USING(metadata_field_id)
JOIN metadataschemaregistry AS s USING(metadata_schema_id)

JOIN collection AS c ON c.uuid = v.dspace_object_id

> To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/CAEA0NYnW4OtstOq7FRwjA02B9tX-_gJyAmU1Zj1EFLuch-BgPQ%40mail.gmail.com.