/// <summary>
/// Creates a FromTerm which represents a sub-query.
/// </summary>
/// <param name="query">A SelectQuery instance representing the sub query</param>
/// <param name="alias">term alias</param>
/// <returns>A FromTerm which represents a sub-query.</returns>
public static FromTerm SubQuery(SelectQuery query, string alias)
{
FromTerm term = new FromTerm();
term.QueryValue = query;
term.Alias = alias;
term.Type = FromTermType.SubQueryObj;
return(term);
}
/// <summary>
/// Creates a FromTerm which represents a UNION.
/// </summary>
public static FromTerm Union(OmUnion union, string alias)
{
FromTerm term = new FromTerm();
term.UnionValue = union;
term.Alias = alias;
term.Type = FromTermType.Union;
return(term);
}
/// <summary>
/// Joins two tables using on an arbitrary join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="conditions">Specifies how the join should be performed</param>
/// <remarks>
/// Use this overload to create complex join conditions.
/// Note that not all <see cref="WhereClause"/> operators and expressions are supported in joins.
/// </remarks>
/// <example>
/// WhereClause condition = new WhereClause(WhereClauseRelationship.Or);
/// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
/// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productName", tOrders), SqlExpression.Field("productName", tProducts), CompareOperator.Equal));
/// query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);
/// </example>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, WhereClause conditions)
{
if (conditions.IsEmpty && type != JoinType.Cross)
{
throw new InvalidQueryException("A join must have at least one condition.");
}
joins.Add(new Join(leftTable, rightTable, conditions, type));
}
/// <summary>
/// Creates a FromTerm which represents a sub-query.
/// </summary>
/// <param name="query">sub-query sql</param>
/// <param name="alias">term alias</param>
/// <returns>A FromTerm which represents a sub-query.</returns>
public static FromTerm SubQuery(string query, string alias)
{
FromTerm term = new FromTerm();
term.StringValue = query;
term.Alias = alias;
term.Type = FromTermType.SubQuery;
return(term);
}
/// <summary>
/// Creates a SqlExpression which represents a field in a database table.
/// </summary>
/// <param name="fieldName">Name of a field</param>
/// <param name="table">The table this field belongs to</param>
/// <returns></returns>
public static OmExpression Field(string fieldName, FromTerm table)
{
OmExpression expr = new OmExpression();
expr.StringValue = fieldName;
expr.ValueCode = ExprValCode.String;
expr.Table = table;
expr.Type = OmExpressionType.Field;
return(expr);
}
/// <summary>
/// Creates a FromTerm which represents a database table or view.
/// </summary>
/// <param name="tableName">Name of the table or view</param>
/// <param name="alias">Alias of the FromTerm</param>
/// <param name="ns1">First table namespace</param>
/// <param name="ns2">Second table namespace</param>
/// <returns>A FromTerm which represents a database table or view</returns>
/// <remarks>Use the <paramref name="ns1"/> parameter to set table database and <paramref name="ns2"/> to set table owner.</remarks>
public static FromTerm Table(string tableName, string alias, string ns1, string ns2)
{
FromTerm term = new FromTerm();
term.StringValue = tableName;
term.Alias = alias;
term.Type = FromTermType.Table;
term.Ns1 = ns1;
term.Ns2 = ns2;
return(term);
}
/// <summary>
/// Joins two tables using on an array join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="conditions">An array of equality condition to be applied on the join</param>
/// <remarks>
/// A logical AND will be applied on the conditions.
/// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3 and ... and condN) ...
/// </remarks>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition[] conditions)
{
WhereClause clause = new WhereClause(WhereRel.And);
foreach (JoinCondition cond in conditions)
{
clause.Terms.Add(WhereTerm.CreateCompare(OmExpression.Field(cond.LeftField, leftTable), OmExpression.Field(cond.RightField, rightTable), CompCond.Equal));
}
Join(type, leftTable, rightTable, clause);
}
/// <summary>
/// Creates a SelectColumn with a column name, table, column alias and optional aggregation function
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
/// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias, AggFunc function)
{
if (function == AggFunc.None)
{
Expression = OmExpression.Field(columnName, table);
}
else
{
Expression = OmExpression.Function(function, OmExpression.Field(columnName, table));
}
this.ColumnAlias = columnAlias;
}
/// <summary>
/// Creates an uncoditional join
/// </summary>
/// <param name="type">Must be JoinType.CrossJoin</param>
/// <param name="leftTable"></param>
/// <param name="rightTable"></param>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable)
{
Join(type, leftTable, rightTable, new JoinCondition[] {});
}
/// <overloads>Use the following methods to define a join between two FromTerms.</overloads>
/// <summary>
/// Joins two tables using on a single join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="leftField">Name of the field in the left table to join on</param>
/// <param name="rightField">Name of the field in the right table to join on</param>
/// <example>
/// <code>
/// query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
/// </code>
/// </example>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, string leftField, string rightField)
{
Join(type, leftTable, rightTable, new JoinCondition(leftField, rightField));
}
/// <summary>
/// Joins two tables using on a triple join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="cond1">First equality condition to be applied on the join</param>
/// <param name="cond2">First equality condition to be applied on the join</param>
/// <param name="cond3">First equality condition to be applied on the join</param>
/// <remarks>
/// A logical AND will be applied on all conditions.
/// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3) ...
/// </remarks>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition cond1, JoinCondition cond2, JoinCondition cond3)
{
Join(type, leftTable, rightTable, new JoinCondition[] { cond1, cond2, cond3 });
}
/// <summary>
/// Creates a SelectColumn with a column name, table and column alias
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias) : this(columnName, table, columnAlias, AggFunc.None)
{
}
/// <summary>
/// Creates a SelectColumn with a column name, table, no column alias and no function
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
public SelectColumn(string columnName, FromTerm table) : this(columnName, table, null)
{
}
