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