/// <summary> /// Builds the field expression. /// </summary> /// <param name="fieldExpression">The field expression.</param> /// <param name="context"></param> /// <returns>ResourceDataColumn.</returns> private static ResourceDataColumn BuildFieldExpression(Model.FieldExpression fieldExpression, FromEntityContext context) { DatabaseType castType = TypedValueHelper.GetDatabaseType(fieldExpression.ReportExpressionResultType); if (fieldExpression.FieldExpressionField == null) { EventLog.Application.WriteWarning(context.DebugInfo + "field was null"); return(null); } ResourceDataColumn rdc = new ResourceDataColumn { FieldId = fieldExpression.FieldExpressionField, CastType = castType, ExpressionId = Guid.NewGuid(), SourceNodeEntityId = fieldExpression.As <NodeExpression>().SourceNode.Id }; return(rdc); }
protected override ScalarExpression OnBuildQuery(QueryBuilderContext context) { // Currently this must be a literal var field = Right as ConstantEntityNode; if (field == null) { throw new ParseException("Field required."); } var queryNode = context.GetNode(Left); // Start workaround for #27148 // If an aggregate operation is performed on a choice field, then we try to treat it like an entity // then we have a scenario that can't currently be represented in structured queries. // (Because sq aggregate operations get declared as expressions, but if it's an expression we can no longer use it as a sq node for other expressions). // This can happen if we use the choice-field in a comparison (e.g. max(choice)>whatever) because the compiler // will inject a field lookup to redirect to the ordering field. // For now, detect this specific scenario, and pass the aggregate expression directly through because the query engine // will defer to the ordering version of the SQL, which will in turn look up the enumOrder field. // However, ideally we should also be able to do things like max(choice).someOtherRelationshipOrField bool nodeIsAggregate = Left is AggregateNode || Left is EntityTypeCast && Left.Arguments[0] is AggregateNode; bool fieldIsEnumOrdering = field.Instance.Id == WellKnownAliases.CurrentTenant.EnumOrder; if (nodeIsAggregate && fieldIsEnumOrdering) { return(Left.BuildQuery(context)); } // End workaround var result = new ResourceDataColumn { FieldId = field.Instance, NodeId = queryNode.NodeId }; return(result); }
/// <summary> /// This is a hacky function to convert condition which under aggregated node and operator is IsNull /// for normal query, if the condition is under aggregated node, the queryBuilder will add this condition to child aggregate table. /// however for IsNull condition is little special, it should be in main where clause and the condition node should be current aggreagate node id. /// </summary> /// <param name="conditions">structuredQuery conditions</param> /// <param name="aggregateEntity">current aggregated entity</param> private void ConvertConditionExpressionToAggregate(List <QueryCondition> conditions, AggregateEntity aggregateEntity) { if (conditions.Any(c => c.Operator == ConditionType.IsNull && c.Expression is ResourceDataColumn && MatchGroupedEntityNode(((ResourceDataColumn)c.Expression).NodeId, aggregateEntity.GroupedEntity) )) { foreach (QueryCondition condition in conditions) { ResourceDataColumn resourceExpression = condition.Expression as ResourceDataColumn; if (resourceExpression != null) { AggregateExpression aggregateExpression = new AggregateExpression { AggregateMethod = AggregateMethod.Min, NodeId = aggregateEntity.NodeId, Expression = resourceExpression }; condition.Expression = aggregateExpression; } } } }
/// <summary> /// Gets the type of the column database. /// </summary> /// <param name="sourceQuery">The source query.</param> /// <param name="selectColumn">The select column.</param> /// <returns></returns> private static DatabaseType GetColumnDatabaseType(StructuredQuery sourceQuery, SelectColumn selectColumn) { DatabaseType columnType = DatabaseType.UnknownType; var field = selectColumn.Expression as ResourceDataColumn; if (field != null) { ResourceDataColumn dataField = field; if (dataField.CastType != null && dataField.CastType.GetDisplayName( ) != DatabaseType.UnknownType.GetDisplayName( )) { columnType = dataField.CastType; } else { columnType = GetFieldDataType(dataField.FieldId); } } else if (selectColumn.Expression is StructureViewExpression) { columnType = DatabaseType.StructureLevelsType; } else { var expression = selectColumn.Expression as CalculationExpression; if (expression != null) { //TODO hack now if (expression.DisplayType != null && expression.DisplayType != DatabaseType.UnknownType) { return(expression.DisplayType); } } else { var reference = selectColumn.Expression as ColumnReference; if (reference != null) { ColumnReference columnReference = reference; // Sanity check. Prevent infinite recursion if (columnReference.ColumnId != selectColumn.ColumnId) { SelectColumn referencedColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnReference.ColumnId); columnType = GetColumnDatabaseType(sourceQuery, referencedColumn); } } else { var aggregateExpression = selectColumn.Expression as AggregateExpression; if (aggregateExpression != null) { if (aggregateExpression.Expression is StructureViewExpression) { columnType = DatabaseType.StructureLevelsType; } else { columnType = (aggregateExpression.Expression is ResourceDataColumn) ? ((ResourceDataColumn)(aggregateExpression.Expression)).CastType : DatabaseType.StringType; } } else if (selectColumn.Expression is IdExpression) { columnType = DatabaseType.IdentifierType; } } } } return(columnType); }
/// <summary> /// Remove any columns from a report that are not required to achieve a rollup result. /// </summary> /// <remarks> /// If columns get removed here then the query optimiser will later remove various joins. /// This can affect whether some rows are repeated. /// Some aggregate types (e.g. max/min) are unaffected by this. /// Some types (e.g. Count) are very affected by this, so we replace columns with simpler ones, rather than removing them completely. /// Some types (e.g. Sum) in principle could be affected by this, but in practice are OK because they will reference the relationship branch that is relevant to them anyway. /// </remarks> /// <param name="query">The original query.</param> /// <param name="clientAggregate">Aggregate settings that are used to determine what columns are used.</param> /// <returns>A clone of the query, with unused columns removed.</returns> public static StructuredQuery RemoveUnusedColumns(StructuredQuery query, ClientAggregate clientAggregate, bool supportQuickSearch = false) { StructuredQuery queryCopy = query.DeepCopy( ); // Determine columns that are used by the rollup HashSet <Guid> referencedColumns = new HashSet <Guid>(clientAggregate.AggregatedColumns.Select(a => a.ReportColumnId) .Concat(clientAggregate.GroupedColumns.Select(g => g.ReportColumnId))); // Also include columns that are referenced by analyzer conditions foreach (QueryCondition condition in query.Conditions) { ColumnReference colRefExpr = condition.Expression as ColumnReference; if (colRefExpr == null) { continue; } referencedColumns.Add(colRefExpr.ColumnId); } // Ensure that the inner report returns at least something. (This will typically be the ID column). if (referencedColumns.Count == 0 && query.SelectColumns.Count > 0) { referencedColumns.Add(query.SelectColumns [0].ColumnId); } // There are two types of optimisations. Either we can just pull out all unused columns, and let the structured query optimiser // remove the subsequent relationship joins - which is OK for things like max & min in particular. // But in some cases (e.g. for Count) we need to ensure we capture all relationships to get the true fanout. // This is safer, but less efficient. bool strictlyMaintainRowPresence = clientAggregate.AggregatedColumns.Any(ag => ag.AggregateMethod == AggregateMethod.Count); if (!strictlyMaintainRowPresence) { // Remove all unused columns queryCopy.SelectColumns.RemoveAll(column => !referencedColumns.Contains(column.ColumnId)); } else { // Visit each column and determine if it can be removed, or converted to a simpler type List <SelectColumn> columns = queryCopy.SelectColumns; List <Guid> toRemove = new List <Guid>( ); for (int i = 0; i < columns.Count; i++) { SelectColumn column = columns [i]; if (referencedColumns.Contains(column.ColumnId)) { continue; } // Replace field lookups with an equivalent ID column .. to maintain the relationship join, but remove the field join. ResourceDataColumn fieldColumnExpr = column.Expression as ResourceDataColumn; if (fieldColumnExpr != null) { // TODO: we could delete this column entire IF the entire relationship path to it is 'to one' relationships, without any advance properties to enforce rows. // UPDATE: for quick serach purpose, the column expression should be ResourceDataColumn but skip in select clause. // however for performance reasons, if without quick search, still change to IdExpression if (supportQuickSearch) { column.IsHidden = true; } else { column.Expression = new IdExpression { NodeId = fieldColumnExpr.NodeId } }; continue; } // Remove aggregate expressions if they don't have group-bys. (A group-by would cause the aggregate to return more than one row). AggregateExpression aggExpr = column.Expression as AggregateExpression; if (aggExpr != null) { AggregateEntity aggNode = StructuredQueryHelper.FindNode(queryCopy.RootEntity, aggExpr.NodeId) as AggregateEntity; if (aggNode != null) { if (aggNode.GroupBy == null || aggNode.GroupBy.Count == 0) { toRemove.Add(column.ColumnId); } } } // Would be nice to remove calculated columns .. but probably too risky } queryCopy.SelectColumns.RemoveAll(column => toRemove.Contains(column.ColumnId)); } // Remove any obsolete order-by instructions queryCopy.OrderBy.RemoveAll(orderBy => { ColumnReference colRefExpr = orderBy.Expression as ColumnReference; if (colRefExpr == null) { return(false); } bool colStillPresent = queryCopy.SelectColumns.Any(column => column.ColumnId == colRefExpr.ColumnId); return(!colStillPresent); }); return(queryCopy); } }
protected override ScalarExpression OnBuildQuery(QueryBuilderContext context) { ScalarExpression arg = Argument.BuildQuery(context); if (ResultType.Type == DataType.Entity) { return(arg); } // Special case for AggregateExpression, because if we aggregate a choicefield, then it presents // as an Entity type, therefore requesting this cast - but the query builder wants to receive the aggregate expression directly. if (arg is EDC.ReadiNow.Metadata.Query.Structured.AggregateExpression) { var result = new MutateExpression { Expression = arg }; switch (ResultType.Type) { case DataType.String: result.MutateType = MutateType.DisplaySql; break; case DataType.Bool: result.MutateType = MutateType.BoolSql; break; default: throw new InvalidOperationException(ResultType.Type.ToString( )); } return(result); } // Just refer to the node var queryNode = context.GetNode(Argument); switch (ResultType.Type) { case DataType.String: var nameResult = new ResourceDataColumn { FieldId = "core:name", NodeId = queryNode.NodeId }; return(nameResult); case DataType.Bool: var boolResult = new CalculationExpression { Operator = CalculationOperator.IsNull, Expressions = new List <ScalarExpression> { new IdExpression { NodeId = queryNode.NodeId } } }; return(boolResult); default: throw new InvalidOperationException(ResultType.Type.ToString()); } }
public HttpResponseMessage <ReportDataDefinition> Query([FromBody] JsonStructuredQuery jsonQuery) { if (jsonQuery == null) { throw new WebArgumentNullException("jsonQuery"); } if (jsonQuery.Root == null) { throw new WebArgumentNullException("jsonQuery.Root"); } try { var query = new StructuredQuery( ); var nodeIds = new Dictionary <string, Guid>( ); string rootAs = jsonQuery.Root.As ?? ""; nodeIds.Add(rootAs, Guid.NewGuid( )); query.RootEntity = new ResourceEntity { EntityTypeId = WebApiHelpers.GetId(jsonQuery.Root.Id), NodeId = nodeIds[rootAs] }; query.RootEntity.RelatedEntities.AddRange(RelatedEntities(jsonQuery.Root.Related, nodeIds)); query.SelectColumns.Add(new SelectColumn { ColumnId = Guid.NewGuid( ), ColumnName = "_Id", IsHidden = true, Expression = new IdExpression { NodeId = query.RootEntity.NodeId } }); if (jsonQuery.Selects != null) { foreach (JsonSelectInQuery f in jsonQuery.Selects) { if (!string.IsNullOrEmpty(f.Field) && f.Field.ToLower( ) == "_id" && f.On != null) { if (f.On != null) { query.SelectColumns.Add(new SelectColumn { ColumnId = Guid.NewGuid( ), ColumnName = f.DisplayAs ?? f.On + "_Id", IsHidden = true, Expression = new IdExpression { NodeId = nodeIds[f.On] } }); } } else { var field = new ResourceDataColumn { FieldId = WebApiHelpers.GetId(f.Field), NodeId = nodeIds[f.On ?? rootAs] }; string name = field.FieldId.Entity.Cast <Resource>( ).Name; var sc = new SelectColumn { ColumnId = Guid.NewGuid( ), DisplayName = f.DisplayAs ?? name, ColumnName = name, Expression = field }; query.SelectColumns.Add(sc); } } } if (jsonQuery.Conds != null) { foreach (JsonCondition c in jsonQuery.Conds) { var qc = new QueryCondition { Operator = GetConditionOperator(c.Operation), Argument = new TypedValue(c.Value) }; if (!string.IsNullOrEmpty(c.Expression.Field) && c.Expression.Field.ToLower( ) == "_id") { qc.Expression = new IdExpression { NodeId = nodeIds[c.Expression.On ?? rootAs] }; } else { qc.Expression = new ResourceDataColumn { FieldId = WebApiHelpers.GetId(c.Expression.Field), NodeId = nodeIds[c.Expression.On ?? rootAs] }; } query.Conditions.Add(qc); } } QueryResult result = Factory.QueryRunner.ExecuteQuery(query, new QuerySettings { SecureQuery = true }); // debug LogResult(result); return(new HttpResponseMessage <ReportDataDefinition>(PackReportResponse(result))); } catch (HttpResponseException) { throw; } catch (PlatformSecurityException) { throw; } catch (Exception e) { if (e is ArgumentException) // would be better if there was a more specific exception for 'not found' { throw new HttpResponseException(HttpStatusCode.NotFound); } throw; } }