/// <summary>
/// Adds a (SELECT COUNT({countFieldName}) FROM {sqlText} WHERE {subQueryConditions}) {subQueryOperator} {countValue} condition to the query;
/// </summary>
/// <param name="where"></param>
/// <param name="sqlText"></param>
/// <param name="countColumnName"></param>
/// <param name="operator"></param>
/// <param name="countValue"></param>
/// <param name="subQueryWhere"></param>
/// <returns></returns>
public static IDbQueryWhereClause SubQueryCount(this IDbQueryWhereClause where, string sqlText, string countColumnName, SqlOperator @operator, int countValue, Action <IDbQueryWhereClause> subQueryWhere)
{
var sqf = new SqlCountFilter(where.Root, SqlTable.Create(sqlText), countColumnName, @operator, countValue);
var sqfWhere = new DbQueryWhereClause(sqf.SubQueryTable, SqlLogic.And);
subQueryWhere(sqfWhere);
sqfWhere.ApplyTo(sqf.SubQueryFilters);
where.AddFilter(sqf);
return(where);
}
/// <summary>
/// Adds an EXISTS(selectStatement) filter critera.
/// </summary>
/// <param name="where"></param>
/// <param name="innerQuery">The value to render as the inner SELECT statement</param>
/// <returns></returns>
public static IDbQueryWhereClause Exists(this IDbQueryWhereClause where, string sqlText, Action <IDbQueryWhereClause> subQueryFilters)
{
var innerTable = SqlTable.Create(sqlText);
var innerQuery = new SqlSelectBuilder(innerTable);
var innerWhere = new DbQueryWhereClause(innerTable, SqlLogic.And);
innerQuery.Columns.Add(new SqlRawColumn("1"));
subQueryFilters(innerWhere);
innerWhere.ApplyTo(innerQuery.Filters);
where.AddFilter(new Sql.SqlExistFilter(innerQuery));
return(where);
}
/// <summary>
/// Adds a {columnName} IN | NOT IN (SELECT {subQueryColumnName} FROM {sqlText} WHERE {SubQueryConditions})
/// </summary>
/// <param name="where"></param>
/// <param name="columnName"></param>
/// <param name="sqlText"></param>
/// <param name="subQueryColumnName"></param>
/// <param name="condition"></param>
/// <param name="subQueryFilters"></param>
/// <returns></returns>
internal static IDbQueryWhereClause FieldSubQuery(IDbQueryWhereClause where, string columnName, string sqlText, string subQueryColumnName, SubQueryMode condition, Action <IDbQueryWhereClause> subQueryFilters)
{
var subQueryFilter = new Csg.Data.Sql.SqlSubQueryFilter(where.Root, SqlTable.Create(sqlText))
{
ColumnName = columnName,
Condition = condition,
SubQueryColumn = subQueryColumnName,
};
var subQueryWhere = new DbQueryWhereClause(subQueryFilter.SubQueryTable, SqlLogic.And);
subQueryFilters(subQueryWhere);
subQueryWhere.ApplyTo(subQueryFilter.SubQueryFilters);
where.AddFilter(subQueryFilter);
return(where);
}
