public static WhereFilterCollection WhereColumnLike(this WhereFilterCollection query, string leftTableAlias, string leftField, string text, LikeMode mode = LikeMode.Default)
{
query.Add(new ColumnLikeWhereFilter()
{
LeftColumn = new Column(leftTableAlias, leftField),
LikeMode = mode,
Text = text
});
return(query);
}
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>();
}
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);
}
}
}
public static WhereFilterCollection WhereColumnColumn(this WhereFilterCollection query, string leftTableAlias, string leftField, Compare comparison, string rightTableAlias, string rightField)
{
var whereFilter = new ColumnColumnWhereFilter()
{
LeftColumn = new Column(leftTableAlias, leftField),
Operator = comparison,
RightColumn = new Column(rightTableAlias, rightField)
};
query.Add(whereFilter);
return(query);
}
public static WhereFilterCollection WhereColumnValue(this WhereFilterCollection query, string leftTableAlias, string leftField, Compare comparison, string rightValue, DbType rightType, string parameterAlias = null, string isNullValue = null)
{
var whereFilter = new ColumnValueWhereFilter()
{
LeftColumn = new Column(leftTableAlias, leftField),
Operator = comparison,
RightValue = new LiteralValue(rightValue, rightType),
ParameterAlias = parameterAlias,
IsNullValue = isNullValue
};
query.Add(whereFilter);
return(query);
}
public static WhereFilterCollection WhereColumnIn(this WhereFilterCollection query, string leftTableAlias, string leftField, List <string> rightValues, DbType rightType, string parameterAlias = null)
{
var where = new ColumnInWhereFilter()
{
LeftColumn = new Column(leftTableAlias, leftField),
ParameterAlias = parameterAlias,
RightValues = new List <LiteralValue>()
};
foreach (var v in rightValues)
{
where.RightValues.Add(new LiteralValue(v, rightType));
}
query.Add(where);
return(query);
}
public static WhereFilterCollection WhereColumnLike(this WhereFilterCollection query, Table leftTable, string leftField, string text, LikeMode mode = LikeMode.Default)
{
query.WhereColumnLike(leftTable.Alias, leftField, text, mode);
return(query);
}
public static WhereFilterCollection Where(this WhereFilterCollection query, string sql)
{
query.Add(new SqlWhereFilter(sql));
return(query);
}
public static WhereFilterCollection WhereColumnColumn(this WhereFilterCollection query, Table leftTable, string leftField, Compare comparison, Table rightTable, string rightField)
{
query.WhereColumnColumn(leftTable.Alias, leftField, comparison, rightTable.Alias, rightField);
return(query);
}
public static WhereFilterCollection WhereColumnValue(this WhereFilterCollection query, string leftTableAlias, string leftField, Compare comparison, DateTime rightValue, string parameterAlias = null, DateTime?isNullValue = null)
{
query.WhereColumnValue(leftTableAlias, leftField, comparison, rightValue.ToString("yyyy-MM-dd HH:mm:ss"), DbType.DateTime2, parameterAlias, NullableToString(isNullValue));
return(query);
}
public static WhereFilterCollection WhereColumnValue(this WhereFilterCollection query, Table leftTable, string leftField, Compare comparison, DateTime rightValue, string parameterAlias = null, DateTime?isNullValue = null)
{
query.WhereColumnValue(leftTable.Alias, leftField, comparison, rightValue, parameterAlias, isNullValue);
return(query);
}
public static WhereFilterCollection WhereColumnValue(this WhereFilterCollection query, string leftTableAlias, string leftField, Compare comparison, string rightValue, string parameterAlias = null)
{
query.WhereColumnValue(leftTableAlias, leftField, comparison, rightValue, DbType.String, parameterAlias);
return(query);
}
public static WhereFilterCollection WhereColumnValue(this WhereFilterCollection query, string leftTableAlias, string leftField, Compare comparison, decimal rightValue, string parameterAlias = null, decimal?isNullValue = null)
{
query.WhereColumnValue(leftTableAlias, leftField, comparison, rightValue.ToString(), DbType.Decimal, parameterAlias, NullableToString(isNullValue));
return(query);
}
public static WhereFilterCollection WhereCollection(this WhereFilterCollection query, Combine groupingOperator, WhereFilterCollection collection)
{
collection.GroupingOperator = groupingOperator;
query.Add(collection);
return(query);
}
// WHERE COLLECTION
public static SelectQuery WhereCollection(this SelectQuery query, Combine groupingOperator, WhereFilterCollection collection)
{
query.WhereFilters.WhereCollection(groupingOperator, collection);
return(query);
}
/// <param name="filters">The filters to apply</param>
/// <param name="isPostFilter">Indicates that the filter is being applied after aggregations, in the final select - it will use column aliases rather than fields</param>
/// <param name="matchAll">All filters must evaluate to true in order to select the row, effectively an AND operator</param>
protected WhereFilterCollection BuildWhereFilters(List <MappedSearchRequestFilter> filters, MappedSearchRequest request, bool isPostFilter = false, bool matchAll = true)
{
if (filters == null || !filters.Any())
{
return(new WhereFilterCollection());
}
var result = new WhereFilterCollection
{
GroupingOperator = matchAll ? Combine.And : Combine.Or
};
foreach (var filter in filters)
{
if (isPostFilter)
{
if (QueryHelpers.GetTableType(filter.Column.KnownTable) == TableType.Data)
{
continue; // do not filter on data columns in the main selector
}
}
else if (QueryHelpers.GetTableType(filter.Column.KnownTable) != TableType.Data)
{
continue;
}
var field = GetColumnSelector(filter.Column, request, dontAggregate: !isPostFilter);
if (isPostFilter)
{
var fieldName = GetFieldAlias(filter.Column, request);
field = new Column(string.Empty, fieldName);
}
var compareOperator = MagiQL.DataAdapters.Infrastructure.Sql.QueryHelpers.GetOperator(filter);
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();
}
// todo : how do we decide which columns should be evaluated with null = 0, and which shouldnt... it has performance impact
// for now just fix this for org id
if (!WhereFilterColumnAllowIsNull(filter))
{
isNullValue = null; // dont allow isnull for org id
}
result.WhereColumnValue(field.TableAlias, field.Field.Name, compareOperator, filter.Values[0], filter.Column.DbType, filter.Column.UniqueName, isNullValue);
}
else
{
result.WhereColumnIn(field.TableAlias, field.Field.Name, filter.Values, filter.Column.DbType, filter.Column.UniqueName);
}
}
return(result);
}
