Compile order by for postgres

qgis · Dec 18, 2015 · c0f2b29 · c0f2b29
1 parent d96a274
commit c0f2b29
Show file tree

Hide file tree

Showing 2 changed files with 46 additions and 4 deletions.
diff --git a/src/providers/postgres/qgspostgresfeatureiterator.cpp b/src/providers/postgres/qgspostgresfeatureiterator.cpp
@@ -35,6 +35,7 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
     , mFetched( 0 )
     , mFetchGeometry( false )
     , mExpressionCompiled( false )
+    , mOrderByCompiled( false )
     , mLastFetch( false )
 {
   if ( !source->mTransactionConnection )
@@ -111,12 +112,40 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
     }
   }
 
-  bool success = declareCursor( whereClause, limitAtProvider ? mRequest.limit() : -1, false );
+  QStringList orderByParts;
+
+  mOrderByCompiled = true;
+
+  Q_FOREACH ( const QgsFeatureRequest::OrderByClause& clause, request.orderBys() )
+  {
+    QgsPostgresExpressionCompiler compiler = QgsPostgresExpressionCompiler( source );
+    QgsExpression expression = clause.expression();
+    if ( compiler.compile( &expression ) == QgsSqlExpressionCompiler::Complete )
+    {
+      QString part;
+      part = compiler.result();
+      part += clause.ascending() ? " ASC" : " DESC";
+      part += clause.nullsFirst() ? " NULLS FIRST" : " NULLS LAST";
+      orderByParts << part;
+    }
+    else
+    {
+      // Bail out on first non-complete compilation.
+      // Most important clauses at the beginning of the list
+      // will still be sent and used to pre-sort so the local
+      // CPU can use its cycles for fine-tuning.
+      mOrderByCompiled = false;
+      limitAtProvider = false;
+      break;
+    }
+  }
+
+  bool success = declareCursor( whereClause, limitAtProvider ? mRequest.limit() : -1, false, orderByParts.join( "," ) );
   if ( !success && useFallbackWhereClause )
   {
     //try with the fallback where clause, eg for cases when using compiled expression failed to prepare
     mExpressionCompiled = false;
-    success = declareCursor( fallbackWhereClause, -1, false );
+    success = declareCursor( fallbackWhereClause, -1, false, orderByParts.join( "," ) );
   }
 
   if ( !success )
@@ -232,6 +261,13 @@ bool QgsPostgresFeatureIterator::providerCanSimplify( QgsSimplifyMethod::MethodT
   return methodType == QgsSimplifyMethod::OptimizeForRendering || methodType == QgsSimplifyMethod::PreserveTopology;
 }
 
+bool QgsPostgresFeatureIterator::prepareOrderBy( const QList<QgsFeatureRequest::OrderByClause>& orderBys )
+{
+  Q_UNUSED( orderBys )
+  // Preparation has already been done in the constructor, so we just communicate the result
+  return mOrderByCompiled;
+}
+
 bool QgsPostgresFeatureIterator::rewind()
 {
   if ( mClosed )
@@ -347,7 +383,7 @@ QString QgsPostgresFeatureIterator::whereClauseRect()
 
 
 
-bool QgsPostgresFeatureIterator::declareCursor( const QString& whereClause, long limit, bool closeOnFail )
+bool QgsPostgresFeatureIterator::declareCursor( const QString& whereClause, long limit, bool closeOnFail, const QString& orderBy )
 {
   mFetchGeometry = !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) && !mSource->mGeometryColumn.isNull();
 #if 0
@@ -509,6 +545,9 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString& whereClause, long
   if ( limit >= 0 )
     query += QString( " LIMIT %1" ).arg( limit );
 
+  if ( !orderBy.isEmpty() )
+    query += QString( " ORDER BY %1 " ).arg( orderBy );
+
   if ( !mConn->openCursor( mCursorName, query ) )
   {
 

diff --git a/src/providers/postgres/qgspostgresfeatureiterator.h b/src/providers/postgres/qgspostgresfeatureiterator.h
@@ -97,7 +97,7 @@ class QgsPostgresFeatureIterator : public QgsAbstractFeatureIteratorFromSource<Q
     QString whereClauseRect();
     bool getFeature( QgsPostgresResult &queryResult, int row, QgsFeature &feature );
     void getFeatureAttribute( int idx, QgsPostgresResult& queryResult, int row, int& col, QgsFeature& feature );
-    bool declareCursor( const QString& whereClause, long limit = -1, bool closeOnFail = true );
+    bool declareCursor( const QString& whereClause, long limit = -1, bool closeOnFail = true , const QString& orderBy = QString() );
 
     QString mCursorName;
 
@@ -124,7 +124,10 @@ class QgsPostgresFeatureIterator : public QgsAbstractFeatureIteratorFromSource<Q
     //! returns whether the iterator supports simplify geometries on provider side
     virtual bool providerCanSimplify( QgsSimplifyMethod::MethodType methodType ) const override;
 
+    virtual bool prepareOrderBy( const QList<QgsFeatureRequest::OrderByClause> &orderBys ) override;
+
     bool mExpressionCompiled;
+    bool mOrderByCompiled;
     bool mLastFetch;
 };