Initializes the Query object with the provided schema.

• @param schema [Schema] The schema object to use for the query
• @return [Query] The query object

Example Creating a new query

query = CQL::Query.new(AcmeDB)

A clause collection most queries never use: allocated on first access through its getter, so .new and #clone pay nothing for it. name? tells whether it holds anything without allocating.

Adds an AVG aggregate function to the query.

• @param column [Symbol] The column to average
• @return [Query] The query object

Example

AcmeDB.query.from(:users).avg(:age).to_sql
# => {"SELECT AVG(users.age) FROM users", []}

Builds the final query expression.

• @param limit [Int32?] Overrides the query's limit for this build (the query is unchanged)
• @return [Expression::Query] The query expression

Example

AcmeDB.query.from(:users).build # an Expression::Query, ready for a generator

Clone the query object

Store column aliases: column => alias

Store column aliases: column => alias

The column as an expression qualified by the alias of its table in this query.

Adds a COUNT aggregate function to the query.

• @param column [Symbol] The column to count
• @return [Query] The query object

Example

AcmeDB.query.from(:users).count(:id).to_sql
# => {"SELECT COUNT(users.id) FROM users", []}

Adds a CROSS JOIN (every row paired with every row of table).

CTEs declared on this query, and the tables they expose by name.

CTEs declared on this query, and the tables they expose by name.

Sets the distinct flag to true.

• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).distinct.to_sql
# => {"SELECT DISTINCT users.name FROM users", []}

PostgreSQL / CockroachDB: keep the first row of each group of equal columns (the order is decided by #order). Other dialects raise when SQL is generated.

The first row mapped to kind, rendered with LIMIT 1 instead of on a clone.

Uses a subquery as the FROM source: FROM (SELECT ...) AS alias. Its selected columns can be referenced through alias.

Specifies the tables to select from.

• @param tbls_or_aliases [Symbol* | Hash(Symbol, Symbol)*] The tables to select from
• @return [Query] The query object

Example

AcmeDB.query.from(:users, :posts) # selects every column of both tables

Accept keyword arguments for table/alias pairs

Adds a FULL OUTER JOIN using a block for the condition (not supported by MySQL).

Specifies the columns to group by. Handles qualified columns or aliases.

• @param columns [Symbol* | String*] The columns to group by
• @return [Query] The query object

Adds a HAVING condition to the grouped results. The block uses a HavingBuilder which needs alias awareness.

• @yield [HavingBuilder] Block to build the condition.
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:age).group(:age).having { count(:id) > 5 }.to_sql
# => {"SELECT users.age FROM users GROUP BY users.age HAVING COUNT(users.id) > ?", [5]}

Adds a JOIN to the query with automatic relationship detection.

• @param table_or_alias [Symbol] The table to join
• @return [Query] The query object

Adds a JOIN to the query using a block for the condition.

• @param table_or_alias [Symbol] The table to join
• @yield [FilterBuilder] The block to build the ON condition
• @return [Query] The query object

Adds JOINs to the query using named arguments for table aliasing.

• @param tables_with_aliases [NamedTuple] Table name => alias
• @return [Query] The query object

Joins table with the given join type and an explicit ON condition built by the block.

Adds a LEFT JOIN to the query with automatic relationship detection.

• @param table_or_alias [Symbol] The table to join
• @return [Query] The query object

Adds a LEFT JOIN to the query using a block for the condition.

• @param table_or_alias [Symbol] The table to join
• @yield [FilterBuilder] The block to build the ON condition
• @return [Query] The query object

Adds LEFT JOINs to the query using named arguments for table aliasing.

• @param tables_with_aliases [NamedTuple] Table name => alias
• @return [Query] The query object

Sets the limit for the number of records to return.

• @param value [Int32] The limit value
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).limit(10).to_sql
# => {"SELECT users.name FROM users LIMIT ?", [10]}

Appends FOR UPDATE (or FOR SHARE), optionally SKIP LOCKED / NOWAIT. SQLite has no row locks and raises when SQL is generated.

Adds a MAX aggregate function to the query.

• @param column [Symbol] The column to find the maximum value of
• @return [Query] The query object

Example

AcmeDB.query.from(:users).max(:age).to_sql
# => {"SELECT MAX(users.age) FROM users", []}

Merges the properties of another Query object into this one. The current query is modified in place.

• @param other_query [Query] The query object to merge from.
• @return [Query] The current query object, modified.

Behavior:

• Schema: Must be the same for both queries.
• Distinct: Becomes true if either query is distinct.
• Select Columns (@columns): Concatenated and uniqued by object identity.
• Aggregate Columns (@select_expressions): Concatenated and uniqued by object identity.
• Query Tables (@query_tables): Merged. Conflicts on alias pointing to different tables raise an error.
• Joins (@joins): Concatenated and uniqued by object identity.
• Where (@where): Conditions are combined using AND.
• Group By (@group_by): Concatenated and uniqued by object identity.
• Having (@having): Conditions are combined using AND.
• Order By (@order_by): Entries from other_query take precedence.
• Limit (@limit): The minimum of the two limits is taken if both are set; otherwise, the set limit is used.
• Offset (@offset): The other_query's offset takes precedence if set.

ameba/disable Metrics/CyclomaticComplexity

Adds a MIN aggregate function to the query.

• @param column [Symbol] The column to find the minimum value of
• @return [Query] The query object

Example

AcmeDB.query.from(:users).min(:age).to_sql
# => {"SELECT MIN(users.age) FROM users", []}

A fresh query that can reference this query's CTEs, for subqueries and CTE bodies.

Sets the offset for the query.

• @param value [Int32] The offset value
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).limit(10).offset(20).to_sql
# => {"SELECT users.name FROM users LIMIT ? OFFSET ?", [10, 20]}

Specifies the columns to order by. Handles qualified columns like #order("users.name") or aliases #order("u.name").

• @param fields [Symbol* | String*] The columns to order by (Symbol or "alias.column")
• @return [Query] The query object

Specifies the columns to order by with direction. Handles qualified columns or aliases in keys.

• @param fields [Hash(Symbol | String, Symbol)] Column => :asc/:desc
• @return [Query] The query object

Replaces the existing order clause with new ordering.

• @param fields [Symbol*] The fields to order by
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).order(:name).reorder(:age).to_sql
# => {"SELECT users.name FROM users ORDER BY users.age ASC", []}

Replaces the existing order clause with new ordering using hash syntax.

• @param fields [Hash] The fields and directions to order by
• @return [Query] The query object

Reverses the order of the query.

• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).order(:name).reverse_order.to_sql
# => {"SELECT users.name FROM users ORDER BY users.name DESC", []}

Adds a RIGHT JOIN to the query with automatic relationship detection.

• @param table_or_alias [Symbol] The table to join
• @return [Query] The query object

Adds a RIGHT JOIN to the query using a block for the condition.

• @param table_or_alias [Symbol] The table to join
• @yield [FilterBuilder] The block to build the ON condition
• @return [Query] The query object

Adds RIGHT JOINs to the query using named arguments for table aliasing.

• @param tables_with_aliases [NamedTuple] Table name => alias
• @return [Query] The query object

Specifies the columns to select.

• @param columns [Symbol*] The columns to select
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name, :age).to_sql
# => {"SELECT users.name, users.age FROM users", []}

Specifies the columns to select, grouped by table name or alias.

• @param fields [Hash(Symbol, Array(Symbol) | Symbol)] The columns to select
• @return [Query] The query object

Example

AcmeDB.query.from(:users, :posts).select(users: [:name, :age], posts: [:title]).to_sql
# => {"SELECT users.name, users.age, posts.title FROM users, posts", []}

Example with aggregates

AcmeDB.query.from(:users).select(count: :id).to_sql
# => {"SELECT COUNT(users.id) FROM users", []}

Allow mixing String/Symbol args and Hash

Adds a SUM aggregate function to the query.

• @param column [Symbol] The column to sum
• @return [Query] The query object

Example

AcmeDB.query.from(:users).sum(:age).to_sql
# => {"SELECT SUM(users.age) FROM users", []}

Removes specific scopes from the query.

• @param scopes [Symbol*] The scopes to remove
• @return [Query] The query object

Example

AcmeDB.query.from(:users).select(:name).where(active: true).unscope(:where).to_sql
# => {"SELECT users.name FROM users", []}

Accept Hash(Symbol, DB::Any) for backward compatibility

ANDs an already built condition (typed DSL, CQL.raw) with the existing WHERE.

A raw SQL fragment with bound ? or :name values (not both in one fragment), ANDed with other conditions.

AcmeDB.query.from(:users).where("lower(name) = ? AND age > ?", "ada", 18)
AcmeDB.query.from(:users).where("age > :age", age: 18)

Correct implementation for where(&)

Adds a greater-than condition for the specified field. Useful for cursor-based pagination and range queries.

• @param field [Symbol | String] The column to compare
• @param value [DB::Any] The value to compare against
• @return [Query] The query object

Example

# Find records with id greater than 100
AcmeDB.query.from(:users).where_gt(:id, 100).all(User)

Support for LIKE conditions

Adds a less-than condition for the specified field. Useful for cursor-based pagination and range queries.

• @param field [Symbol | String] The column to compare
• @param value [DB::Any] The value to compare against
• @return [Query] The query object

Example

# Find records with id less than 100
AcmeDB.query.from(:users).where_lt(:id, 100).all(User)

Adds an IS NOT NULL condition for the specified field. Provides explicit non-null checking for better code clarity.

• @param field [Symbol | String] The column to check for not null
• @return [Query] The query object

Example

# Find posts with a body
AcmeDB.query.from(:posts).where_not_null(:body).all(Post)

Adds an IS NULL condition for the specified field. Provides explicit null checking for better code clarity.

• @param field [Symbol | String] The column to check for null
• @return [Query] The query object

Example

# Find posts without a body
AcmeDB.query.from(:posts).where_null(:body).all(Post)

Adds FUNCTION(...) OVER (PARTITION BY ... ORDER BY ... frame) AS name to the select list.

query = AcmeDB.query.from(:users)
query.window(:row_number, as: :age_rank, partition_by: :active, order_by: {age: :desc})
query.window(:sum, :age, as: :running_age, order_by: :id, rows: {:unbounded_preceding, 0})
query.window(:lag, :age, offset: 1, as: :previous_age, order_by: :id)
query.window(:ntile, buckets: 4, as: :quartile, order_by: :age)

Functions: row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, count, sum, avg, min, max. Frame bounds are offsets (negative = preceding) or :unbounded_preceding / :unbounded_following. Without a preceding #select(...) only the window column is selected; windows read plain table columns (not aggregates).

Declares WITH name AS (source); name can then be used in #from/#join like a table, with the columns source selects.

adults = AcmeDB.query.from(:users).select(:name).where_gt(:age, 17)
AcmeDB.query.with(:adults, adults).from(:adults).to_sql
# => {"WITH adults AS (SELECT users.name FROM users WHERE users.age > ?) SELECT adults.name FROM adults", [17]}

Like #with, building the source from a query that can already see the CTEs declared so far, so one CTE can read another.

Executes the query within a performance monitoring context. This is useful for grouping related queries together in performance reports.

• @param context [String] The context name (e.g., "UserController#index")
• @yield Block that executes the query
• @return The result of the block

Example

query = AcmeDB.query.from(:users).where(active: true)
users = query.with_performance_context("UserController#index") do
  query.all(User)
end

Declares WITH RECURSIVE name AS (anchor UNION [ALL] step). The block receives an empty query that already knows name, and returns the recursive step (which cannot have ORDER BY / LIMIT; put those on the outer query).