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