class Clear::SQL::SelectQuery

Overview

A Select Query builder

Remember of PostgreSQL Select query:

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

Included Modules

Defined in:

clear/sql/select_query.cr

Instance Method Summary

Instance methods inherited from module Clear::SQL::SelectBuilder

before_query(&block : -> Nil) before_query, columns : Array(SQL::Column) columns, default_wildcard_table default_wildcard_table, dup : self dup, havings : Array(Clear::Expression::Node) havings, is_distinct? is_distinct?, joins : Array(SQL::Join) joins, limit : Int64? limit, lock : String? lock, offset : Int64? offset, order_bys : Array(Clear::SQL::Query::OrderBy::Record) order_bys, to_delete to_delete, to_sql : String to_sql, to_update to_update, total_entries : Int64? total_entries, total_entries=(total_entries : Int64?) total_entries=, wheres : Array(Clear::Expression::Node) wheres

Constructor methods inherited from module Clear::SQL::SelectBuilder

new(distinct_value = nil, cte = {} of String => Clear::SQL::Query::CTE::Record, columns = [] of SQL::Column, forced_columns = [] of SQL::Column, froms = [] of SQL::From, joins = [] of SQL::Join, wheres = [] of Clear::Expression::Node, havings = [] of Clear::Expression::Node, windows = [] of ::Tuple(String, String), group_bys = [] of Symbolic, order_bys = [] of Clear::SQL::Query::OrderBy::Record, limit = nil, offset = nil, lock = nil, before_query_triggers = [] of (-> Nil)) new

Instance methods inherited from module Clear::SQL::Query::WithPagination

current_page current_page, first_page? first_page?, last_page? last_page?, next_page next_page, out_of_bounds? out_of_bounds?, paginate(page : Int32 = DEFAULT_PAGE, per_page : Int32 = DEFAULT_LIMIT) paginate, per_page per_page, previous_page previous_page, total_pages total_pages

Instance methods inherited from module Clear::SQL::Query::Change

change! : self change!

Instance methods inherited from module Clear::SQL::Query::Connection

connection_name : String connection_name, use_connection(connection_name : String) use_connection

Instance methods inherited from module Clear::SQL::Query::Pluck

pluck(fields : Tuple(*T)) forall T
pluck(*fields)
pluck(**fields : **T) forall T
pluck
, pluck_col(field : Clear::SQL::Symbolic, type : T.class) forall T
pluck_col(field : Clear::SQL::Symbolic)
pluck_col

Instance methods inherited from module Clear::SQL::Query::Fetch

fetch(fetch_all = false, &block : Hash(String, Clear::SQL::Any) -> Nil) fetch, fetch_first fetch_first, fetch_first! fetch_first!, fetch_with_cursor(count = 1000, &block : Hash(String, Clear::SQL::Any) -> Nil) fetch_with_cursor, first first, first! first!, scalar(type : T.class) forall T scalar, to_a : Array(Hash(String, Clear::SQL::Any)) to_a

Instance methods inherited from module Clear::SQL::Query::Execute

execute(connection_name : String? = nil) execute

Instance methods inherited from module Clear::SQL::Query::Lock

clear_lock clear_lock, with_lock(str : String = "FOR UPDATE") with_lock

Instance methods inherited from module Clear::SQL::Query::Window

clear_windows clear_windows, print_windows print_windows, window(name, value)
window(windows : NamedTuple)
window
, windows : Array(WindowDeclaration) windows

Instance methods inherited from module Clear::SQL::Query::CTE

cte : Hash(String, Clear::SQL::Query::CTE::Record) cte, with_cte(name, request : CTEAuthorized, recursive = false)
with_cte(tuple : NamedTuple)
with_cte
, with_recursive_cte(tuple : NamedTuple) with_recursive_cte

Instance methods inherited from module Clear::SQL::Query::Aggregate

agg(field, x : X.class) forall X agg, avg(field, x : X.class) forall X avg, count(type : X.class = Int64) forall X count, max(field, x : X.class) forall X max, min(field, x : X.class) forall X min, sum(field, x : X.class) forall X
sum(field) : Float64
sum

Instance methods inherited from module Clear::SQL::Query::OffsetLimit

clear_limit clear_limit, clear_offset clear_offset, limit(x : Int?) limit, offset(x : Int?) offset

Instance methods inherited from module Clear::SQL::Query::GroupBy

clear_group_bys clear_group_bys, group_by(*column_list) group_by, group_bys : Array(Symbolic) group_bys

Instance methods inherited from module Clear::SQL::Query::OrderBy

clear_order_bys clear_order_bys, order_by(__tuple : NamedTuple)
order_by(expression : Symbol, direction : Symbol = :asc, nulls : Symbol? = nil)
order_by(expression : String, direction : Symbol = :asc, nulls : Symbol? = nil)
order_by(**tuple)
order_by
, reverse_order_by reverse_order_by

Instance methods inherited from module Clear::SQL::Query::Having

clear_havings clear_havings, having(node : Clear::Expression::Node)
having(&)
having(__conditions : NamedTuple | Hash(String, Clear::SQL::Any))
having(__template : String, *__args)
having(__template : String, **__tuple)
having(**tuple)
having
, or_having(node : Clear::Expression::Node)
or_having(__template : String, *__args)
or_having(__template : String, **__named_tuple)
or_having(&)
or_having

Instance methods inherited from module Clear::SQL::Query::Where

clear_wheres clear_wheres, or_where(node : Clear::Expression::Node)
or_where(__template : String, *__args)
or_where(__template : String, **__named_tuple)
or_where(&)
or_where
, where(node : Clear::Expression::Node)
where(&)
where(__conditions : NamedTuple | Hash(String, Clear::SQL::Any))
where(__template : String, *__args)
where(__template : String, **__tuple)
where(**tuple)
where

Instance methods inherited from module Clear::SQL::Query::Join

cross_join(name : Selectable, lateral = false) cross_join, full_outer_join(name : Selectable, lateral = false, &)
full_outer_join(name : Selectable, condition : String = "true", lateral = false)
full_outer_join
, inner_join(name : Selectable, lateral = false, &)
inner_join(name : Selectable, condition : String = "true", lateral = false)
inner_join
, join(name : Selectable, type = :inner, lateral = false, &)
join(name : Selectable, type = :inner, condition : String = "true", lateral = false)
join(name : Selectable, type = :inner, lateral = false)
join
, left_join(name : Selectable, lateral = false, &)
left_join(name : Selectable, condition : String = "true", lateral = false)
left_join
, right_join(name : Selectable, lateral = false, &)
right_join(name : Selectable, condition : String = "true", lateral = false)
right_join

Instance methods inherited from module Clear::SQL::Query::From

clear_from clear_from, from(*__args)
from(**__named_tuple)
from
, froms : Array(SQL::From) froms

Instance methods inherited from module Clear::SQL::Query::Select

clear_distinct clear_distinct, clear_force_select clear_force_select, clear_select clear_select, distinct(on : String? = "") distinct, distinct_value : String? distinct_value, force_select(c : Column)
force_select(*__args)
force_select(**__tuple)
force_select
, select(*__args)
select(**__tuple)
select
, set_default_table_wildcard(table : String? = nil) set_default_table_wildcard

Instance Method Detail

def count(&block) #
Description copied from module Enumerable(Hash(String, Array(JSON::Any) | Array(PG::BoolArray) | Array(PG::CharArray) | Array(PG::Float32Array) | Array(PG::Float64Array) | Array(PG::Int16Array) | Array(PG::Int32Array) | Array(PG::Int64Array) | Array(PG::NumericArray) | Array(PG::StringArray) | Array(PG::TimeArray) | BigDecimal | Bool | Char | Clear::Expression::UnsafeSql | Float32 | Float64 | Hash(String, JSON::Any) | Int16 | Int32 | Int64 | Int8 | JSON::Any | PG::Geo::Box | PG::Geo::Circle | PG::Geo::Line | PG::Geo::LineSegment | PG::Geo::Path | PG::Geo::Point | PG::Geo::Polygon | PG::Interval | PG::Numeric | Slice(UInt8) | String | Time | UInt16 | UInt32 | UInt64 | UInt8 | UUID | Nil))

Returns the number of elements in the collection for which the passed block returns true.

[1, 2, 3, 4].count { |i| i % 2 == 0 } # => 2

[View source]
def each(&) #

Enumerable items


[View source]
def size #
Description copied from module Enumerable(Hash(String, Array(JSON::Any) | Array(PG::BoolArray) | Array(PG::CharArray) | Array(PG::Float32Array) | Array(PG::Float64Array) | Array(PG::Int16Array) | Array(PG::Int32Array) | Array(PG::Int64Array) | Array(PG::NumericArray) | Array(PG::StringArray) | Array(PG::TimeArray) | BigDecimal | Bool | Char | Clear::Expression::UnsafeSql | Float32 | Float64 | Hash(String, JSON::Any) | Int16 | Int32 | Int64 | Int8 | JSON::Any | PG::Geo::Box | PG::Geo::Circle | PG::Geo::Line | PG::Geo::LineSegment | PG::Geo::Path | PG::Geo::Point | PG::Geo::Polygon | PG::Interval | PG::Numeric | Slice(UInt8) | String | Time | UInt16 | UInt32 | UInt64 | UInt8 | UUID | Nil))

Returns the number of elements in the collection.

[1, 2, 3, 4].size # => 4

[View source]