If you do not want to relay on the owning collection and use the collection
handle, you can do this:
select distinct text_value
from metadatavalue
where metadata_field_id = (select metadata_field_id from
metadatafieldregistry where element = 'contributor' and qualifier =
'author')
AND resource_type_id = 2 AND
resource_id IN (select item_id from collection2item where collection_id IN
(select resource_id from handle where handle in ('123456789/3',
'123456789/4')));
[image: logo] Tom Desair
250-B Suite 3A, Lucius Gordon Drive, West Henrietta, NY 14586
Esperantolaan 4, Heverlee 3001, Belgium
www.atmire.com
<http://atmire.com/website/?q=services&utm_source=emailfooter&utm_medium=email&utm_campaign=tomdesair>
2016-09-29 15:24 GMT+02:00 Sean Carte <sean.ca...@gmail.com>:
>
>
> On 29 September 2016 at 15:12, Alan Orth <alan.o...@gmail.com> wrote:
>
>> Hi,
>>
>> I want to generate a list of authors in a certain community and its
>> collections. My goal is to do some batch cleanups of the names.
>> Normally I just select all the distinct text_value fields for the
>> metadata field I'm interested in, like here for 3
>> (dc.contributor.author):
>>
>> dspace=# select distinct text_value from metadatavalue where
>> resource_type_id=2 and metadata_field_id=3;
>>
>> But I don't understand DSpace's database schema well enough to limit
>> that to a certain community and its collections.
>>
>> Can anyone help? This is DSpace 5 + PostgreSQL.
>>
>
> Hi Alan
>
> How about something like this:
>
> SELECT i.item_id AS item_id, i.owning_collection AS collection,
> d.text_value AS date, a.text_value AS author
> FROM metadatavalue d, metadatavalue a, item i
> WHERE d.resource_id = a.resource_id AND d.resource_id = i.item_id
> AND i.discoverable = 't' AND i.in_archive = 't' AND i.withdrawn = 'f'
> AND d.metadata_field_id = 11
> AND a.metadata_field_id = 3
> AND d.text_value >= '2016-01' AND d.text_value < '2016-10'
> AND i.owning_collection = 11;
>
> Sean
> --
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to dspace-tech@googlegroups.com.
> Visit this group at https://groups.google.com/group/dspace-tech.
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to dspace-tech@googlegroups.com.
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.
