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"

Compile the query, and store the compiled SQL statement

Set the query type to delete, and execute the query

Execute the query

Execute the query, and return the result set

Set the group fields for the query

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

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

Set the query type to insert, set the insert values, and execute the 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")

Sets the join type for a child query

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

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)

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)

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)

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)

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}
  )

Set a parent query for subqueries and joins

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

Sets the type for the query

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

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")
  )