# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1729527591 -7200
# Node ID ae7375d3860765e6e0765e881ed3763cf443321c
# Parent  8296c6a0238eb8daffb5067043d6c5f638f530d2
MySQL: fix ordering

diff -r 8296c6a0238e -r ae7375d38607 Framework/Plugins/ISqlLookupFormatter.cpp
--- 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
     {
diff -r 8296c6a0238e -r ae7375d38607 Framework/Plugins/ISqlLookupFormatter.h
--- 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,
diff -r 8296c6a0238e -r ae7375d38607 Framework/Plugins/IndexBackend.cpp
--- 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);