class DBX::QueryBuilder

Overview

Query builder.

Direct Known Subclasses

Defined in:

query_builder/builder.cr
query_builder/builder_criteria.cr
query_builder/builder_support.cr

Constant Summary

QUOTE = '"'

Quoting character.

Constructors

Instance Method Summary

Constructor Detail

def self.new #

Creates a new QueryBuilder.


Instance Method Detail

def add_arg(value) : String #

Adds value to args and returns the placeholder.


def add_args_and_fields_from_data(data : NamedTuple | Hash, sep = ", ") : String #

Extracts arguments and fields from data, populates args and returns SQL part for a listing statement. Example: field1, field2, field3


def add_args_and_fields_from_data(data : Array, sep = ", ") : String #

Extracts arguments and fields from data, populates args and returns SQL part for a listing statement. Example: field1, field2, field3


def add_args_and_kv_from_data(data : NamedTuple | Hash, sep = ", ") : String #

Extracts arguments and fields from data, populates args and returns SQL part for a combined statement. Example: field1 = $1, field2 = $2, field3 = $3


def alter(table : OneOrMoreFieldsType, command : String, field : String, data_type = "") : QueryBuilder #

def alter(command : String, field : String, data_type = "") : QueryBuilder #

def analyze(table : OneOrMoreFieldsType) : QueryBuilder #

Builds the ANALYZE query


def analyze : QueryBuilder #

Builds the ANALYZE query


def avg(field : FieldType, name = nil) : QueryBuilder #

Adds AVG to the current query.


def between(field : FieldType, value1, value2, type = "", and_or = "AND") : QueryBuilder #

def build : SQLandArgsType #

Builds the current query and returns SQL (string) and arguments (array).

sql, args = builder.build

def check : QueryBuilder #

Builds the CHECK query


def check(table : OneOrMoreFieldsType) : QueryBuilder #

Builds the CHECK query


def checksum : QueryBuilder #

Builds the CHECKSUM query


def checksum(table : OneOrMoreFieldsType) : QueryBuilder #

Builds the CHECKSUM query


def count(field : FieldType, name = nil) : QueryBuilder #

Adds COUNT to the current query.


def delete : QueryBuilder #

def delete(table : OneOrMoreFieldsType) : QueryBuilder #

def delete(pk_name, pk_value) : QueryBuilder #

def drop(table : OneOrMoreFieldsType, check_exists = true) : QueryBuilder #

def drop(check_exists = true) : QueryBuilder #

def find(pk_name, pk_value) : QueryBuilder #

Finds one resource by its primary key.

Same as:

builder.find.where(pk_name, pk_value)

def find : QueryBuilder #

def find(&) : QueryBuilder #

Adds #find to current query and defines in raw form the SQL statement of the table(s).

Be careful, you have to manage arguments (arg) and quotes (q).

Example:

builder.find { "#{q("posts") AS p, articles a" }"

Generates:

SELECT * FROM "posts" AS p, articles a

def find(table_name : OneOrMoreFieldsType) : QueryBuilder #

def full_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds FULL JOIN to the current query.


def full_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds FULL OUTER JOIN to the current query.


def group_by(field : OneOrMoreFieldsType) : QueryBuilder #

def having(&) : QueryBuilder #

Defines in raw form the SQL statement of HAVING.

Be careful, you have to manage arguments (arg) and quotes (q).

Example:

builder.find(:tests).group_by(:payment).having { "SUM(price) > 40" }

Generates:

SELECT * FROM tests GROUP_BY payment HAVING SUM(person) > 40

def having(field : FieldType, op_or_val, value = nil) : QueryBuilder #

def in(field : FieldType, values : Array | Tuple, type = "", and_or = "AND") : QueryBuilder #

def inner_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds INNER JOIN to the current query.


def insert(data : Hash | NamedTuple) : QueryBuilder #

def insert(table : OneOrMoreFieldsType, data : Hash | NamedTuple) : QueryBuilder #

def join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil, type = "") : QueryBuilder #

def join(&) : QueryBuilder #

Adds a raw #join to current query.

Be careful, you have to manage arguments (arg) and quotes (q).

Example:

builder.find("tests").join { "
  INNER JOIN T2 ON T1.a = T2.a
  AND T1.b = T2.b
  OR T1.b = #{q(some_value_to_quote)}
" }
  .join { "LEFT JOIN payments p USING (product_id)" }

def join : String #

Returns jointure. Returns empty string if no jointure has been defined.

puts builder.join unless builder.join.blank? # or .empty?

def last_query : String #

Returns the last query.


def last_query_method : Symbol? #

Returns the last query method.


def left_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds LEFT JOIN to the current query.


def left_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds LEFT OUTER JOIN to the current query.


def like(field : FieldType, value, type = "", and_or = "AND") : QueryBuilder #

def limit : String #

Returns #limit value. Returns empty string if no limit has been defined.

puts builder.limit unless builder.limit.blank? # or .empty?

def limit(limit, limit_end = nil) : QueryBuilder #

def max(field : FieldType, name = nil) : QueryBuilder #

Adds MAX to the current query.


def min(field : FieldType, name = nil) : QueryBuilder #

Adds MIN to the current query.


def not_between(field : FieldType, value1, value2) : QueryBuilder #

def not_in(field : FieldType, values : Array | Tuple) : QueryBuilder #

def not_like(field : FieldType, value) : QueryBuilder #

def not_where(field : FieldType, op_or_val, value = nil) : QueryBuilder #

def offset(offset) : QueryBuilder #

def offset : String #

Returns #offset value. Returns empty string if no offset has been defined.

puts builder.offset unless builder.offset.blank? # or .empty?

def optimize(table : OneOrMoreFieldsType) : QueryBuilder #

Builds the OPTIMIZE query


def optimize : QueryBuilder #

Builds the OPTIMIZE query


def or_between(field : FieldType, value1, value2) : QueryBuilder #

def or_in(field : FieldType, values : Array | Tuple) : QueryBuilder #

def or_like(field : FieldType, value) : QueryBuilder #

def or_not_between(field : FieldType, value1, value2) : QueryBuilder #

def or_not_in(field : FieldType, values : Array | Tuple) : QueryBuilder #

def or_not_like(field : FieldType, value) : QueryBuilder #

def or_not_where(field : FieldType, op_or_val, value = nil) : QueryBuilder #

def or_where(field : FieldType, op_or_val, value = nil) : QueryBuilder #

def order_by(field : FieldType, dir = nil) : QueryBuilder #

def paginate(per_page, page) : QueryBuilder #

Sets #offset and #limit to get pagination-compatible results.


def ph(position : Int) : String #

Adds placeholder for a SQL argument.


def query(&) : QueryBuilder #

Generates a raw query.

Be careful, you have to manage arguments and quotes.

Example:

puts builder.query { "
  SELECT * FROM tests
  status = #{arg(true)}
  AND (
    #{q(:date)} <= #{arg(Time.utc - 1.day)}
    OR role = #{arg(:admin)}
  )
  LIMIT 1
" }

Generates:

SELECT *
FROM tests
WHERE status = $1
AND ("date" <= $2 OR role = $3)
LIMIT 1

def query_count : Int #

Returns number of queries made by the current instance.


def query_method : Symbol? #

Returns the query method.


def quote(field : FieldType) : String #

Wraps field with quotes (QUOTE).


def repair : QueryBuilder #

Builds the REPAIR query


def repair(table : OneOrMoreFieldsType) : QueryBuilder #

Builds the REPAIR query


def reset_query #

Resets current query.


def returning(*name : FieldType) : QueryBuilder #

SQL field(s) to be returned after an #insert statement.

* (wildcard) means all fields.

def returning : String? #

Returns the SQL field(s) to be returned after an #insert statement.

* (wildcard) means all fields.

def returning(fields : OneOrMoreFieldsType) : QueryBuilder #

SQL field(s) to be returned after an #insert statement.

* (wildcard) means all fields.

def right_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds RIGHT JOIN to the current query.


def right_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType? = nil) : QueryBuilder #

Adds RIGHT OUTER JOIN to the current query.


def select(fields : OneOrMoreFieldsType) : QueryBuilder #

Selects one or more fields.


def select : String #

Returns selected field(s). Default is *.

puts builder.select

def select(*name : FieldType) : QueryBuilder #

Selects one or more fields.


def sum(field : FieldType, name = nil) : QueryBuilder #

Adds SUM to the current query.


def table(&) : QueryBuilder #

Defines in raw form the SQL statement of the table(s).

Be careful, you have to manage arguments (arg) and quotes (q).

Example:

builder.find { "#{q("posts") AS p, articles a" }"

Generates:

SELECT * FROM "posts" AS p, articles a

def table(*name : FieldType) : QueryBuilder #

Targets tables defined by variadic arguments.

builder.table(:table1, :table2)

def table : String #

Returns table name(s). Returns empty string if no table has been defined.

puts builder.table unless builder.table.blank? # or .empty?

def table(name : OneOrMoreFieldsType) : QueryBuilder #

Targets one or more tables.


def to_data_h(data : Hash | NamedTuple) : DataHashType #

Converts NamedTuple to DataHashType.


def update(data : Hash | NamedTuple) : QueryBuilder #

def update(table : OneOrMoreFieldsType, data : Hash | NamedTuple) : QueryBuilder #

def update(pk_name, pk_value, data : Hash | NamedTuple) : QueryBuilder #

def where(field : FieldType, op_or_val, value = nil, type = "", and_or = "AND") : QueryBuilder #

Where clause.


def where(&) : QueryBuilder #

Adds a raw #where to current query.

Be careful, you have to manage arguments (arg) and quotes (q).

Example:

builder.find("tests").where { "
  status = #{arg(true)}
  AND (
    #{q(:date)} <= #{arg(Time.utc - 1.day)}
    OR role = #{arg(:admin)}
  )
" }

Generates:

SELECT *
FROM tests
WHERE status = $1
AND ("date" <= $2 OR role = $3)