Hello,
Is there some way to get the value of a study’s “LastUpdate” metadata tag directly from postgresql db?
This is the query I have come up with. Can someone confirm if Im doing it right?
`
select value from resources join metadata on resources.internalid=metadata.id where resourcetype=1 and publicid=‘f449e965-14455898-ce34306a-d7982152-ef12b584’
`
As always, you should not access the database directly, and use the REST API instead.
Sébastien-
Is there a way via the restapi to get a list of studies whose "lastupdate" tag falls between a certain range? I haven't been able to figure out how to achieve this
No, you’ll have to implement a loop by yourself.
See that's why I'm asking for a way to do it via a database query. Looping through 30,000 studies isn't practicle. Could you please just let me know if the query I've written is ok? Thanks
Why don’t you test this SQL query by yourself? This is a pure SQL question, for which many tutorials exist over Internet.
Check out the list of available metadata in the source code of Orthanc:
Note that metadata is not indexed in the SQL database, so the SQL server will still implement a loop over all the studies. You could implement a plugin to implement this filter, this would avoid calling the REST API to get the metadata of each study:
https://book.orthanc-server.com/developers/creating-plugins.html
The query works just fine… I’m not asking if there are any syntactic issues in the query. I’m just asking if it is indeed getting the data I want ie the “LastUpdate” tag.
You don’t filter the “type” column in the “Metadata” table to be “MetadataType_LastUpdate” (i.e. “7”, check out my previous message), so your SQL query can’t work.
Thats weird…when I run the query I wrote it runs fine and gives the following result:
`
20191128T132054
`
Here is what I came up with based on the info you gave in your last post:
`
select value from resources join metadata on resources.internalid=metadata.id where resourcetype=1 and metadata.type=7 and publicid=‘f449e965-14455898-ce34306a-d7982152-ef12b584’
`
The “resourceType” should be “2” if you are playing with studies, as “1” corresponds to patients:
https://bitbucket.org/sjodogne/orthanc/src/Orthanc-1.5.8/Core/Enumerations.h#lines-709
This is weird…
When I run the following I get 13 rows which is the number of studies in my test Orthanc setup
`
select value from resources join metadata on resources.internalid=metadata.id where resourcetype=1 and metadata.type=7
`
but when I change resourcetype to “2” I get 40 rows which is the number of series in Orthanc
`
select value from resources join metadata on resources.internalid=metadata.id where resourcetype=2 and metadata.type=7
`
Indeed, my mistake, this is because your are using a database plugin (not the built-in SQLite index), for which the “OrthancPluginResourceType” enumeration is used instead of the “Orthanc::ResourceType” enumeration:
https://bitbucket.org/sjodogne/orthanc/src/Orthanc-1.5.8/Plugins/Include/orthanc/OrthancCPlugin.h#lines-678
By the way, this illustrates the necessity of avoiding direct access to the database (whose structure is implementation-specific), and favoring access through the REST API (that unifies the interface).
Thanks as usual for all your help Sébastien.