SQL queries for retrieving item details as we get in api call

Community Contributions Data Modeling
1

Hi, I’m trying to compose SQL queries to retrieve the same information we get from /api/items/[item_id] API call to an Omeka-S instance.

If someone out there has this already figured out, can you please share? Thanks in advance!

I’ll also post what I can find here from my research.

2

I’m not looking directly at the API at the moment to be exact but in a rough sense you’d be looking at:

  • the item table itself has almost nothing, only the ID of the “primary” media, most “base” information about items (and other resources) is in
  • the resource table, which has the ID of the owning user, resource class, template, custom thumbnail, public/private status
  • the value table holds metadata values for all resources, look up with a match to the resource_id column; this is where much of the data is actually stored
  • items are contained in sets in a many-to-many relationship with the item_item_set table (and item sets are themselves resources to which the above bullets apply)
  • the media table holds media; all media belong to one and only one parent item, look up with the item_id column; media-specific info like filenames and data for external resources is directly in the media table, and they’re also resources so the resource and value tables apply just as for items and sets

Depending on what you’re specifically looking for you could potentially get it with a single query, or need multiple.