class Cql::Query

Overview

The Query class is responsible for building SQL queries in a structured manner. It holds various components like selected columns, tables, conditions, and more. It provides methods to execute the query and return results.

Example Creating a new query

schema = Cql::Schema.new

Cql::Query.new(schema)
query.select(:name, :age).from(:users).where(name: "John").all(User)
=> [{"name" => "John", "age" => 30}]

Example Executing a query and iterating over results

schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:name, :age).from(:users).where(name: "John").each(User) do |user|
  puts user.name
end

=> John

Defined in:

query.cr

Constructors

Macro Summary

Instance Method Summary

Constructor Detail

def self.new(schema : Schema) #

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

schema = Cql::Schema.new
query = Cql::Query.new(schema)

=> #<Cql::Query:0x00007f8b1b0b3b00>

[View source]

Macro Detail

macro method_missing(call) #

[View source]

Instance Method Detail

def aggr_columns : Array(Expression::Aggregate) #

[View source]
def all(as as_kind) #

Executes the query and returns all records.

  • @param as [Type] The type to cast the results to
  • @return [Array(Type)] The results of the query

Example

schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:name, :age).from(:users).all(User)

=> [<User:0x00007f8b1b0b3b00 @name="John", @age=30>, <User:0x00007f8b1b0b3b00 @name="Jane", @age=25>]

[View source]
def all!(as as_kind) #
  • @param as [Type] The type to cast the results to
  • @return [Array(Type)] The results of the query

Example

schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:name, :age).from(:users).all!(User)

=> [<User:0x00007f8b1b0b3b00 @name="John", @age=30>, <User:0x00007f8b1b0b3b00 @name="Jane", @age=25>]

[View source]
def avg(column : Symbol) #

Adds an AVG aggregate function to the query.

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

Example

query.avg(:rating)
=> "SELECT AVG(rating) FROM users"

[View source]
def build #

Builds the final query expression.

  • @return [Expression::Query] The query expression

Example

query.build
=> #<Expression::Query:0x00007f8b1b0b3b00>

[View source]
def columns : Array(BaseColumn) #

[View source]
def count(column : Symbol = :*) #

Adds a COUNT aggregate function to the query.

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

Example

query.count(:id)
=> "SELECT COUNT(id) FROM users"

[View source]
def distinct #

Sets the distinct flag to true.

  • @return [Query] The query object

Example

query.from(:users).distinct
=> "SELECT DISTINCT * FROM users"

[View source]
def distinct? : Bool #

[View source]
def each(as as_kind, &) #

Iterates over each result and yields it to the provided block. Example:

query.each(User) do |user|
  puts user.name
end

=> John

[View source]
def first(as as_kind) #

Executes the query and returns the first record.

  • @param as [Type] The type to cast the result to
  • @return [Type] The first result of the query

Example

schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:name, :age).from(:users).first(User)

=> <User:0x00007f8b1b0b3b00 @name="John", @age=30>

[View source]
def first!(as as_kind) #
  • @param as [Type] The type to cast the result to
  • @return [Type] The first result of the query

Example

schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:name, :age).from(:users).first!(User)

=> <User:0x00007f8b1b0b3b00 @name="John", @age=30>

[View source]
def from(*tbls : Symbol) #

Specifies the tables to select from.

  • @param tbls [Symbol*] The tables to select from
  • @return [Query] The query object

Example

query.from(:users, :orders)
=> "SELECT * FROM users, orders"

[View source]
def get(as as_kind) #

Executes the query and returns a scalar value.

  • @param as [Type] The type to cast the result to
  • @return [Type] The scalar result of the query Example: query.get(Int64)
schema = Cql::Schema.new
query = Cql::Query.new(schema)
query.select(:count).from(:users).get(Int64)

=> 10

[View source]
def group(*columns) #

Specifies the columns to group by.

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

Example

query.from(:products).group(:category)
=> "SELECT * FROM products GROUP BY category"

[View source]
def group_by : Array(BaseColumn) #

[View source]
def having : Expression::Having | Nil #

[View source]
def having(&) #

Adds a HAVING condition to the grouped results.

  • @param block [Block] The block to evaluate the having condition
  • @return [Query] The query object

Example

query.from(:products).group(:category).having { avg(:price) > 100 }
=> "SELECT * FROM products GROUP BY category HAVING AVG(price) > 100"

[View source]
def inner(table : Symbol, on : Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)) #

Adds an INNER JOIN to the query.

  • @param table [Symbol] The table to join
  • @param on [Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)] The join condition
  • @return [Query] The query object

Example

query.inner(:orders, on: { users.id => orders.user_id })
=> "SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id"

[View source]
def inner(table : Symbol, &) #

Adds an INNER JOIN to the query.

  • @param table [Symbol] The table to join
  • @yield [FilterBuilder] The block to build the conditions
  • @return [Query] The query object
  • @raise [Exception] if the block is not provided
  • @raise [Exception] if the block does not return an expression
  • @raise [Exception] if the column does not exist

Example

query.inner(:orders) { |w| w.users.id == orders.user_id }
=> "SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id"

[View source]
def joins : Array(Expression::Join) #

[View source]
def left(table : Symbol, on : Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)) #

Adds a LEFT JOIN to the query.

  • @param table [Symbol] The table to join
  • @param on [Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)] The join condition

Example

query.left(:orders, on: { users.id => orders.user_id })
=> "SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id"

[View source]
def left(table : Symbol, &) #

Adds a LEFT JOIN to the query using a block.

  • @param table [Symbol] The table to join
  • @yield [FilterBuilder] The block to build the conditions
  • @return [Query] The query object
  • @raise [Exception] if the block is not provided
  • @raise [Exception] if the block does not return an expression
  • @raise [Exception] if the column does not exist

Example

query.left(:orders) { |w| w.users.id == orders.user_id }
=> "SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id"

[View source]
def limit(value : Int32) #

Sets the limit for the number of records to return.

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

Example

query.from(:users).limit(10)
=> "SELECT * FROM users LIMIT 10"

[View source]
def limit : Int32 | Nil #

[View source]
def max(column : Symbol) #

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

query.from(:users).max(:price)
=> "SELECT MAX(price) FROM users"

[View source]
def min(column : Symbol) #

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

query.min(:price)
=> "SELECT MIN(price) FROM users"

[View source]
def offset(value : Int32) #

Sets the offset for the query.

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

Example

query.from(:users).limit(10).offset(20)
=> "SELECT * FROM users LIMIT 10 OFFSET 20"

[View source]
def offset : Int32 | Nil #

[View source]
def order(*fields) #

Specifies the columns to order by.

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

Example

query.order(:name, :age)
=> "SELECT * FROM users ORDER BY name, age"

[View source]
def order(**fields) #

Specifies the columns to order by.

  • @param fields [Hash(Symbol, Symbol)] The columns to order by and their direction
  • @return [Query] The query object

Example

query.order(name: :asc, age: :desc)
=> "SELECT * FROM users ORDER BY name ASC, age DESC"

[View source]
def order_by : Hash(Expression::Column, Expression::OrderDirection) #

[View source]
def right(table : Symbol, on : Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)) #

Adds a RIGHT JOIN to the query.

  • @param table [Symbol] The table to join
  • @param on [Hash(Cql::BaseColumn, Cql::BaseColumn | DB::Any)] The join condition
  • @return [Query] The query object

Example

query.right(:orders, on: { users.id => orders.user_id })
=> "SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id"

[View source]
def right(table : Symbol, &) #

Adds a RIGHT JOIN to the query using a block.

  • @param table [Symbol] The table to join
  • @yield [FilterBuilder] The block to build the conditions
  • @return [Query] The query object
  • @raise [Exception] if the block is not provided
  • @raise [Exception] if the block does not return an expression
  • @raise [Exception] if the column does not exist
  • @raise [Exception] if the value is invalid

Example

query.right(:orders) { |w| w.users.id == orders.user_id }
=> "SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id"

[View source]
def select(*columns : Symbol) #

Specifies the columns to select.

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

Example

query.select(:name, :age)
=> "SELECT name, age FROM users"

[View source]
def select(**fields) #

Specifies the columns to select.

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

Example

query.from(:users, :address).select(users: [:name, :age], address: [:city, :state])
=> "SELECT users.name, users.age, address.city, address.state FROM users, address"

[View source]
def sum(column : Symbol) #

Adds a SUM aggregate function to the query.

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

Example

query.sum(:total_amount)
=> "SELECT SUM(total_amount) FROM users"

[View source]
def tables : Hash(Symbol, Table) #

[View source]
def to_sql(gen = @schema.gen) #

Converts the query into an SQL string and its corresponding parameters.

  • @param gen [Generator] The generator to use for converting the query
  • @return [Tuple(String, Array(DB::Any))] The SQL query and its parameters

Example

query.to_sql
=> {"SELECT * FROM users WHERE name = ? AND age = ?", ["John", 30]}

[View source]
def where(hash : Hash(Symbol, DB::Any)) #

Adds a WHERE condition with a hash of column-value pairs.

  • @param hash [Hash(Symbol, DB::Any)] The hash of column-value pairs
  • @return [Query] The query object

Example

query.from(:users).where(name: "John", age: 30)
=> "SELECT * FROM users WHERE name = 'John' AND age = 30"

[View source]
def where : Expression::Where | Nil #

[View source]
def where(**fields) #

Adds a WHERE condition with a block.

  • @fields [FilterBuilder] The block to build the conditions
  • @return [Query] The query object
  • @raise [Exception] if the column does not exist
  • @raise [Exception] if the value is invalid
  • @raise [Exception] if the value is not of the correct type

Example

query.from(:users).where(name: "John")
=> "SELECT * FROM users WHERE name = 'John'"

[View source]
def where(&) #

Adds WHERE conditions using a block.

  • @yield [FilterBuilder] The block to build the conditions
  • @return [Query] The query object
  • @raise [Exception] if the block is not provided
  • @raise [Exception] if the block does not return an expression
  • @raise [Exception] if the column does not exist

Example

query.from(:users).where { |w| w.name == "John" }
=> "SELECT * FROM users WHERE name = 'John'"

[View source]