public SelectWhereStatement Having(string field, Comparison @operator, object compareValue) { if (HavingStatement.InnerClauseOperator == LogicOperator.And) { return(HavingStatement.And(field, @operator, compareValue)); } else { return(HavingStatement.Or(field, @operator, compareValue)); } }
/// <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()); }