public virtual bool QueryFilterIsSupported(MappedSearchRequest request)
{
if (string.IsNullOrEmpty(request.TextFilter))
{
return(true);
}
// we can only support the text search if its searching the table we are summarizing by
var queryColumns = request.TextFilterColumns;
if ((queryColumns == null || !queryColumns.Any()) && _constants.TextSearchColumnId > 0)
{
// set queryColumns to the default column
var defaultTextSearchColumn = _columnProvider.GetColumnMapping(1, _constants.TextSearchColumnId);
queryColumns = new List <ReportColumnMapping>()
{
defaultTextSearchColumn
};
}
foreach (var column in queryColumns)
{
if (column.KnownTable != request.SummarizeByColumn.KnownTable)
{
return(false);
}
}
return(true);
}
protected override void BuildGroupBy(
SelectQuery query,
ReportColumnMapping groupByColumn,
MappedSearchRequest request)
{
base.BuildGroupBy(query, request.SummarizeByColumn, request);
}
// Restrict
protected override List <ReportColumnMapping> RestrictColumns(MappedSearchRequest request)
{
var allColumns = request.SelectedAndDependantColumns;
// only non claculated columns in this table
var result = allColumns.Where(x =>
x.KnownTable == _fromTableName &&
!QueryHelpers.IsCalculatedColumn(x)
)
// remove the _C column used for getting group counts
.Where(x => QueryHelpers.GetFieldName(x) != _constants.CountKeyAlias).ToList();
if (_primaryKeyColumnId > 0 && result.All(x => x.Id != _primaryKeyColumnId))
{
result.Add(QueryHelpers.GetColumnMapping(_primaryKeyColumnId));
}
// add the currency key column if its in this table
if (result.All(x => x.Id != _constants.CurrencyColumnId))
{
var currencyCol = base.GetCurrencyColumn();
if (currencyCol != null && currencyCol.KnownTable == _fromTableName)
{
result.Add(currencyCol);
}
}
return(result);
}
// Build
public override Query Build(MappedSearchRequest request)
{
var result = new Query();
using (new DebugTimer("SearchQueryBuilder.Build"))
{
Dictionary <string, CommonTableExpression> cteDict;
AddCommonTableExpressions(request, result, out cteDict);
var selectQuery = new SelectQuery();
result.SelectQuery = selectQuery;
var restrictedColumns = RestrictColumns(request);
BuildSelect(selectQuery, restrictedColumns, RestrictSort(request), RestrictGroupBy(request), request);
BuildFrom(selectQuery, restrictedColumns, request.GroupByColumn, request.SortByColumn, request);
BuildGroupBy(selectQuery, RestrictGroupBy(request), request);
BuildWhere(selectQuery, request.TextFilter, RestrictFilters(request, isWhere: true), request);
BuildHaving(selectQuery, RestrictFilters(request, isWhere: false), request.GroupByColumn, request);
BuildOrderBy(selectQuery, RestrictSort(request, forOrderBy: true), request);
BuildPaging(selectQuery, request);
RemoveUnusedCommonTableExpressions(result, cteDict);
AddParameters(result);
}
return(result);
}
// Restrict
protected override List <ReportColumnMapping> RestrictColumns(MappedSearchRequest request)
{
var result = base.RestrictColumns(request);
result = RemoveDateColumnsIfNonDateQuery(request, result);
return(result);
}
public override void SelectGroupKey(SelectQuery query, MappedSearchRequest request)
{
if (QueryHelpers.IsStatsColumn(request.GroupByColumn))
{
// we cannot group by a stats column, so we need to group by the column stats join onto
// get the stats relationship to the data join table
var statsRelationship =
_tableMappings.GetAllTableRelationships()
.FirstOrDefault(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping);
if (statsRelationship != null)
{
var dataTable = statsRelationship.Table1.TableType == TableType.Data
? statsRelationship.Table1
: statsRelationship.Table2;
var primaryKeyColumn = _columnProvider.Find(_constants.DataSourceId, dataTable.KnownTableName,
dataTable.PrimaryKey, null);
if (primaryKeyColumn != null && primaryKeyColumn.Count == 1)
{
var groupCol = primaryKeyColumn.First();
query.Select(dataTable.Alias, groupCol.FieldName, _constants.GroupKeyAlias, Aggregate.Min);
}
}
}
else
{
base.SelectGroupKey(query, request);
}
}
// Restrict
protected override List <ReportColumnMapping> RestrictColumns(MappedSearchRequest request)
{
using (new DebugTimer("DateStatsCteQueryBuilder.Build"))
{
var selectedColumnsPlusFilters = request.SelectedAndDependantColumns.ToList();
// remove NON stats columns, action stats columns & calculated stats columns
var result = selectedColumnsPlusFilters.Where(IsRawStatColumn).ToList();
foreach (var col in _foreignKeyColumnIds)
{
if (result.All(x => x.Id != col))
{
result.Add(QueryHelpers.GetColumnMapping(col));
}
}
AddColumnsForGroupBy(request, result);
if (result.All(x => x.Id != _constants.DateStatColumnId))
{
result.Add(QueryHelpers.GetColumnMapping(_constants.DateStatColumnId));
}
var currency = GetCurrencyColumn();
if (currency != null)
{
result.Add(currency);
}
return(result);
}
}
public MappedSearchRequest Map(SearchRequest request)
{
var result = new MappedSearchRequest()
{
DateEnd = request.DateEnd,
DateStart = request.DateStart,
DateRangeType = request.DateRangeType,
DebugMode = request.DebugMode,
GetCount = request.GetCount,
PageIndex = request.PageIndex,
PageSize = request.PageSize,
TextFilter = request.TextFilter ?? request.Query, // TODO: Remove Query.
TemporalAggregation = request.TemporalAggregation,
ExcludeRecordsWithNoStats = request.ExcludeRecordsWithNoStats,
SortDescending = request.SortDescending,
};
result.GroupByColumn = GetColumnMapping(request.GroupByColumn);
result.SummarizeByColumn = GetColumnMapping(request.SummarizeByColumn);
result.SortByColumn = GetColumnMapping(request.SortByColumn);
result.SelectedColumns = GetColumnMappings(request.SelectedColumns);
result.TextFilterColumns = GetColumnMappings(request.TextFilterColumns);
result.Filters = GetMappedFilters(request.Filters);
result.DependantColumns = GetDependantColumnMappings(result);
return(result);
}
protected virtual List <ReportColumnMapping> RemoveDateColumnsIfNonDateQuery(MappedSearchRequest request, List <ReportColumnMapping> result)
{
if (!IsDateQuery(request))
{
result = result.Where(x => !IsDateColumn(x)).ToList();
}
return(result);
}
public List <ReportColumnMapping> GetAllTransposeStatsColumns(MappedSearchRequest request)
{
var allTransposeStatsColumns = request.SelectedAndDependantColumns
.Where(x => QueryHelpers.IsTransposeStatColumn(x))
.ToList();
return(allTransposeStatsColumns);
}
protected override ReportColumnMapping RestrictSort(MappedSearchRequest request, bool forOrderBy = false)
{
if (request.SortByColumn != null)
{
// remove stats columns
return(QueryHelpers.IsStatsColumn(request.SortByColumn) ? null : request.SortByColumn);
}
return(null);
}
protected virtual void AddColumnsForGroupBy(MappedSearchRequest request, List <ReportColumnMapping> result)
{
var column = RestrictGroupBy(request);
if (result.All(x => x.Id != column.Id))
{
result.Add(column);
}
}
protected override ReportColumnMapping RestrictSort(MappedSearchRequest request, bool forOrderBy = false)
{
if (request.SortByColumn != null)
{
return(request.SortByColumn.Id > 0 &&
IsRawStatColumn(request.SortByColumn)
? request.SortByColumn : null);
}
return(null);
}
// Restrict
protected override List <ReportColumnMapping> RestrictColumns(MappedSearchRequest request)
{
var result = request.SelectedColumns.ToList();
result.AddRange(request.DependantColumns);
// remove stats columns
result = result.Where(x => !QueryHelpers.IsStatsColumn(x)).ToList();
return(result);
}
public override Column GetColumnSelector(ReportColumnMapping col, MappedSearchRequest request, bool dontAggregate = false, bool useFieldAlias = false)
{
var result = _dataSourceComponents.QueryBuilderBase.GetColumnSelector(col, request, dontAggregate: dontAggregate, useFieldAlias: useFieldAlias);
if (result.Field.Name.StartsWith("_C_"))
{
result.Field.Name = _constants.CountKeyAlias;
}
return(result);
}
private void BuildTextSearchWhereFIlters(
SelectQuery query,
string queryText,
MappedSearchRequest request)
{
if (!string.IsNullOrEmpty(queryText) && CanFilterOnText())
{
var canUseFullText = false;
var queryColumns = request.TextFilterColumns;
if ((queryColumns == null || !queryColumns.Any()) && _constants.TextSearchColumnId > 0)
{
// set queryColumns to the default column
var defaultTextSearchColumn = _columnProvider.GetColumnMapping(_constants.DataSourceId, _constants.TextSearchColumnId);
queryColumns = new List <ReportColumnMapping>()
{
defaultTextSearchColumn
};
canUseFullText = _constants.FullTextSearchEnabled;
}
if (!queryColumns.Any())
{
// do nothing
return;
}
// Add Text Search
if (queryText.StartsWith("*"))
{
queryText = queryText.Substring(1); // remove the *
canUseFullText = false;
}
if (canUseFullText)
{
// we are using the default column (and there's only 1)
var tableMapping = _tableMappings.GetTableMapping(queryColumns.First().KnownTable);
query.WhereColumnContains(tableMapping.Alias, queryColumns.First().FieldName, queryText, ContainsMode.FreeText);
}
else
{
var filters = new WhereFilterCollection();
foreach (var column in queryColumns)
{
var tableMapping = _tableMappings.GetTableMapping(column.KnownTable);
filters.WhereColumnLike(tableMapping.Alias, column.FieldName, queryText, LikeMode.FreeText);
}
query.WhereCollection(Combine.Or, filters);
}
}
}
protected override string GetFieldAlias(ReportColumnMapping column, MappedSearchRequest request)
{
var result = QueryHelpers.GetFieldName(column);
// to allow selecting both min and max together (not working with custom columns - its complicated)
// depends on DefaultStatsQueryBuilder.GetColumnSelector()
// if (RestrictGroupBy(request).Id != column.Id && result != _constants.CountKeyAlias)
// {
// result += "_" + column.FieldAggregationMethod;
// }
return(result);
}
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var fromTableName = request.SummarizeByColumn.KnownTable;
var fromTable = _tableMappings.GetTableMapping(fromTableName);
query.From(fromTable.DbTableName, fromTable.Alias);
}
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
// idea here is to be able to get a key which can be joined on, so select from the table we're building the CTE from
// then join on the tables required to get to the join key (which can be used as the group key)
_rootTableName = request.GroupByColumn.KnownTable;
var fromTable = _tableMappings.GetTableMapping(_fromTableName);
var addedTables = new List <string>();
if (CanJoinTables(_fromTableName, _rootTableName))
{
query.From(fromTable.DbTableName, fromTable.Alias);
}
else
{
var graphBuilder = new TableRelationshipGraphBuilder();
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _rootTableName);
// todo : use path
//var path = graphBuilder.GetPathFromTableToRoot(relationshipGraph, _fromTableName);
var distance = graphBuilder.GetDistance(relationshipGraph, _fromTableName);
query.From(fromTable.DbTableName, fromTable.Alias);
string currentTableName = fromTable.KnownTableName;
while (distance > 1)
{
var potentialNodes = graphBuilder.GetByDistance(relationshipGraph, distance - 1);
var parentNode = potentialNodes.SingleOrDefault(x => x.Relations.Any(y => y.TableName == currentTableName));
if (parentNode == null)
{
throw new Exception(string.Format("Could not find a relationship between {0} and {1}", currentTableName, relationshipGraph.TableName));
}
JoinTables(query, parentNode.TableName, currentTableName);
currentTableName = parentNode.TableName;
distance--;
addedTables.Add(parentNode.TableName);
if (CanJoinTables(_fromTableName, currentTableName) && CanJoinTables(currentTableName, _rootTableName))
{
break;
}
}
}
}
protected override ReportColumnMapping RestrictSort(MappedSearchRequest request, bool forOrderBy = false)
{
if (forOrderBy)
{
return(request.SortByColumn);
}
if (request.SortByColumn != null)
{
// remove data columns
return(request.SortByColumn.Id > 0 && QueryHelpers.IsStatsColumn(request.SortByColumn) ? request.SortByColumn : null);
}
return(null);
}
/// <summary>
/// Gets the Column with alias for non calculated columns
/// Gets the Column without alias for calculated columns and handles the conversion of calculated columns to sql
/// For calculated colums, the column will be expanded to a full formula
/// Sets the result on the column mapping so that it doesnt need to be re-calculated - this works with the cache
/// </summary>
public virtual Column GetColumnSelector(ReportColumnMapping col, MappedSearchRequest request, bool dontAggregate = false, bool useFieldAlias = false)
{
var cached = col.CalculatedValues.GetColumnTableAndField(dontAggregate, useFieldAlias);
if (cached != null)
{
return(new Column(cached[0], cached[1]));
}
var result = GetColumSelectorInternal(col, dontAggregate, useFieldAlias);
col.CalculatedValues.SetColumnTableAndField(dontAggregate, useFieldAlias, result.TableAlias, result.Field.Name);
return(result);
}
protected override List <MappedSearchRequestFilter> RestrictFilters(MappedSearchRequest request, bool isWhere)
{
if (request.Filters == null || !request.Filters.Any())
{
return(request.Filters);
}
// remove stats columns
var result = request.Filters.Where(x => !QueryHelpers.IsStatsColumn(x.Column));
// remove aggregated columns for where filters and non aggregated columns for having filters
result = result.Where(x => x.ProcessBeforeAggregation == isWhere);
return(result.ToList());
}
public virtual bool FiltersAreSupported(MappedSearchRequest request)
{
if (request.Filters != null)
{
foreach (var filter in request.Filters)
{
if (filter.Column.KnownTable != request.SummarizeByColumn.KnownTable)
{
return(false);
}
}
}
return(true);
}
protected override List <MappedSearchRequestFilter> RestrictFilters(MappedSearchRequest request, bool isWhere)
{
if (request.Filters == null || !request.Filters.Any())
{
return(request.Filters);
}
// remove aggregated columns for where filters and non aggregated columns for having filters
var result = request.Filters.Where(x => x.ProcessBeforeAggregation == isWhere);
if (!isWhere)
{
result = result.Where(x => !x.Column.IsCalculated);
}
return(result.ToList());
}
public virtual void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var tables = request.SelectedAndDependantColumns.Select(x => x.KnownTable).ToList();
tables.Add(groupByColumn.KnownTable);
tables = tables.Distinct().ToList();
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !tables.Contains(currencyColumn.KnownTable))
{
tables.Add(currencyColumn.KnownTable);
}
var rootTableMapping = _tableMappings.GetTableMapping(_constants.RootTableName);
query.From(rootTableMapping.DbTableName, rootTableMapping.Alias);
var graphBuilder = new TableRelationshipGraphBuilder();
// bulild a graph of the table relationships to calculate the distance from the root table
// include missing tables needed for joins
// order the tables by distance
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
graphBuilder.TrimToTables(relationshipGraph, tables);
tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList();
tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList();
// dictionary of TableName, IsAggregated for tables which have been added to the query
var addedTables = new Dictionary <string, bool> {
{ _constants.RootTableName, false }
};
foreach (var table in tables.Distinct())
{
AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph);
}
}
// Build
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var graphBuilder = new TableRelationshipGraphBuilder();
var dataJoinTable = _dataSourceComponents.StatsQueryBuilder.GetDataJoinTable(request.GroupByColumn, statsRelationships, graphBuilder);
//-- Find out which stats table or view we need to query to satisfy the requested temporal
// aggregation and type of date range.
var statsTableName = GetStatsTableName(
_statsTableName,
dataJoinTable,
request.TemporalAggregation,
request.DateRangeType,
request.DateStart,
request.DateEnd);
query.From(statsTableName, _statsTableAlias);
JoinTables(query, dataJoinTable, _statsTableName);
// join up to the root table
var addedTables = new Dictionary <string, bool> {
{ dataJoinTable, false }
};
_dataSourceComponents.StatsQueryBuilder.AddDataTableJoinsUpToRoot(query, dataJoinTable, graphBuilder, addedTables);
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !addedTables.ContainsKey(currencyColumn.KnownTable))
{
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
AddFromTableJoin(query, groupByColumn, currencyColumn.KnownTable, ref addedTables, graphBuilder, relationshipGraph);
}
}
public virtual string GetStatsTableName(string groupTable, MappedSearchRequest request)
{
// Do we need to query the daily or hourly tables?
if (IsDateQuery(request))
{
// This request requires us to query the daily or hourly tables. The DateStatsCteQueryBuilder
// will take care of building a CTE that does that and will call it "dateStats". It's the view
// that will contain the stats.
return("dateStats");
}
// We don't need to query the daily or hourly stats tables. I.e. this is a
// straighforward request to query lifetime stats from the lifetime stats tables.
// Get the name of the Lifetime stats table we need to query. If we've been asked to return stats data
// grouped by AdGroup, we'll have to query the AdGroupLifetime table. But for all other groupings,
// we can get the data we need from the CampaignLifetime table.
return(GetStatsTableName(_statsTableName, groupTable, request.TemporalAggregation, request.DateRangeType, request.DateStart, request.DateStart));
}
// Restrict
protected override List <ReportColumnMapping> RestrictColumns(MappedSearchRequest request)
{
var allTransposeStatColumns = _dataSourceComponents.StatsQueryBuilder.GetAllTransposeStatsColumns(request)
// calculations are not performed in the transpose stats query
.Where(x => !x.IsCalculated);
var result = new List <ReportColumnMapping>();
// add any columns needed by calculations
foreach (var found in allTransposeStatColumns)
{
if (result.All(x => x.FieldName != found.FieldName))
{
result.Add(found);
}
}
return(result);
}
protected override List <CommonTableExpression> BuildCommonTableExpressions(MappedSearchRequest request)
{
var result = new List <CommonTableExpression>();
// build the date stats CTE
if (IsDateQuery(request))
{
using (new DebugTimer("StatsQueryBuilder.BuildCommonTableExpressions - date"))
{
var dateStatsQuery = _dataSourceComponents.DateStatsQueryBuilder.Build(request);
var cte = new CommonTableExpression
{
Alias = _dateStatsTableAlias,
Query = dateStatsQuery.SelectQuery
};
result.Add(cte);
}
}
// bulid the transpose stats CTE
var allTransposeStatsColumns = GetAllTransposeStatsColumns(request);
if (allTransposeStatsColumns != null && allTransposeStatsColumns.Any())
{
using (new DebugTimer("StatsQueryBuilder.BuildCommonTableExpressions - transpose"))
{
var transposeCteQuery = _dataSourceComponents.TransposeStatsQueryBuilder.Build(request);
var transposeStatsCte = new CommonTableExpression
{
Alias = _transposeStatsTableAlias,
Query = transposeCteQuery.SelectQuery
};
result.Add(transposeStatsCte);
}
}
return(result);
}
protected virtual void HandleSelectCountColumn(MappedSearchRequest request, Column field)
{
var columnTable = _tableMappings.GetAllTables().FirstOrDefault(x => x.Alias == field.TableAlias);
if (columnTable == null ||
columnTable.KnownTableName == request.GroupByColumn.KnownTable ||
!GroupByIsChildTable(field.TableAlias, request.GroupByColumn) ||
columnTable.TableType == TableType.Stats)
{
field.TableAlias = null;
field.Field.Name = "1";
// when there are multiple stats in a date range, inherit the count from the date stats table
if (columnTable != null && columnTable.TableType == TableType.Stats && IsDateQuery(request))
{
field.TableAlias = "Stats";
field.Field.Name = "_C";
}
}
}