/// <summary>
/// Groups the by.
/// </summary>
/// <param name="columns">The columns.</param>
public void GroupBy(params string[] columns)
{
foreach (var column in columns)
{
GroupByColumns.Add(column);
}
}
/// <summary>
/// Returns the statement as an SQL string.
/// </summary>
public override String ToString()
{
var stringBuilder = new StringBuilder("SELECT ");
if (!Columns.Any())
{
Columns.Add("*");
}
stringBuilder.AppendLine(String.Join(QueryBuilder.ColumnSeparator, Columns));
stringBuilder.AppendLine(FromBuilder.ToString());
if (GroupByColumns.Any())
{
stringBuilder.AppendLine("GROUP BY " + String.Join(QueryBuilder.ColumnSeparator, GroupByColumns));
}
if (OrderByColumns.Any())
{
var orderByColumns = OrderByColumns
.Select(pair => pair.Key + " " + (pair.Value == SortingDirection.Ascending ? "ASC" : "DESC"))
.ToArray();
stringBuilder.AppendLine("ORDER BY " + String.Join(QueryBuilder.ColumnSeparator, orderByColumns));
}
return(stringBuilder.ToString().Trim());
}
public SelectQuery GroupBy(IEnumerable <string> groupByColumns)
{
foreach (string groupByColumn in groupByColumns)
{
GroupByColumns.Add(Query.ColumnSyntax(groupByColumn));
}
return(this);
}
public Query GroupBy(params string[] columns)
{
foreach (var item in columns)
{
GroupByColumns.Add(new Column(item));
}
return(this);
}
/// <inheritdoc />
public ISelectStringBuilder GroupBy(params string[] columnNames)
{
foreach (var columnName in columnNames)
{
GroupByColumns.Add(columnName);
}
return(this);
}
/// <summary>
/// Converts this SoqlQuery into a string format suitable for use in a SODA call.
/// </summary>
/// <returns>The string representation of this SoqlQuery.</returns>
public override string ToString()
{
var sb = new StringBuilder();
sb.AppendFormat("{0}=", SelectKey);
if (SelectColumns.Length == 1 && SelectColumns[0] == "*")
{
sb.Append(SelectColumns[0]);
}
else
{
//evaluate the provided aliases
var finalColumns = SelectColumns.Zip(SelectColumnAliases, (c, a) => String.Format("{0} AS {1}", c, a)).ToList();
if (SelectColumns.Length > SelectColumnAliases.Length)
{
//some columns were left un-aliased
finalColumns.AddRange(SelectColumns.Skip(SelectColumnAliases.Length));
}
//form the select clause
sb.Append(String.Join(Delimiter, finalColumns));
}
sb.AppendFormat("&{0}={1} {2}", OrderKey, String.Join(Delimiter, OrderByColumns), OrderDirection);
if (!String.IsNullOrEmpty(WhereClause))
{
sb.AppendFormat("&{0}={1}", WhereKey, WhereClause);
}
if (GroupByColumns != null && GroupByColumns.Any())
{
sb.AppendFormat("&{0}={1}", GroupKey, String.Join(Delimiter, GroupByColumns));
}
if (OffsetValue > 0)
{
sb.AppendFormat("&{0}={1}", OffsetKey, OffsetValue);
}
if (LimitValue > 0)
{
sb.AppendFormat("&{0}={1}", LimitKey, LimitValue);
}
if (!String.IsNullOrEmpty(SearchText))
{
sb.AppendFormat("&{0}={1}", SearchKey, SearchText);
}
return(sb.ToString());
}
public bool IsTrivial()
{
return((WhereExpr == null || WhereExpr.Type == ExprType.EMPTY) &&
(GroupWhereExpr == null || GroupWhereExpr.Type == ExprType.EMPTY) &&
!Columns.Any() &&
!OrderByExprs.Any() &&
!Joins.Any() &&
RowsLimit == null &&
RowsOffset == 0 &&
!DistinctRecords &&
!GroupByColumns.Any());
}
internal override void DeleteLinkedObjects(bool isChildOfDeleted)
{
// Remove any relationships this column participates in:
UsedInRelationships.ToList().ForEach(r => r.Delete());
if (!isChildOfDeleted)
{
if (Handler.CompatibilityLevel >= 1400)
{
ObjectLevelSecurity.Clear();
}
// Remove any hierarchy levels this column is used in:
UsedInLevels.ToList().ForEach(l => l.Delete());
// Make sure the column is no longer used as a Sort By column:
UsedInSortBy.ToList().ForEach(c => c.SortByColumn = null);
// Make sure the column is no longer used in any Calculated Tables:
foreach (var ctc in OriginForCalculatedTableColumns.ToList())
{
ctc.InternalDelete();
}
}
// Make sure the column is no longer used in any Variations:
if (Handler.CompatibilityLevel >= 1400)
{
UsedInVariations.ToList().ForEach(v => v.Delete());
}
// Make sure the column is no longer used in AlternateOf's:
if (Handler.CompatibilityLevel >= 1460)
{
UsedInAlternateOfs.ToList().ForEach(a => a.BaseColumn = null);
}
if (GroupByColumns != null)
{
GroupByColumns.Clear();
}
base.DeleteLinkedObjects(isChildOfDeleted);
}
public SelectQuery GroupBy(string groupByColumn, params string[] groupByColumns)
{
GroupByColumns.Add(Query.ColumnSyntax(groupByColumn));
return(GroupBy(groupByColumns));
}
/// <summary>
/// Builds the query string or SQLiteCommand
/// </summary>
/// <param name="buildCommand"></param>
/// <returns></returns>
protected object BuildQuery(bool buildCommand)
{
// Define local variables
int tableIndex = 0;
int tableCount = Tables.Count;
// Make sure we have a table name
if (Tables.Count == 0 || String.IsNullOrWhiteSpace(Tables[0].Name))
{
throw new Exception("No tables were specified for this query.");
}
// Start Query
StringBuilder query = new StringBuilder("SELECT ", 256);
query.AppendIf(Distinct, "DISTINCT ");
// Append columns from each table
foreach (var table in Tables)
{
// Define local variables
int colCount = table.Columns.Count;
tableIndex++;
tableCount--;
// Create alias for this table if there is none
if (String.IsNullOrWhiteSpace(table.Alias))
{
table.Alias = $"t{tableIndex}";
}
// Check if the user wants to select all columns
if (colCount == 0)
{
query.AppendFormat("{0}.*", Context.QuoteIdentifier(table.Alias));
query.AppendIf(tableCount > 0, ", ");
}
else
{
// Add each result selector to the query
foreach (ColumnIdentifier column in table.Columns.Values)
{
// Use the internal method to append the column string to our query
column.AppendToQuery(query, Context, table.Alias);
// If we have more results to select, append Comma
query.AppendIf(--colCount > 0 || tableCount > 0, ", ");
}
}
}
// === Append main Table === //
var fromTbl = Tables[0];
query.Append($" FROM {Context.QuoteIdentifier(fromTbl.Name)} AS {Context.QuoteIdentifier(fromTbl.Alias)}");
// Append Joined tables
if (Joins.Count > 0)
{
foreach (JoinClause clause in Joins)
{
// Convert join type to string
switch (clause.JoinType)
{
default:
case JoinType.InnerJoin:
query.Append(" JOIN ");
break;
case JoinType.OuterJoin:
query.Append(" OUTER JOIN ");
break;
case JoinType.CrossJoin:
query.Append(" CROSS JOIN ");
break;
case JoinType.LeftJoin:
query.Append(" LEFT JOIN ");
break;
}
// Append the join statement
string alias = Context.QuoteIdentifier(clause.JoiningTable.Alias);
query.Append($"{Context.QuoteIdentifier(clause.JoiningTable.Name)} AS {alias}");
// Do we have an expression?
if (clause.ExpressionType == JoinExpressionType.On)
{
// Try and grab the table
var tbl = Tables.Where(x => x.Name.Equals(clause.FromTable, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
string fromT = tbl?.Alias ?? clause.FromTable;
query.Append(" ON ");
query.Append(
SqlExpression <WhereStatement> .CreateExpressionString(
$"{alias}.{Context.QuoteIdentifier(clause.JoiningColumn)}",
clause.ComparisonOperator,
new SqlLiteral(Context.QuoteIdentifier($"{fromT}.{clause.FromColumn}"))
)
);
}
else if (clause.ExpressionType == JoinExpressionType.Using)
{
var parts = clause.JoiningColumn.Split(',');
query.AppendFormat(" USING({0})", String.Join(", ", parts.Select(x => Context.QuoteIdentifier(x))));
}
}
}
// Append Where Statement
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
if (WhereStatement.HasClause)
{
if (buildCommand)
{
query.Append(" WHERE " + WhereStatement.BuildStatement(parameters));
}
else
{
query.Append(" WHERE " + WhereStatement.BuildStatement());
}
}
// Append GroupBy
if (GroupByColumns.Count > 0)
{
query.Append(" GROUP BY " + String.Join(", ", GroupByColumns.Select(x => Context.QuoteIdentifier(x))));
}
// Append Having
if (HavingStatement.HasClause)
{
if (GroupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
query.Append(" HAVING " + HavingStatement.BuildStatement(parameters));
}
// Append OrderBy
if (OrderByStatements.Count > 0)
{
int count = OrderByStatements.Count;
query.Append(" ORDER BY");
foreach (OrderByClause clause in OrderByStatements)
{
query.Append($" {Context.QuoteIdentifier(clause.ColumnName)}");
// Add sorting if not default
query.AppendIf(clause.SortOrder == Sorting.Descending, " DESC");
// Append seperator if we have more orderby statements
query.AppendIf(--count > 0, ",");
}
}
// Append Limit
query.AppendIf(Limit > 0, " LIMIT " + Limit);
query.AppendIf(Offset > 0, " OFFSET " + Offset);
// Create Command
SQLiteCommand command = null;
if (buildCommand)
{
command = Context.CreateCommand(query.ToString());
command.Parameters.AddRange(parameters.ToArray());
}
// Return Result
return((buildCommand) ? command as object : query.ToString());
}
/// <summary>
/// Creates and adds a new Groupby clause to the current query object
/// </summary>
/// <param name="fieldName"></param>
public SelectQueryBuilder GroupBy(string fieldName)
{
GroupByColumns.Add(fieldName);
return(this);
}
public Query GroupBy(params Column[] columns)
{
GroupByColumns.AddRange(columns);
return(this);
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
default:
throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
}
sb.Append($"`{clause.ToTable}` ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
}
if (TakeRows.HasValue)
{
sb.AppendLine();
if (SkipRows.HasValue)
{
sb.Append($"LIMIT {SkipRows}, {TakeRows}");
}
else
{
sb.Append($"LIMIT {TakeRows}");
}
}
// Return the built query.
return(sb.ToString());
}
/// <summary>
/// Adds a column to the GROUP BY clause.
/// </summary>
public SelectBuilder GroupBy(String column)
{
GroupByColumns.Add(column);
return(this);
}
public override string ToPlainQuery()
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT");
if (SelectDistinct)
{
sb.Append(" DISTINCT");
}
if (TopN > 0)
{
sb.AppendFormat(" TOP {0}", TopN);
}
if (SelectColumns.Count > 0)
{
for (int i = 0; i < SelectColumns.Count; i++)
{
Column col = SelectColumns[i];
sb.Append(" " + col.AliasedName);
if (i < SelectColumns.Count - 1)
{
sb.Append(",");
}
}
}
else
{
sb.Append(" *");
}
sb.AppendFormat(" FROM {0}{1}",
FromTableOrQuery(),
FromQuery == null ? "" : $" AS {FromQuery.Item2}");
sb.Append($" {JoinString}");
if (WhereCondition != null)
{
sb.AppendFormat(" WHERE {0}", WhereCondition.ToString());
}
if (GroupByColumns.Count > 0)
{
sb.AppendFormat(" GROUP BY {0}", String.Join(",", GroupByColumns.Select(r => " " + r.FullName)));
}
if (HavingCondition != null)
{
sb.AppendFormat(" HAVING {0}", HavingCondition.ToString());
}
if (OrderByColumns.Count > 0)
{
sb.AppendFormat(" ORDER BY {0}", String.Join(",", OrderByColumns.Select(r => String.Format(" {0} {1}", String.IsNullOrEmpty(r.Item1.Alias) ? r.Item1.FullName : r.Item1.Alias, r.Item2.GetStringValue()))));
}
sb.Append($" {SetOpString}");
return(sb.ToString().Trim());
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
if (!SkipRows.HasValue && TakeRows.HasValue)
{
sb.Append($"TOP {TakeRows} ");
if (TopIsPercent)
{
sb.Append("PERCENT ");
}
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.OuterJoin:
sb.Append("OUTER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
}
sb.Append($"[{clause.ToTable}] ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// TODO: Output having statement.
/*
* if (Having.ClauseLevels > 0)
* {
* // Check if a Group By Clause was set
* if (groupByColumns.Count == 0)
* {
* throw new Exception("Having statement was set without Group By");
* }
* if (buildCommand)
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
* }
* else
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement());
* }
* }
*/
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
// Works only in SQL Server 2012 and upper.
// TODO use BETWEEN if it require
if (SkipRows.HasValue)
{
sb.AppendLine();
sb.Append($"OFFSET {SkipRows} ROWS");
if (TakeRows.HasValue)
{
sb.AppendLine();
sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
}
}
}
// Return the built query.
return(sb.ToString());
}