# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1726849156 -7200
# Node ID cf6aea0bf438740a67c25674aff3e84d127b73b5
# Parent  13234fec943819db73d6acff34b1a610b465ee45
fix retrieval of grandchildren info + cleanup

diff -r 13234fec9438 -r cf6aea0bf438 Framework/Plugins/IndexBackend.cpp
--- a/Framework/Plugins/IndexBackend.cpp	Wed Sep 18 14:54:09 2024 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Fri Sep 20 18:19:16 2024 +0200
@@ -3230,8 +3230,8 @@
              "  tagElement AS c7_int2, "
              "  NULL::BIGINT AS c8_big_int1, "
              "  NULL::BIGINT AS c9_big_int2 "
-             "FROM MainDicomTags "
-             "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId ";
+             "FROM Lookup "
+             "INNER JOIN MainDicomTags ON MainDicomTags.id = Lookup.internalId ";
     }
     
     // need resource metadata ?
@@ -3248,8 +3248,8 @@
              "  NULL::INT AS c7_int2, "
              "  NULL::BIGINT AS c8_big_int1, "
              "  NULL::BIGINT AS c9_big_int2 "
-             "FROM Metadata "
-             "INNER JOIN Lookup ON Metadata.id = Lookup.internalId ";
+             "FROM Lookup "
+             "INNER JOIN Metadata ON Metadata.id = Lookup.internalId ";
     }
 
     // need resource attachments ?
@@ -3266,8 +3266,8 @@
              "  compressionType AS c7_int2, "
              "  compressedSize AS c8_big_int1, "
              "  uncompressedSize AS c9_big_int2 "
-             "FROM AttachedFiles "
-             "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId ";
+             "FROM Lookup "
+             "INNER JOIN AttachedFiles ON AttachedFiles.id = Lookup.internalId ";
     }
 
     // need resource labels ?
@@ -3284,8 +3284,8 @@
              "  NULL::INT AS c7_int2, "
              "  NULL::BIGINT AS c8_big_int1, "
              "  NULL::BIGINT AS c9_big_int2 "
-             "FROM Labels "
-             "INNER JOIN Lookup ON Labels.id = Lookup.internalId ";
+             "FROM Lookup "
+             "INNER JOIN Labels ON Labels.id = Lookup.internalId ";
     }
 
     // need MainDicomTags from parent ?
@@ -3421,7 +3421,7 @@
         break;
       }
 
-      if (childrenSpec->retrieve_main_dicom_tags_size() > 0)   // TODO: retrieve only the requested tags ?
+      if (childrenSpec->retrieve_main_dicom_tags_size() > 0)
       {
         sql += "UNION SELECT "
                "  " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
@@ -3453,9 +3453,8 @@
                "  NULL::INT AS c7_int2, "
                "  NULL::BIGINT AS c8_big_int1, "
                "  NULL::BIGINT AS c9_big_int2 "
-               "FROM Resources AS currentLevel "
-               "  INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
-               "  INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
+               "FROM Lookup "
+               "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId ";
       }
 
       if (childrenSpec->retrieve_metadata_size() > 0)
@@ -3506,9 +3505,8 @@
                 "  NULL::INT AS c7_int2, "
                 "  NULL::BIGINT AS c8_big_int1, "
                 "  NULL::BIGINT AS c9_big_int2 "
-                "FROM Resources AS currentLevel "
-                "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
-                "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
+                "FROM Lookup "
+                "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
                 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
         }
 
@@ -3527,7 +3525,7 @@
                  "  NULL::BIGINT AS c9_big_int2 "
                  "FROM Lookup "
                  "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
-                 "  INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
+                 "  INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
                  "  INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(grandchildrenSpec) + ")";
         }
 
@@ -3546,7 +3544,7 @@
                  "  NULL::BIGINT AS c9_big_int2 "
                  "FROM Lookup "
                  "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
-                 "  INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
+                 "  INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
                  "  INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(grandchildrenSpec) + ") ";
         }
 
@@ -3568,9 +3566,8 @@
                   "  NULL::INT AS c7_int2, "
                   "  NULL::BIGINT AS c8_big_int1, "
                   "  NULL::BIGINT AS c9_big_int2 "
-                  "FROM Resources AS currentLevel "
-                  "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
-                  "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
+                  "FROM Lookup "
+                  "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
                   "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
                   "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
           }
@@ -3592,9 +3589,9 @@
              "  NULL::INT AS c7_int2, "
              "  NULL::BIGINT AS c8_big_int1, "
              "  NULL::BIGINT AS c9_big_int2 "
-             "FROM Resources AS currentLevel "
-             "  INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
-             "  INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
+             "FROM Lookup "
+             "  INNER JOIN Resources ON currentLevel.internalId = Lookup.internalId "
+             "  INNER JOIN Resources parentLevel ON Lookup.parentId = parentLevel.internalId ";
     }
 
     // need one instance info ?