OK, Guys, just call me dense, but I can't make sense of Jacob's query -
where it says, bundle.name='THUMBNAIL' - the bundle table does not have a
name field. What am I missing?
(This is on a DSpace 5.0-rc2 box)
Thanks -
Mark
On Thu, Dec 11, 2014 at 7:08 AM, Mark Ehle <marke...@gmail.com> wrote:
> 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
>>
>
>
------------------------------------------------------------------------------
Dive into the World of Parallel Programming! The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net
_______________________________________________
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
