class

Miner::Query

Inherits Reference < Object

This class creates query objects, which will later be compiled into SQL statements by the query builder

Constructors

new(table : Table | String, database : Miner::Database = Miner.default_database)

Create a new query

Queries can be created by passing a table name, or an instance of Miner::Table

# With a table name
query = Miner::Query.new "countries"

# With a Miner::Table instance
table = Miner::Table.new "countries"
query = Miner::Query.new table

By default, queries are executed against the database defined in Miner.default_database. This can be overridden by passing an instance of Miner::Database as a second parameter

config = Miner::Config.new({
  "name"     => "my_other_database",
  "username" => "my_user",
  "password" => "my_password"
})
database = Miner::Database.new config
query = Miner::Query.new "my_table"
Source

Instance methods

clauses
Source
compile

Compile the query, and store the compiled SQL statement

Source
compiled
Source
database
Source
delete

Set the query type to delete, and execute the query

Source
execute

Execute the query

Source
fetch

Execute the query, and return the result set

Source
fields
Source
from(table : Table | String) : self
Source
group_by(*fields : String) : self

Set the group fields for the query

Source
having(column : Value, operator : String, value : Value) : self

Add a HAVING clause to the query, with AND as a delimiter

query = Miner::Query.new("city")
  .having("country_id", "=", 12)
  .having("population", ">=", 4000)
Source
insert(values : Hash(String, Value)) : self

Set the query type to insert, set the insert values, and execute the query

Source
join(table : Table | String, type : JoinType = JoinType::Left) : Query

Add a join to the query

Joins can be defined using a table name

query = Miner::Query.new("city")
  .join "country"

or an instance of Miner::Table

query = Miner::Query.new("city")
  .join Miner::Table.new("country")

Accessing parent fields

When specifying ON clauses for a join, fields on the parent table can be accessed using the parent keyword

query = Miner::Query.new("city")
  .join("country")
    .on("id", "=", "parent.country_id")

Join Types

The join type can be specified by passing a member of Miner::Query::JoinType as the second argument

query = Miner::Query.new("city")
  .join "country", Miner::Query::JoinType::Right

Nesting

Joins return a new instance of Miner::Query, and can be nested indefinitely

query = Miner::Query.new("city")
  .join("country")
    .on("id", "=", "parent.country_id")
    .join("language")
      .on("country_id", "=", "parent.id")
Source
join_type
Source
join_type=(join_type : JoinType)

Sets the join type for a child query

Source
join_type=(join_type : Miner::Query::JoinType | Nil)
Source
joins
Source
on(column : Value, operator : String, value : Value) : self

Add an ON clause to the query, with AND as a delimiter

query = Miner::Query.new("city")
  .join("country")
    .on("id", "=", "parent.country_id")
    .on("population", ">=", 4000)

An exception is raised if an on clause is applied to a query which is not a join

query = Miner::Query.new("city")
  .on("id", "=", 12) # Raises an exception
Source
or_having(column : Value, operator : String, value : Value) : self

Add a HAVING clause to the query, with OR as a delimiter

query = Miner::Query.new("city")
  .having("country_id", "=", 12)
  .or_having("population", ">=", 4000)
Source
or_on(column : Value, operator : String, value : Value) : self

Add an ON clause to the query, with AND as a delimiter

query = Miner::Query.new("city")
  .join("country")
    .on("id", "=", "parent.country_id")
    .or_on("population", ">=", 4000)

An exception is raised if an on clause is applied to a query which is not a join

query = Miner::Query.new("city")
  .on("id", "=", 12) # Raises an exception
  .or_on("population", ">=", 4000)
Source
or_where(column : Value, operator : String, value : Value) : self

Add a WHERE clause to the query, with OR as a delimiter

query = Miner::Query.new("city")
  .where("country_id", "=", 12)
  .or_where("population", ">=", 4000)
Source
order_by(field : String, direction : Sort = Sort::Asc) : self

Set the order fields for the query

query = Miner::Query.new("countries")
  .order_by("name")

The sort direction can be specified by passing a member of Miner::Query::Sort as a second argument

query = Miner::Query.new("countries")
  .order_by("name", Miner::Query::Sort::Desc)

Multiple fields can be specified by chaining the method

query = Miner::Query.new("countries")
  .order_by("language", Miner::Query::Sort::Asc)
  .order_by("name", Miner::Query::Sort::Asc)
Source
order_by(*fields : String | Tuple(String, Sort)) : self

A shorthand for setting multiple sort fields at once

query = Miner::Query.new("countries")
  .order_by("name", "language")

The sort direction can be specified by passing tuples containing the field name and a member of Miner::Query::Sort

query = Miner::Query.new("countries")
  .order_by(
    {"name", Miner::Query::Sort::Desc},
    {"language", Miner::Query::Sort::Asc}
  )
Source
parent
Source
parent=(parent : Query)

Set a parent query for subqueries and joins

Source
parent=(parent : Nil | Miner::Query)
Source
select(*fields : String) : self

Set the query type to select, and set the fields to be selected

Source
sql
Source
table
Source
type
Source
type=(type : Type)

Sets the type for the query

Source
update(values : Hash(String, Value)) : self

Set the query type to update, set the update values, and execute the query

Source
values
Source
where(column : Value, operator : String, value : Value) : self

Add a WHERE clause to the query, with AND as a delimiter

query = Miner::Query.new("city")
  .where("country_id", "=", 12)
  .where("population", ">=", 4000)

Sub-queries can be passed into where clauses by creating a new instance of Miner::Query

query = Miner::Query.new("city")
  .where("country_id", "IN", Miner::Query.new("country"))
    .select("id")
    .where("language", "=", "British")
  )
Source

Nested types