public SelectQuery(bool selectDistinct = false)
{
SelectDistinct = selectDistinct;
SelectColumns = new List <IColumnSelector>();
TableJoins = new List <ITableJoin>();
GroupByColumns = new List <IGroupByColumn>();
WhereFilters = new WhereFilterCollection();
HavingFilters = new HavingFilterCollection();
OrderByColumns = new List <OrderByColumn>();
}
public static HavingFilterCollection HavingColumnValue(this HavingFilterCollection query, Aggregate aggregate, string leftTableAlias, string leftField, Compare comparison, string rightValue, DbType rightType, string parameterAlias = null, string isNullValue = null)
{
var HavingFilter = new ColumnValueHavingFilter()
{
Aggregate = aggregate,
LeftColumn = new Column(leftTableAlias, leftField),
Operator = comparison,
RightValue = new LiteralValue(rightValue, rightType),
ParameterAlias = parameterAlias,
IsNullValue = isNullValue
};
query.Add(HavingFilter);
return(query);
}
protected HavingFilterCollection BuildHavingFilters(List <MappedSearchRequestFilter> filters, MappedSearchRequest request, bool isPostFilter = false, bool matchAll = true, bool excludeDataFilters = false)
{
if (filters == null || !filters.Any())
{
return(null);
}
var result = new HavingFilterCollection
{
GroupingOperator = matchAll ? Combine.And : Combine.Or
};
foreach (var filter in filters)
{
if (excludeDataFilters && QueryHelpers.GetTableType(filter.Column.KnownTable) == TableType.Data)
{
continue; // dont need to filter out data values - should this be in the CTE builder instead?
}
var field = GetColumnSelector(filter.Column, request, dontAggregate: true);
if (isPostFilter)
{
field = new Column(null, filter.Column.Id.ToString()); // todo : suspect!
}
var compareOperator = MagiQL.DataAdapters.Infrastructure.Sql.QueryHelpers.GetOperator(filter);
var aggregate = MagiQL.DataAdapters.Infrastructure.Sql.QueryHelpers.GetAggregate(filter.Column.FieldAggregationMethod);
if (filter.Values != null && filter.Values.Count == 1)
{
string isNullValue = null;
if (filter.Column.DbType.IsNumericType() && (compareOperator == Compare.Equal || compareOperator == Compare.LessThan || compareOperator == Compare.LessThanOrEqual))
{
isNullValue = 0.ToString();
}
// theres a special case for handling counts, if the referenced table is not a CTE, then we should actually select 1
if (field.Field.Name == _constants.CountKeyAlias)
{
HandleSelectCountColumn(request, field);
}
result.HavingColumnValue(aggregate, field.TableAlias, field.Field.Name, compareOperator, filter.Values[0], filter.Column.DbType, filter.Column.UniqueName, isNullValue);
}
else
{
var collection = new HavingFilterCollection();
foreach (var v in filter.Values)
{
// theres a special case for handling counts, if the referenced table is not a CTE, then we should actually select 1
if (field.Field.Name == _constants.CountKeyAlias)
{
HandleSelectCountColumn(request, field);
}
collection.HavingColumnValue(aggregate, field.TableAlias, field.Field.Name, compareOperator, v, filter.Column.DbType, filter.Column.UniqueName);
}
result.HavingCollection(Combine.Or, collection);
}
}
return(result);
}
public static HavingFilterCollection HavingColumnValue(this HavingFilterCollection query, Aggregate aggregate, string leftTableAlias, string leftField, Compare comparison, int rightValue, string parameterAlias = null, int?isNullValue = null)
{
query.HavingColumnValue(aggregate, leftTableAlias, leftField, comparison, rightValue.ToString(), DbType.Int32, parameterAlias, NullableToString(isNullValue));
return(query);
}
public static HavingFilterCollection HavingColumnValue(this HavingFilterCollection query, Aggregate aggregate, Table leftTable, string leftField, Compare comparison, int rightValue, string parameterAlias = null, int?isNullValue = null)
{
query.HavingColumnValue(aggregate, leftTable.Alias, leftField, comparison, rightValue, parameterAlias, isNullValue);
return(query);
}
public static HavingFilterCollection Having(this HavingFilterCollection query, string sql)
{
query.Add(new SqlHavingFilter(sql));
return(query);
}
public static HavingFilterCollection HavingCollection(this HavingFilterCollection query, Combine groupingOperator, HavingFilterCollection collection)
{
collection.GroupingOperator = groupingOperator;
query.Add(collection);
return(query);
}
public static HavingFilterCollection HavingColumnValue(this HavingFilterCollection query, Aggregate aggregate, string leftTableAlias, string leftField, Compare comparison, DateTime rightValue, string parameterAlias = null)
{
query.HavingColumnValue(aggregate, leftTableAlias, leftField, comparison, rightValue.ToString("yyyy-MM-dd HH:mm:ss"), DbType.DateTime2, parameterAlias);
return(query);
}
public static HavingFilterCollection HavingColumnValue(this HavingFilterCollection query, Aggregate aggregate, string leftTableAlias, string leftField, Compare comparison, string rightValue, string parameterAlias = null)
{
query.HavingColumnValue(aggregate, leftTableAlias, leftField, comparison, rightValue, DbType.String, parameterAlias);
return(query);
}
// Having COLLECTION
public static SelectQuery HavingCollection(this SelectQuery query, Combine groupingOperator, HavingFilterCollection collection)
{
query.HavingFilters.HavingCollection(groupingOperator, collection);
return(query);
}
