Thanks, guys! That gives me something to chew on. What I need the query for
is to generate a media rss feed from Dspace.
On Wed, Dec 10, 2014 at 5:16 PM, Brown, Jacob <j.h.br...@tcu.edu> wrote:
> 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:marke...@gmail.com]
> *Sent:* Wednesday, December 10, 2014 11:12 AM
> *To:* DSpace-tech@lists.sourceforge.net
> *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
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
