Skip to content

Commit

Permalink
[FEATURE] Add order by support to expression aggregate and concatenat…
Browse files Browse the repository at this point in the history 
…ion functions

Because certain aggregates and concatenation requires results in
a certain order, this change allows specific control of the order
features are added to the aggregate during an expression evaluation.

E.g.

concatenate("Station",concatenator:=',', order_by:="Station")

will give a comma separated list of station names in alphabetical
order, rather than layer feature order.

Sponsored by SMEC/SJ
  • Loading branch information
@nyalldawson
nyalldawson committed May 9, 2019
1 parent 6b16218 commit 2e52573
Show file tree
Hide file tree
Showing 7 changed files with 112 additions and 21 deletions.
3 changes: 2 additions & 1 deletion resources/function_help/json/aggregate
View file
Expand Up @@ -7,7 +7,8 @@
{"arg":"aggregate", "description":"a string corresponding to the aggregate to calculate. Valid options are:<br /><ul><li>count</li><li>count_distinct</li><li>count_missing</li><li>min</li><li>max</li><li>sum</li><li>mean</li><li>median</li><li>stdev</li><li>stdevsample</li><li>range</li><li>minority</li><li>majority</li><li>q1: first quartile</li><li>q3: third quartile</li><li>iqr: inter quartile range</li><li>min_length: minimum string length</li><li>max_length: maximum string length</li><li>concatenate: join strings with a concatenator</li><li>collect: create an aggregated multipart geometry</li></ul>"},
{"arg":"expression", "description":"sub expression or field name to aggregate"},
{"arg":"filter", "optional":true, "description":"optional filter expression to limit the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer. The source feature can be accessed with the variable @parent."},
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values for 'concatenate' aggregate"}
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values for 'concatenate' aggregate"},
{"arg":"order_by", "optional":true, "description":"optional filter expression to order the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer."}
],
"examples": [
{ "expression":"aggregate(layer:='rail_stations',aggregate:='sum',expression:=\"passengers\")", "returns":"sum of all values from the passengers field in the rail_stations layer"},
Expand Down
3 changes: 2 additions & 1 deletion resources/function_help/json/array_agg
View file
Expand Up @@ -5,7 +5,8 @@
"arguments": [
{"arg": "expression", "description": "sub expression of field to aggregate"},
{"arg": "group_by", "optional": true, "description": "optional expression to use to group aggregate calculations"},
{"arg": "filter", "optional": true, "description": "optional expression to use to filter features used to calculate aggregate"}
{"arg": "filter", "optional": true, "description": "optional expression to use to filter features used to calculate aggregate"},
{"arg": "order_by", "optional": true, "description": "optional expression to use to order features used to calculate aggregate"}
],
"examples": [
{ "expression": "array_agg(\"name\",group_by:=\"state\")", "returns":"list of name values, grouped by state field"}
Expand Down
5 changes: 3 additions & 2 deletions resources/function_help/json/concatenate
View file
@@ -1,12 +1,13 @@
{
"name": "concatenate",
"type": "function",
"description": "Returns the all aggregated strings from a field or expression joined by a delimiter.",
"description": "Returns all aggregated strings from a field or expression joined by a delimiter.",
"arguments": [
{"arg":"expression", "description":"sub expression of field to aggregate"},
{"arg":"group_by", "optional":true, "description":"optional expression to use to group aggregate calculations"},
{"arg":"filter", "optional":true, "description":"optional expression to use to filter features used to calculate aggregate"},
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values"}
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values"},
{"arg":"order_by", "optional":true, "description":"optional expression to use to order features used to calculate aggregate"}
],
"examples": [
{ "expression":"concatenate(\"town_name\",group_by:=\"state\",concatenator:=',')", "returns":"comma separated list of town_names, grouped by state field"}
Expand Down
15 changes: 15 additions & 0 deletions resources/function_help/json/concatenate_unique
View file
@@ -0,0 +1,15 @@
{
"name": "concatenate_unique",
"type": "function",
"description": "Returns all unique strings from a field or expression joined by a delimiter.",
"arguments": [
{"arg":"expression", "description":"sub expression of field to aggregate"},
{"arg":"group_by", "optional":true, "description":"optional expression to use to group aggregate calculations"},
{"arg":"filter", "optional":true, "description":"optional expression to use to filter features used to calculate aggregate"},
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values"},
{"arg":"order_by", "optional":true, "description":"optional expression to use to order features used to calculate aggregate"}
],
"examples": [
{ "expression":"concatenate(\"town_name\",group_by:=\"state\",concatenator:=',')", "returns":"comma separated list of unique town_names, grouped by state field"}
]
}
3 changes: 2 additions & 1 deletion resources/function_help/json/relation_aggregate
View file
Expand Up @@ -6,7 +6,8 @@
{"arg":"relation", "description":"a string, representing a relation ID"},
{"arg":"aggregate", "description":"a string corresponding to the aggregate to calculate. Valid options are:<br /><ul><li>count</li><li>count_distinct</li><li>count_missing</li><li>min</li><li>max</li><li>sum</li><li>mean</li><li>median</li><li>stdev</li><li>stdevsample</li><li>range</li><li>minority</li><li>majority</li><li>q1: first quartile</li><li>q3: third quartile</li><li>iqr: inter quartile range</li><li>min_length: minimum string length</li><li>max_length: maximum string length</li><li>concatenate: join strings with a concatenator</li></ul>"},
{"arg":"expression", "description":"sub expression or field name to aggregate"},
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values for 'concatenate' aggregate"}
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values for 'concatenate' aggregate"},
{"arg":"filter", "optional":true, "description":"optional filter expression to order the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer."}
],
"examples": [
{ "expression":"relation_aggregate(relation:='my_relation',aggregate:='mean',expression:=\"passengers\")", "returns":"mean value of all matching child features using the 'my_relation' relation"},
Expand Down
90 changes: 74 additions & 16 deletions src/core/expression/qgsexpressionfunction.cpp
View file
Expand Up @@ -550,6 +550,20 @@ static QVariant fcnAggregate( const QVariantList &values, const QgsExpressionCon
parameters.delimiter = value.toString();
}

//optional sixth node is order by
QString orderBy;
if ( values.count() > 5 )
{
node = QgsExpressionUtils::getNode( values.at( 5 ), parent );
ENSURE_NO_EVAL_ERROR;
QgsExpressionNodeLiteral *nl = dynamic_cast< QgsExpressionNodeLiteral * >( node );
if ( !nl || nl->value().isValid() )
{
orderBy = node->dump();
parameters.orderBy << QgsFeatureRequest::OrderByClause( orderBy );
}
}

QVariant result;
if ( context )
{
Expand All @@ -561,12 +575,12 @@ static QVariant fcnAggregate( const QVariantList &values, const QgsExpressionCon
|| subExp.referencedVariables().contains( QStringLiteral( "parent" ) )
|| subExp.referencedVariables().contains( QString() ) )
{
cacheKey = QStringLiteral( "aggfcn:%1:%2:%3:%4:%5%6" ).arg( vl->id(), QString::number( aggregate ), subExpression, parameters.filter,
QString::number( context->feature().id() ), QString( qHash( context->feature() ) ) );
cacheKey = QStringLiteral( "aggfcn:%1:%2:%3:%4:%5%6:%7" ).arg( vl->id(), QString::number( aggregate ), subExpression, parameters.filter,
QString::number( context->feature().id() ), QString( qHash( context->feature() ) ), orderBy );
}
else
{
cacheKey = QStringLiteral( "aggfcn:%1:%2:%3:%4" ).arg( vl->id(), QString::number( aggregate ), subExpression, parameters.filter );
cacheKey = QStringLiteral( "aggfcn:%1:%2:%3:%4:%5" ).arg( vl->id(), QString::number( aggregate ), subExpression, parameters.filter, orderBy );
}

if ( context && context->hasCachedValue( cacheKey ) )
Expand Down Expand Up @@ -665,13 +679,29 @@ static QVariant fcnAggregateRelation( const QVariantList &values, const QgsExpre
parameters.delimiter = value.toString();
}

//optional fifth node is order by
QString orderBy;
if ( values.count() > 4 )
{
node = QgsExpressionUtils::getNode( values.at( 4 ), parent );
ENSURE_NO_EVAL_ERROR;
QgsExpressionNodeLiteral *nl = dynamic_cast< QgsExpressionNodeLiteral * >( node );
if ( !nl || nl->value().isValid() )
{
orderBy = node->dump();
parameters.orderBy << QgsFeatureRequest::OrderByClause( orderBy );
}
}


FEAT_FROM_CONTEXT( context, f );
parameters.filter = relation.getRelatedFeaturesFilter( f );

QString cacheKey = QStringLiteral( "relagg:%1:%2:%3:%4" ).arg( vl->id(),
QString cacheKey = QStringLiteral( "relagg:%1:%2:%3:%4:%5" ).arg( vl->id(),
QString::number( static_cast< int >( aggregate ) ),
subExpression,
parameters.filter );
parameters.filter,
orderBy );
if ( context && context->hasCachedValue( cacheKey ) )
return context->cachedValue( cacheKey );

Expand All @@ -695,7 +725,7 @@ static QVariant fcnAggregateRelation( const QVariantList &values, const QgsExpre
}


static QVariant fcnAggregateGeneric( QgsAggregateCalculator::Aggregate aggregate, const QVariantList &values, QgsAggregateCalculator::AggregateParameters parameters, const QgsExpressionContext *context, QgsExpression *parent )
static QVariant fcnAggregateGeneric( QgsAggregateCalculator::Aggregate aggregate, const QVariantList &values, QgsAggregateCalculator::AggregateParameters parameters, const QgsExpressionContext *context, QgsExpression *parent, int orderByPos = -1 )
{
if ( !context )
{
Expand Down Expand Up @@ -739,6 +769,20 @@ static QVariant fcnAggregateGeneric( QgsAggregateCalculator::Aggregate aggregate
parameters.filter = node->dump();
}

//optional order by node, if supported
QString orderBy;
if ( orderByPos >= 0 && values.count() > orderByPos )
{
node = QgsExpressionUtils::getNode( values.at( orderByPos ), parent );
ENSURE_NO_EVAL_ERROR;
QgsExpressionNodeLiteral *nl = dynamic_cast< QgsExpressionNodeLiteral * >( node );
if ( !nl || nl->value().isValid() )
{
orderBy = node->dump();
parameters.orderBy << QgsFeatureRequest::OrderByClause( orderBy );
}
}

// build up filter with group by

// find current group by value
Expand All @@ -755,10 +799,11 @@ static QVariant fcnAggregateGeneric( QgsAggregateCalculator::Aggregate aggregate
parameters.filter = groupByClause;
}

QString cacheKey = QStringLiteral( "agg:%1:%2:%3:%4" ).arg( vl->id(),
QString cacheKey = QStringLiteral( "agg:%1:%2:%3:%4:%5" ).arg( vl->id(),
QString::number( static_cast< int >( aggregate ) ),
subExpression,
parameters.filter );
parameters.filter,
orderBy );
if ( context && context->hasCachedValue( cacheKey ) )
return context->cachedValue( cacheKey );

Expand Down Expand Up @@ -885,7 +930,7 @@ static QVariant fcnAggregateStringConcat( const QVariantList &values, const QgsE
parameters.delimiter = value.toString();
}

return fcnAggregateGeneric( QgsAggregateCalculator::StringConcatenate, values, parameters, context, parent );
return fcnAggregateGeneric( QgsAggregateCalculator::StringConcatenate, values, parameters, context, parent, 4 );
}

static QVariant fcnAggregateStringConcatUnique( const QVariantList &values, const QgsExpressionContext *context, QgsExpression *parent, const QgsExpressionNodeFunction * )
Expand All @@ -902,12 +947,12 @@ static QVariant fcnAggregateStringConcatUnique( const QVariantList &values, cons
parameters.delimiter = value.toString();
}

return fcnAggregateGeneric( QgsAggregateCalculator::StringConcatenateUnique, values, parameters, context, parent );
return fcnAggregateGeneric( QgsAggregateCalculator::StringConcatenateUnique, values, parameters, context, parent, 4 );
}

static QVariant fcnAggregateArray( const QVariantList &values, const QgsExpressionContext *context, QgsExpression *parent, const QgsExpressionNodeFunction * )
{
return fcnAggregateGeneric( QgsAggregateCalculator::ArrayAggregate, values, QgsAggregateCalculator::AggregateParameters(), context, parent );
return fcnAggregateGeneric( QgsAggregateCalculator::ArrayAggregate, values, QgsAggregateCalculator::AggregateParameters(), context, parent, 3 );
}

static QVariant fcnMapScale( const QVariantList &, const QgsExpressionContext *context, QgsExpression *, const QgsExpressionNodeFunction * )
Expand Down Expand Up @@ -4849,6 +4894,13 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
<< QgsExpressionFunction::Parameter( QStringLiteral( "group_by" ), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "filter" ), true );

QgsExpressionFunction::ParameterList aggParamsConcat = aggParams;
aggParamsConcat << QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "order_by" ), true, QVariant(), true );

QgsExpressionFunction::ParameterList aggParamsArray = aggParams;
aggParamsArray << QgsExpressionFunction::Parameter( QStringLiteral( "order_by" ), true, QVariant(), true );

sFunctions
<< new QgsStaticExpressionFunction( QStringLiteral( "sqrt" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "value" ) ), fcnSqrt, QStringLiteral( "Math" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "radians" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "degrees" ) ), fcnRadians, QStringLiteral( "Math" ) )
Expand Down Expand Up @@ -4907,7 +4959,8 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
<< QgsExpressionFunction::Parameter( QStringLiteral( "aggregate" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "expression" ), false, QVariant(), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "filter" ), true, QVariant(), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true ),
<< QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "order_by" ), true, QVariant(), true ),
fcnAggregate,
QStringLiteral( "Aggregates" ),
QString(),
Expand Down Expand Up @@ -4969,7 +5022,12 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
true
)

<< new QgsStaticExpressionFunction( QStringLiteral( "relation_aggregate" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "relation" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "aggregate" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "expression" ), false, QVariant(), true ) << QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true ),
<< new QgsStaticExpressionFunction( QStringLiteral( "relation_aggregate" ), QgsExpressionFunction::ParameterList()
<< QgsExpressionFunction::Parameter( QStringLiteral( "relation" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "aggregate" ) )
<< QgsExpressionFunction::Parameter( QStringLiteral( "expression" ), false, QVariant(), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true )
<< QgsExpressionFunction::Parameter( QStringLiteral( "order_by" ), true, QVariant(), true ),
fcnAggregateRelation, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>() << QgsFeatureRequest::ALL_ATTRIBUTES, true )

<< new QgsStaticExpressionFunction( QStringLiteral( "count" ), aggParams, fcnAggregateCount, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
Expand All @@ -4990,9 +5048,9 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
<< new QgsStaticExpressionFunction( QStringLiteral( "min_length" ), aggParams, fcnAggregateMinLength, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "max_length" ), aggParams, fcnAggregateMaxLength, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "collect" ), aggParams, fcnAggregateCollectGeometry, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "concatenate" ), aggParams << QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true ), fcnAggregateStringConcat, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "concatenate_unique" ), aggParams << QgsExpressionFunction::Parameter( QStringLiteral( "concatenator" ), true ), fcnAggregateStringConcatUnique, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "array_agg" ), aggParams, fcnAggregateArray, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "concatenate" ), aggParamsConcat, fcnAggregateStringConcat, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "concatenate_unique" ), aggParamsConcat, fcnAggregateStringConcatUnique, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "array_agg" ), aggParamsArray, fcnAggregateArray, QStringLiteral( "Aggregates" ), QString(), false, QSet<QString>(), true )

<< new QgsStaticExpressionFunction( QStringLiteral( "regexp_match" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "string" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "regex" ) ), fcnRegexpMatch, QStringList() << QStringLiteral( "Conditionals" ) << QStringLiteral( "String" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "regexp_matches" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "string" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "regex" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "emptyvalue" ), true, "" ), fcnRegexpMatches, QStringLiteral( "Arrays" ) )
Expand Down

0 comments on commit 2e52573

Please sign in to comment.