public virtual void BuildSelect(SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping sortColumn, ReportColumnMapping groupByColumn, MappedSearchRequest request)
{
var resolution = RestrictResolution(request);
using (new DebugTimer("SearchQueryBuilder.BuildSelect (" + selectedColumns.Count + ")"))
{
query.SelectCount(_constants.CountKeyAlias);
SelectGroupKey(query, request);
if (resolution != TemporalAggregation.Total)
{
query.SelectGroupKey(_constants.DateKeyAlias, 1 + (RequireCurrencyGroupBy(request) ? 1 : 0));
}
if (sortColumn != null && CanSelectRowNumber())
{
//query.SelectRowNumber("_R"); // this can be slow inside the CTE
query.SelectOrderByColumn(_constants.SortKeyAlias); // so just get the value in a known column and get rownumber later
}
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null)
{
var currencyCol = GetColumnSelector(currencyColumn, request);
var aggregate = RequireCurrencyGroupBy(request) ? Aggregate.None : Aggregate.Min;
query.Select(currencyCol.TableAlias, currencyCol.Field.Name, _constants.CurrencyKeyAlias, aggregate);
}
var selectedAliases = new List <string>()
{
_constants.CountKeyAlias, _constants.GroupKeyAlias
};
foreach (var col in selectedColumns)
{
var colName = GetColumnSelector(col, request);
var aggregate = MagiQL.DataAdapters.Infrastructure.Sql.QueryHelpers.GetAggregate(col.FieldAggregationMethod);
if (QueryHelpers.ContainsAggregate(colName.Field.Name))
{
aggregate = Aggregate.None;
}
var fieldAlias = GetFieldAlias(col, request);
// dont request the same column twice
if (selectedAliases.Contains(fieldAlias))
{
continue;
}
// theres a special case for handling counts, if the referenced table is not a CTE, then we should actually select 1
if (colName.Field.Name == _constants.CountKeyAlias)
{
HandleSelectCountColumn(request, colName);
}
query.Select(colName.TableAlias, colName.Field.Name, fieldAlias, aggregate);
selectedAliases.Add(fieldAlias);
}
}
}
public override void BuildSelect(SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping sortColumn, ReportColumnMapping groupByColumn, MappedSearchRequest request)
{
// this was the old way of doing it, when the row number was calculated in the CTE, but had performance issues
// var row = request.SummarizeByColumn == null ? "_R" : "MIN(_R)";
// query.Select(string.Format("ISNULL({0}, 2147483647) as _ROW", row));
// if (request.GetCount)
// {
// query.Select(string.Format("CASE WHEN ROW_NUMBER() OVER(ORDER BY ISNULL({0}, 2147483647) ASC) = {1} THEN COUNT(1) OVER () ELSE 0 END AS _COUNT", row, (request.PageIndex * request.PageSize) + 1));
// }
var sortField = "_SortKey";
var row = request.SummarizeByColumn == null ? sortField : "MIN(" + sortField + ")";
var rowNumberSelect = string.Format("ROW_NUMBER() OVER(ORDER BY ISNULL({0}, {2}) {1})",
row,
request.SortDescending ? "DESC" : "ASC",
request.SortDescending ? request.SortByColumn.DbType.MinValue() : request.SortByColumn.DbType.MaxValue()
);
query.Select(rowNumberSelect + " as _ROW");
if (request.GetCount)
{
query.Select("COUNT(1) OVER () AS _COUNT");
}
foreach (var col in selectedColumns)
{
var aggregate = Aggregate.None;
var columnSelector = GetFieldAlias(col, request);
bool isCustomColumn = col.OrganizationId > 0;
bool isSummarizing = request.SummarizeByColumn != null;
// need to calculate custom columns here, because there may be no stats
if (isSummarizing || isCustomColumn)
{
aggregate = MagiQL.DataAdapters.Infrastructure.Sql.QueryHelpers.GetAggregate(col.FieldAggregationMethod);
if (col.IsCalculated && col.FieldAggregationMethod == FieldAggregationMethod.Average)
{
// when summarizing, the calculated columns need to be calculated from aggregate values
var colName = GetColumnSelector(col, request, dontAggregate: !isSummarizing, useFieldAlias: true);
columnSelector = colName.Field.Name;
}
if (QueryHelpers.ContainsAggregate(columnSelector) || !isSummarizing)
{
aggregate = Aggregate.None;
}
if (isSummarizing && aggregate == Aggregate.Sum)
{
if (col.DbType == DbType.Int32 || col.DbType == DbType.Int16)
{
columnSelector = new ToBigIntFunction().Parse(columnSelector);
}
}
}
query.Select(string.Empty, columnSelector, GetFieldAlias(col, request), aggregate);
}
}
