Mercurial > hg > orthanc
diff OrthancServer/Sources/Search/ISqlLookupFormatter.cpp @ 5825:881cd0965146 find-refactoring
added OrderBy support in SQLite
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Fri, 04 Oct 2024 19:03:14 +0200 |
parents | 16ce3c920f71 |
children | d73dfb4548c6 |
line wrap: on
line diff
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Fri Sep 27 14:38:20 2024 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Fri Oct 04 19:03:14 2024 +0200 @@ -28,6 +28,7 @@ #include "../../../OrthancFramework/Sources/Toolbox.h" #include "../Database/FindRequest.h" #include "DatabaseConstraint.h" +#include "../Database/MainDicomTagsRegistry.h" #include <cassert> #include <boost/lexical_cast.hpp> @@ -263,6 +264,76 @@ } + static void FormatJoinForOrdering(std::string& target, + const DicomTag& tag, + size_t index, + ResourceType requestLevel) + { + std::string orderArg = "order" + boost::lexical_cast<std::string>(index); + + target.clear(); + + ResourceType tagLevel; + DicomTagType tagType; + MainDicomTagsRegistry registry; + + registry.LookupTag(tagLevel, tagType, tag); + + if (tagLevel == ResourceType_Patient && requestLevel == ResourceType_Study) + { // Patient tags are copied at study level + tagLevel = ResourceType_Study; + } + + std::string tagTable; + if (tagType == DicomTagType_Identifier) + { + tagTable = "DicomIdentifiers "; + } + else + { + tagTable = "MainDicomTags "; + } + + std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tag.GetGroup()) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tag.GetElement()); + + if (tagLevel == requestLevel) + { + target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter; + } + } + + static void FormatJoinForOrdering(std::string& target, + const MetadataType& metadata, + size_t index, + ResourceType requestLevel) + { + std::string arg = "order" + boost::lexical_cast<std::string>(index); + + + target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + arg + ".type = " + + boost::lexical_cast<std::string>(metadata); + } + static std::string Join(const std::list<std::string>& values, const std::string& prefix, const std::string& separator) @@ -619,8 +690,51 @@ assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); - std::string ordering = "row_number() over (order by " + strQueryLevel + ".publicId) as rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + std::string ordering; + std::string orderingJoins; + + if (request.GetOrdering().size() > 0) + { + int counter = 0; + std::vector<std::string> orderByFields; + for (std::deque<FindRequest::Ordering*>::const_iterator it = request.GetOrdering().begin(); it != request.GetOrdering().end(); ++it) + { + std::string orderingJoin; + switch ((*it)->GetKeyType()) + { + case FindRequest::KeyType_DicomTag: + FormatJoinForOrdering(orderingJoin, (*it)->GetDicomTag(), counter, request.GetLevel()); + break; + case FindRequest::KeyType_Metadata: + FormatJoinForOrdering(orderingJoin, (*it)->GetMetadataType(), counter, request.GetLevel()); + break; + default: + throw OrthancException(ErrorCode_InternalError); + } + orderingJoins += orderingJoin; + + std::string orderByField = "order" + boost::lexical_cast<std::string>(counter) + ".value"; + if ((*it)->GetDirection() == FindRequest::OrderingDirection_Ascending) + { + orderByField += " ASC"; + } + else + { + orderByField += " DESC"; + } + orderByFields.push_back(orderByField); + ++counter; + } + + std::string orderByFieldsString; + Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", "); + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber"; + } + else + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + } sql = ("SELECT " + strQueryLevel + ".publicId, " + @@ -738,7 +852,7 @@ ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); } - sql += joins + Join(where, " WHERE ", " AND "); + sql += joins + orderingJoins + Join(where, " WHERE ", " AND "); if (request.HasLimits()) {