Mercurial > hg > orthanc-databases
changeset 583:ae7375d38607 find-refactoring tip
MySQL: fix ordering
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Mon, 21 Oct 2024 18:19:51 +0200 |
| parents | 8296c6a0238e |
| children | |
| files | Framework/Plugins/ISqlLookupFormatter.cpp Framework/Plugins/ISqlLookupFormatter.h Framework/Plugins/IndexBackend.cpp |
| diffstat | 3 files changed, 23 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/ISqlLookupFormatter.cpp Mon Oct 21 17:57:52 2024 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.cpp Mon Oct 21 18:19:51 2024 +0200 @@ -904,7 +904,13 @@ orderingJoins += orderingJoin; - std::string orderByField = "order" + boost::lexical_cast<std::string>(i) + ".value"; + std::string orderByField; + if (!formatter.SupportsNullsLast()) + { + orderByField = "CASE WHEN order" + boost::lexical_cast<std::string>(i) + ".value IS NULL THEN 1 ELSE 0 END, "; + } + orderByField += "order" + boost::lexical_cast<std::string>(i) + ".value"; + if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC) { orderByField += " ASC"; @@ -919,7 +925,15 @@ std::string orderByFieldsString; Orthanc::Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", "); - ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) AS rowNumber"; + + if (formatter.SupportsNullsLast()) + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) AS rowNumber"; + } + else + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber"; + } } else {
--- a/Framework/Plugins/ISqlLookupFormatter.h Mon Oct 21 17:57:52 2024 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.h Mon Oct 21 18:19:51 2024 +0200 @@ -71,6 +71,8 @@ **/ virtual bool IsEscapeBrackets() const = 0; + virtual bool SupportsNullsLast() const = 0; + static void GetLookupLevels(Orthanc::ResourceType& lowerLevel, Orthanc::ResourceType& upperLevel, const Orthanc::ResourceType& queryLevel,
--- a/Framework/Plugins/IndexBackend.cpp Mon Oct 21 17:57:52 2024 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Mon Oct 21 18:19:51 2024 +0200 @@ -2266,6 +2266,11 @@ return (dialect_ == Dialect_MSSQL); } + virtual bool SupportsNullsLast() const + { + return (dialect_ == Dialect_PostgreSQL); + } + void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const { statement.SetReadOnly(true);