Hi Mark,
Not sure if what you are using the query for, but if you are using it inside a
Java application, DSpace provides a
[service](https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/content/service/ItemService.java)
to get the primary bitstream’s thumbnail for an item. It’s used like, e.g.:
org.dspace.content.service.ItemService.getThumbnail(context, item.id, false)
If you need it outside Java, try the following (sorry for the CTEs, I’m using
SQL snippets from helix84 and the ItemService code and didn’t want to rewrite
everything). It could use some re-writing, but should get you started.
with itemHolder as (
SELECT item_id as id FROM item, handle
WHERE handle.resource_id = item.item_id
AND handle.handle = 'dspace/handle'
),
primaryBitstream as (
SELECT bundle.primary_bitstream_id as id FROM item2bundle, bundle
WHERE item2bundle.item_id in (SELECT id FROM itemHolder) AND
item2bundle.bundle_id=bundle.bundle_id AND bundle.name='ORIGINAL' LIMIT 1
),
nameHolder as (
SELECT name || '.jpg' as name
FROM bitstream
WHERE bitstream_id IN (SELECT id FROM PrimaryBitstream)
),
namedBitstream as (
SELECT bitstream.bitstream_id as id FROM item2bundle, bundle,
bundle2bitstream, bitstream
WHERE item2bundle.item_id IN (SELECT id FROM itemHolder) AND
item2bundle.bundle_id=bundle.bundle_id AND bundle.name='THUMBNAIL'
AND bundle.bundle_id=bundle2bitstream.bundle_id AND
bundle2bitstream.bitstream_id=bitstream.bitstream_id AND bitstream.name IN
(SELECT name from nameHolder)
) SELECT id FROM namedBitstream;
Jacob
From: Mark Ehle [mailto:[email protected]]
Sent: Wednesday, December 10, 2014 11:12 AM
To: [email protected]
Subject: [Dspace-tech] Query to get ID of thumbnail of primary bitstream
Folks -
I am in need of a way to query postgres in Dspace to give me the ID of the
primary bitstream's thumbnail ID. I am able to get the primary bitstream ID
from the handle (thanks to
helix84<http://dspace.2283337.n4.nabble.com/template/NamlServlet.jtp?macro=user_nodes&user=211466>,
http://dspace.2283337.n4.nabble.com/Get-item-id-from-bitstream-handle-td4662208.html)
but I don't know where to match up the thumbnail to it.
Thanks!
Mark Ehle
Computer Support Librarian
Willard Library
Battle Creek, MI
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
