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); }