Stacy,
I just realized that I tested the query in my DSpace 5 instance. Try the
following instead.
with recursive r_comm2coll as (
select
community_id,
collection_id
from
community2collection
union
select
cm2cm.parent_comm_id as community_id,
r.collection_id
from
r_comm2coll r
inner join
community2community cm2cm
on
cm2cm.child_comm_id = r.community_id
)
select distinct
commh.handle as CommHandle,
collh.handle as CollHandle,
(
select text_value
from metadatavalue mv
where
mv.dspace_object_id=r_comm2coll.community_id
and metadata_field_id = (
select metadata_field_id
from metadatafieldregistry where element='title' and qualifier is null
)
) as CommName,
(
select text_value
from metadatavalue mv
where
mv.dspace_object_id=r_comm2coll.collection_id
and metadata_field_id = (
select metadata_field_id
from metadatafieldregistry where element='title' and qualifier is null
)
) as CollName
from
handle commh
inner join r_comm2coll
on r_comm2coll.community_id=commh.resource_id
inner join handle collh
on r_comm2coll.collection_id=collh.resource_id
order by CommHandle,CollHandle
On Fri, Nov 17, 2017 at 12:12 PM, Stacy Pennington <[email protected]> wrote:
> Terry,
>
> Thanks for the excellent advice regarding using /rest/hierarchy. I was
> able to get a (long) list that could be easily cleaned up in Excel and used
> to track the flattening of the hierarchy.
>
> I only started using the REST API a few months ago, but I'm really excited
> about what it can do. We're successfully using it with Omeka S to feed
> items in DSpace communities and collections to Omeka S. It is just so
> handy, especially with authenticated REST use in version 6.
>
> As for the query you sent, I get this error in PostgreSQL when I run it:
>
> ERROR: column mv.resource_id does not exist
> LINE 26: mv.resource_id=commh.resource_id and mv.resource_type_id=...
> ^
> ********** Error **********
>
> ERROR: column mv.resource_id does not exist
> SQL state: 42703
> Character: 478
>
> It looks like the metadatavalue in my 6.2 doesn't have a resource_id
> column (or resource_type column, for that matter).
>
> Do you think something is wrong with my metadatavalue table? Or is your
> metadatavalue table from an earlier version of DSpace?
>
> Is there another way to look up these CommName and CollName values?
>
> Thanks.
>
> Stacy
>
>
> On Thursday, November 16, 2017 at 5:04:44 PM UTC-6, Terry Brady wrote:
>
>> Do you have the REST API enabled? The following endpoint will provide a
>> dump of the hierarchy that is easy to parse.
>>
>> https://demo.dspace.org/rest/hierarchy
>>
>> If that does not help, here are some SQL that might help.
>>
>> with recursive r_comm2coll as (
>> select
>> community_id,
>> collection_id
>> from
>> community2collection
>> union
>> select
>> cm2cm.parent_comm_id as community_id,
>> r.collection_id
>> from
>> r_comm2coll r
>> inner join
>> community2community cm2cm
>> on
>> cm2cm.child_comm_id = r.community_id
>> )
>>
>> select distinct
>> commh.handle as CommHandle,
>> collh.handle as CollHandle,
>> (
>> select text_value
>> from metadatavalue mv
>> where
>> mv.resource_id=commh.resource_id and mv.resource_type_id=4
>> and metadata_field_id = (
>> select metadata_field_id
>> from metadatafieldregistry where element='title' and qualifier is null
>> )
>> ) as CommName,
>> (
>> select text_value
>> from metadatavalue mv
>> where
>> mv.resource_id=collh.resource_id and mv.resource_type_id=3
>> and metadata_field_id = (
>> select metadata_field_id
>> from metadatafieldregistry where element='title' and qualifier is null
>> )
>> ) as CollName
>>
>> from
>> handle commh
>> inner join r_comm2coll
>> on r_comm2coll.community_id=commh.resource_id and
>> commh.resource_type_id=4
>> inner join handle collh
>> on r_comm2coll.collection_id=collh.resource_id and
>> collh.resource_type_id=3
>> order by CommHandle,CollHandle
>>
>>
>>
>>
>>
>>
>>
>>
>> On Thu, Nov 16, 2017 at 12:03 PM, Stacy Pennington <[email protected]>
>> wrote:
>>
>>> Does anyone out there have some useful PostgreSQL queries already
>>> written for DSpace 6.2?
>>>
>>> I've found several sets of PostgreSQL queries that were useful for
>>> earlier versions of DSpace, but most of them appear to be written between
>>> the versions 1.8 and 4.x, and the database changes from the past few years,
>>> particularly the UUID use, make many of them not work today. My attempts to
>>> repair them and make them work for 6.2 haven't gone well.
>>>
>>> What I'm actually looking for is a query that would provide a
>>> hierarchical list of communities, sub-communities, and collections in
>>> simple list, probably with just 2-space character indention at each
>>> successive sub-level, include the number of items at each level. Basically,
>>> think of something like what you get when you click Browse > Communities &
>>> Collections in the JSPUI interface but without the images and intro text of
>>> each community/collection and with the number of items for each level.
>>>
>>> (Basically, we need this because we are trying to flatten and
>>> uncomplicate our current DSpace topology, and we have over 500 collections,
>>> so getting an overview or monitoring our progress is really difficult.)
>>>
>>> Before I dive into this and try to create one of these SQL queries on my
>>> own, it seems like a good idea to see if anyone has already done it.
>>>
>>> Thanks.
>>>
>>> Stacy
>>>
>>> --
>>> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at https://groups.google.com/group/dspace-tech.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>>
>> --
>> Terry Brady
>> Applications Programmer Analyst
>> Georgetown University Library Information Technology
>> https://github.com/terrywbrady/info
>> 425-298-5498 <(425)%20298-5498> (Seattle, WA)
>>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/dspace-tech.
> For more options, visit https://groups.google.com/d/optout.
>
--
Terry Brady
Applications Programmer Analyst
Georgetown University Library Information Technology
https://github.com/terrywbrady/info
425-298-5498 (Seattle, WA)
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.
