Question about SQL query search (and about the schema design)

Google Groups archive
1

Hello Sébastien and the group,

Thanks for your great work !!

I am facing the situation that the /tools/find can not quite satisfy my need . (Like there’s no count field so it’s hard to do pagination and I may want the expand option can give more information (Not complain just my case) )

With the MySQL index plugin, I decide to implement the search by directly access to the database and I have been struggling for days and would you mind give some advices…

First question is how to efficiently do the multiple tag-matching-level especially the tag filed are from different levels (Patient/Study/Series/Instance) ? For example, I want get all the instances where

  • PatientName (a tag of patient whose internalId is let’s say 4) is xxx
  • And StudyDescription (a tag of study whose internalId is let’s say 3) is yyy
  • And BodyPartExamined (a tag of series whose internalId is let’s say 2) is zzz
    since 2 != 3 != 4 !=2 and there’s no way to know the 2,3,4 are related unless we check the Resources table and I am dead… But the task is so innocent…(I did do some research in the C++ source code but I failed to find any SQL-like string…)

Another question is about the schema : it seems to me that something like tree is maintained in the Resources table and this drives me crazy when I want to get all the resources that below a certain level ? (e.g. I want to get all the instances (with tags) of all series (with tags) belong to some study, a tree with depth 3 where each node contains information from MainDicomTags table )

Any suggestions ?

Thanks a lot !!

2

Hello,

Regarding your first question, you need to use SQL’s “INNER JOIN” three times on the “Resources” table:
https://www.w3schools.com/sql/sql_join.asp

Regarding your second question, the level of a row in the “Resources” table is given by the “resourceType” column (in the MySQL index plugin: patient = 0, study = 1, series = 2, and instance = 3).

Sébastien-

3

Hello Jodogne,

Thanks a lot !!

For the 2nd question, I would like to share my function that will do the trick

CREATE DEFINER=orthanc@%% FUNCTION getResourceChain(uid int) RETURNS text CHARSET utf8mb4
BEGIN
DECLARE chainStr VARCHAR(1000) DEFAULT ‘’;
DECLARE curInternalId INTEGER DEFAULT uid;
DECLARE curType INTEGER DEFAULT -1;
SELECT resourceType into curType From Resources WHERE internalId = curInternalId;
WHILE (curType>0) DO
SELECT parentId into curInternalId FROM Resources where internalId = curInternalId;
SELECT resourceType into curType From Resources WHERE internalId = curInternalId;
SET chainStr = concat(chainStr,‘,’,curInternalId);
END WHILE;
SET chainStr = RIGHT(chainStr,CHARACTER_LENGTH(chainStr)-1);
RETURN chainStr;
END

For the 1st question, I am still digging…

Sébastien Jodogne於 2019年4月4日星期四 UTC+8下午8時58分22秒寫道：