class DBX::QueryBuilder

• DBX::QueryBuilder
• Reference
• Object

Overview

Query builder.

Direct Known Subclasses

• DBX::Adapter::PGQueryBuilder
• DBX::Adapter::SQLiteQueryBuilder

Defined in:

Constant Summary

QUOTE = '"'

Quoting character.

Constructors

• .new

  Creates a new QueryBuilder.

Instance Method Summary

• #add_arg(value) : String

  Adds value to args and returns the placeholder.

• #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.

• #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.

• #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.

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

  Builds the ANALYZE query

• #analyze : QueryBuilder

  Builds the ANALYZE query

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

  Adds AVG to the current query.

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

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

• #check(table : OneOrMoreFieldsType) : QueryBuilder

  Builds the CHECK query

• #check : QueryBuilder

  Builds the CHECK query

• #checksum(table : OneOrMoreFieldsType) : QueryBuilder

  Builds the CHECKSUM query

• #checksum : QueryBuilder

  Builds the CHECKSUM query

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

  Adds COUNT to the current query.

• #delete(pk_name, pk_value) : QueryBuilder
• #delete(table : OneOrMoreFieldsType) : QueryBuilder
• #delete : QueryBuilder
• #drop(table : OneOrMoreFieldsType, check_exists = true) : QueryBuilder
• #drop(check_exists : Bool | NamedTuple(command: String, field: String, data_type: String) | Nil = true) : QueryBuilder
• #find(pk_name, pk_value) : QueryBuilder

  Finds one resource by its primary key.

• #find(table_name : OneOrMoreFieldsType) : QueryBuilder
• #find : QueryBuilder
• #find(&) : QueryBuilder

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

• #full_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds FULL JOIN to the current query.

• #full_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds FULL OUTER JOIN to the current query.

• #group_by(field : OneOrMoreFieldsType) : QueryBuilder
• #having(field : FieldType, op_or_val, value = nil) : QueryBuilder
• #having(&) : QueryBuilder

  Defines in raw form the SQL statement of HAVING.

• #in(field : FieldType, values : Array | Tuple, type = "", and_or = "AND") : QueryBuilder
• #inner_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds INNER JOIN to the current query.

• #insert(table : OneOrMoreFieldsType, data : Hash | NamedTuple) : QueryBuilder
• #insert(data : Hash | NamedTuple) : QueryBuilder
• #join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil, type = "") : QueryBuilder
• #join(&) : QueryBuilder

  Adds a raw #join to current query.

• #join : String

  Returns jointure.

• #last_query : String

  Returns the last query.

• #last_query_method : Symbol | Nil

  Returns the last query method.

• #left_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds LEFT JOIN to the current query.

• #left_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds LEFT OUTER JOIN to the current query.

• #like(field : FieldType, value, type = "", and_or = "AND") : QueryBuilder
• #limit(limit, limit_end = nil) : QueryBuilder
• #limit : String

  Returns #limit value.

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

  Adds MAX to the current query.

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

  Adds MIN to the current query.

• #not_between(field : FieldType, value1, value2) : QueryBuilder
• #not_in(field : FieldType, values : Array | Tuple) : QueryBuilder
• #not_like(field : FieldType, value) : QueryBuilder
• #not_where(field : FieldType, op_or_val, value = nil) : QueryBuilder
• #offset(offset) : QueryBuilder
• #offset : String

  Returns #offset value.

• #optimize(table : OneOrMoreFieldsType) : QueryBuilder

  Builds the OPTIMIZE query

• #optimize : QueryBuilder

  Builds the OPTIMIZE query

• #or_between(field : FieldType, value1, value2) : QueryBuilder
• #or_in(field : FieldType, values : Array | Tuple) : QueryBuilder
• #or_like(field : FieldType, value) : QueryBuilder
• #or_not_between(field : FieldType, value1, value2) : QueryBuilder
• #or_not_in(field : FieldType, values : Array | Tuple) : QueryBuilder
• #or_not_like(field : FieldType, value) : QueryBuilder
• #or_not_where(field : FieldType, op_or_val, value = nil) : QueryBuilder
• #or_where(field : FieldType, op_or_val, value = nil) : QueryBuilder
• #order_by(field : FieldType, dir = nil) : QueryBuilder
• #paginate(per_page, page) : QueryBuilder

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

• #ph(position : Int) : String

  Adds placeholder for a SQL argument.

• #query(&) : QueryBuilder

  Generates a raw query.

• #query_count : Int

  Returns number of queries made by the current instance.

• #query_method : Symbol | Nil

  Returns the query method.

• #quote(field : FieldType) : String

  Wraps field with quotes (QUOTE).

• #repair(table : OneOrMoreFieldsType) : QueryBuilder

  Builds the REPAIR query

• #repair : QueryBuilder

  Builds the REPAIR query

• #reset_query

  Resets current query.

• #returning(fields : OneOrMoreFieldsType) : QueryBuilder

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

• #returning : String | Nil

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

• #returning(*name : FieldType) : QueryBuilder

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

• #right_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds RIGHT JOIN to the current query.

• #right_outer_join(table : FieldType, field1 : FieldType, field2 : FieldType | Nil = nil) : QueryBuilder

  Adds RIGHT OUTER JOIN to the current query.

• #select(fields : OneOrMoreFieldsType) : QueryBuilder

  Selects one or more fields.

• #select : String

  Returns selected field(s).

• #select(*name : FieldType) : QueryBuilder

  Selects one or more fields.

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

  Adds SUM to the current query.

• #table(name : OneOrMoreFieldsType) : QueryBuilder

  Targets one or more tables.

• #table : String

  Returns table name(s).

• #table(*name : FieldType) : QueryBuilder

  Targets tables defined by variadic arguments.

• #table(&) : QueryBuilder

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

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

  Converts NamedTuple to DataHashType.

• #update(pk_name, pk_value, data : Hash | NamedTuple) : QueryBuilder
• #update(table : OneOrMoreFieldsType, data : Hash | NamedTuple) : QueryBuilder
• #update(data : Hash | NamedTuple) : QueryBuilder
• #where(field : FieldType, op_or_val, value = nil, type = "", and_or = "AND") : QueryBuilder

  Where clause.

• #where(&) : QueryBuilder

  Adds a raw #where to current query.

Constructor Detail

Instance Method Detail

sql, args = builder.build

builder.find.where(pk_name, pk_value)

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

SELECT * FROM "posts" AS p, articles a

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

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

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

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

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

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

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

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

puts builder.select

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

builder.table(:table1, :table2)

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

SELECT * FROM "posts" AS p, articles a

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

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