Class Query

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a SQL SELECT query with support for filtering, joining, grouping, ordering, and unions.

public class Query : ISelectItem, IFromItem, IElement

Inheritance: object

Query

Implements: ISelectItem

IFromItem

IElement

Remarks

Query provides a fluent, type-safe API for building SELECT statements that work across all supported database systems. Queries can be used for data retrieval (Retrieve(Query) or Search(Query)), subqueries in filters, or subqueries in record field values.

The Query class supports:

SELECT with specific columns or SELECT *
FROM with tables or joins
WHERE and HAVING clauses with complex filters
ORDER BY with multiple columns
GROUP BY with aggregate functions
UNION, UNION ALL, INTERSECT, EXCEPT operations
DISTINCT, TOP/LIMIT, and OFFSET clauses

Examples

Basic query with filtering and ordering:

Query query = new Query()
    .Select([usersTable["username"], usersTable["email"], usersTable["age"]])
    .From(usersTable)
    .Where(new Filter([
        new Criterion<string>(usersTable["status"], "active"),
        new Criterion<int>(usersTable["age"], ConditionalType.GreaterThan, 18)
    ]))
    .OrderBy(new OrderClause(usersTable["username"], OrderClauseType.Ascending));

using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("username"));
    }
}

Query with JOIN:

Join join = new Join(ordersTable, usersTable, "user_id", JoinType.Inner);

Query query = new Query()
    .Select([usersTable["username"], ordersTable["order_date"], ordersTable["total"]])
    .From(join)
    .Where(new Criterion<decimal>(ordersTable["total"], ConditionalType.GreaterThan, 100m));

// SQL: SELECT users.username, orders.order_date, orders.total
//      FROM orders
//      INNER JOIN users ON orders.user_id = users.user_id
//      WHERE orders.total > 100

Query with GROUP BY and aggregate functions:

var countExpr = new Expression("order_count", new Count(ordersTable["id"]));
var sumExpr   = new Expression("total_spent",  new Sum(ordersTable["total"]));

Query query = new Query()
    .Select([ordersTable["user_id"], countExpr, sumExpr])
    .From(ordersTable)
    .GroupBy(ordersTable["user_id"])
    .Having(new Criterion<int>(countExpr, ConditionalType.GreaterThan, 5));

// SQL: SELECT user_id, COUNT(id) AS order_count, SUM(total) AS total_spent
//      FROM orders
//      GROUP BY user_id
//      HAVING COUNT(id) > 5

Subquery in filter:

// Find users with above-average age
Query avgAgeQuery = new Query()
    .Select(new Average(usersTable["age"]))
    .From(usersTable);

Query mainQuery = new Query()
    .Select([usersTable["username"], usersTable["age"]])
    .From(usersTable)
    .Where(new Filter(new Criterion<Query>(usersTable["age"], ConditionalType.GreaterThan, avgAgeQuery)));

// SQL: SELECT username, age FROM users
//      WHERE age > (SELECT AVG(age) FROM users)

Constructors

Query(): Creates a new empty query.

Properties

DataType: Gets the data type of this select item. Always returns Query for a query.

FromItems: A Stack representing all the IFromItems in a query. Represents all the items in the FROM clause

HavingClause: Gets the HAVING clause filter, or null if no HAVING clause has been set.

IsDDL: Gets or sets a value indicating whether this query represents a DDL statement. When true, column references omit the table qualifier in generated SQL.

IsDistinct: Gets or sets a value indicating whether the SELECT clause includes the DISTINCT keyword, eliminating duplicate rows from the result set.

IsSelectAll: Gets a value indicating whether this query was built with SelectAll(IFromItem) and includes all columns from its FROM items.

this[int]: Gets the select item at the specified zero-based index in the SELECT clause.

this[string]: Gets the select item with the specified name or alias from the SELECT clause.

LimitValue: Gets the maximum number of rows this query will return, or null if no limit has been applied.

Name: Gets the name of this query when it is used as a subquery or derived table.

OrderClauses: Gets the list of ORDER BY clauses applied to this query.

Precision: Not implemented. Queries do not have a numeric precision.

Scale: Not implemented. Queries do not have a numeric scale.

SelectItemNames: Gets the ordered list of names (or aliases) for the select items in the SELECT clause.

SelectItems: Gets the ordered collection of select items (columns, expressions, functions) in the SELECT clause of this query.

Size: Not implemented. Queries do not have a fixed column size.

Unions: A list of all the unions in the query

WhereClause: Gets the WHERE clause filter, or null if no WHERE clause has been set.

Methods

Create(): Creates a new empty query. This is a factory method for fluent query construction.

Distinct(): Adds the DISTINCT keyword to the SELECT clause, eliminating duplicate rows from the result set. Fluent API method.

From(IEnumerable<(IFromItem, string)>): Sets the FROM clause to the specified tables or joins. Fluent API method.

From(IEnumerable<IFromItem>): Sets the FROM clause to the specified tables or joins. Fluent API method.

From(IFromItem): Sets the FROM clause to the specified table or join. Fluent API method.

From(IFromItem, string): Sets the FROM clause to the specified table or join with an alias. Fluent API method.

GetSelectItem(int): Gets the select item at the specified zero-based index in the SELECT clause.

GetSelectItem(string): Gets the select item with the specified name or alias from the SELECT clause.

GroupBy(IEnumerable<IGroupItem>): Adds multiple GROUP BY clauses. Fluent API method.

GroupBy(IGroupItem): Adds a GROUP BY clause. Fluent API method.

HasSelectItem(string): Determines whether a select item with the specified name is present in the SELECT clause of this query.

HasSelectItem(ISelectItem): Determines whether the specified select item is present in the SELECT clause of this query.

HasUnion(): Determines whether this query has at least one UNION or UNION ALL clause appended.

Having(IEnumerable<IFilterItem>): Sets the HAVING clause from multiple filter items. Fluent API method.

Having(IFilterItem): Sets the HAVING clause to a single filter item. Fluent API method.

Having(Filter): Sets the HAVING clause for filtering grouped results. Fluent API method.

Limit(int): Limits the number of rows returned by the query.

OrderBy(IEnumerable<ISelectItem>): Adds multiple ORDER BY clauses with ascending sort order. Fluent API method.

OrderBy(IEnumerable<OrderClause>): Adds multiple ORDER BY clauses. Fluent API method.

OrderBy(ISelectItem): Adds an ORDER BY clause with ascending sort order. Fluent API method.

OrderBy(ISelectItem, OrderClauseType): Adds an ORDER BY clause with specified sort order. Fluent API method.

OrderBy(OrderClause, Query): Adds an ORDER BY clause. Fluent API method.

Select(IEnumerable<(ISelectItem selectItem, string alias, Query query)>): Adds multiple correlated subquery columns with aliases to the SELECT clause. Fluent API method.

Select(IEnumerable<(ISelectItem, string)>): Adds multiple columns with aliases or expressions to the SELECT clause. Fluent API method.

Select(IEnumerable<ISelectItem>): Adds multiple columns or expressions to the SELECT clause. Fluent API method.

Select(ISelectItem): Adds a column or expression to the SELECT clause. Fluent API method.

Select(ISelectItem, string): Adds a column or expression to the SELECT clause with an alias. Fluent API method.

Select(ISelectItem, string, Query): Adds a column or expression to the SELECT clause with an alias and correlated subquery. Fluent API method.

SelectAll(IEnumerable<IFromItem>): Configures the query to select all columns from the specified tables or joins.

SelectAll(IFromItem): Configures the query to select all columns from the specified table or join.

Union(Union): Appends a UNION or UNION ALL clause to this query. Fluent API method.

Where(IEnumerable<IFilterItem>): Sets the WHERE clause from multiple filter items. Fluent API method.

Where(IFilterItem): Sets the WHERE clause to a single filter item (criterion or nested filter). Fluent API method.