On Wed, Aug 29, 2012 at 8:32 AM, Mark Kariuki <mark.kari...@fao.org> wrote: > What I require is a way in which to identify all the items that do not have > bitstreams associated with them so that I can hand this list to our > librarian for him to locate the necessary files. Does such a way exist?
Mark, in Discovery, this is a simple query: "-fulltext:[* TO *]" (without the quotes). The corresponding SQL query turned out to be a bit complicated. The inner query selects items which have bitstreams. The outer query negates it and removes special cases. The negation was simpler to construct than directly querying for items that have bitstreams. SELECT DISTINCT 'http://example.com/handle/'||handle.handle FROM handle,item WHERE handle.handle NOT IN ( -- select all items with bitstreams SELECT DISTINCT handle.handle FROM handle,item, item2bundle, bundle2bitstream WHERE handle.resource_id = item.item_id AND item.item_id = item2bundle.item_id AND item2bundle.bundle_id = bundle2bitstream.bundle_id AND bitstream_id IN (SELECT bitstream_id FROM bitstream) ) -- only handles which are items AND handle.resource_type_id=2 -- handles which belong to existing (not deleted) items AND handle.resource_id IS NOT NULL -- and item is not withdrawn AND handle.resource_id = item.item_id AND item.withdrawn = 'f' ORDER BY 'http://example.com/handle/'||handle.handle The numbers returned with the Solr and the SQL query may not be an exact match, but it should do. Regards, ~~helix84 ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
