Class: ActiveRecord::Refined::BlockContext

Inherits:
Object
  • Object
show all
Defined in:
lib/active_record/refined.rb

Overview

What a block can call: the aggregates, the functions, CASE, and the escape hatches. A block is evaluated with one of these as self, so its methods are called bare -- count(:*), upper(:name) -- and each gives back an expression that compares, aliases and orders like a column does (see BlockSyntax).

Where a function is spelled differently from one database to the next, the method names the one meaning and the adapter gets its own spelling; where a database has no equivalent, the method raises NotImplementedError as the block is read, rather than leaving the database to reject the SQL.

Examples:

Author.select { [upper(:name).as(:author), count(:*).as(:posts)] }
Author.having { count(:*) > 1 }

Aggregates collapse

JSON collapse

Scalar functions collapse

Datetime value functions collapse

Grouping collapse

Conversions collapse

Window functions collapse

Escape hatches collapse

Bits collapse

Subqueries collapse

CASE collapse

Instance Method Summary collapse

Constructor Details

#initialize(model) ⇒ BlockContext

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.

The model is only consulted to learn which adapter the query is being built for, which is what decides how a scalar function is spelled.



166
167
168
 
# File 'lib/active_record/refined.rb', line 166

def initialize(model)
  @model = model
end

Instance Method Details

#abs(x) ⇒ AST::Function

ABS(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#acos(x) ⇒ AST::Function

ACOS(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#all(relation) ⇒ AST::Quantified

ALL (subquery), on the right of a comparison: true of the rows the comparison holds for every row of the subquery. SQLite has none.

Examples:

Post.where { :likes >= all(Post.select(:likes)) }

Parameters:

  • relation (ActiveRecord::Relation)

Returns:



714
715
716
 
# File 'lib/active_record/refined.rb', line 714

def all(relation)
  quantified("ALL", relation)
end

#any(relation) ⇒ AST::Quantified

ANY (subquery), on the right of a comparison: true of the rows the comparison holds for any row of the subquery. SQLite has none. ANY and ALL quantify a comparison over a subquery, which is what a scalar subquery cannot do: it has to return the one row. == any is IN and != all is NOT IN, so what these add is the four comparisons IN has no spelling for.

Examples:

Post.where { :likes > any(Post.published.select(:likes)) }

Parameters:

  • relation (ActiveRecord::Relation)

Returns:



704
705
706
 
# File 'lib/active_record/refined.rb', line 704

def any(relation)
  quantified("ANY", relation)
end

#asin(x) ⇒ AST::Function

ASIN(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#atan(x) ⇒ AST::Function

ATAN(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#atan2(y, x) ⇒ AST::Function

ATAN2(y, x): ATN2 on SQL Server.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#avg(column, distinct: false) ⇒ AST::Aggregate

AVG(column), or AVG(DISTINCT column).

Returns:



185
186
187
 
# File 'lib/active_record/refined.rb', line 185

AGGREGATE_FUNCTIONS = {
  sum: :sum, avg: :average, min: :minimum, max: :maximum,
}.freeze

#bit_and(column) ⇒ AST::Function

BIT_AND(column), an aggregate. PostgreSQL and MySQL have it.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#bit_count(expr) ⇒ AST::Function

BIT_COUNT(expr), the bits set in a number. MySQL and PostgreSQL have it; SQLite, Oracle and SQL Server do not. MySQL counts the bits of a number; PostgreSQL counts those of a bit string, so the argument is cast, and to bit(64) because that is what makes a negative come back as MySQL has it -- 64 bits of two's complement rather than as many as the column happens to be wide.

Examples:

Post.select { bit_count(:flags).as(:set) }

Returns:



676
677
678
 
# File 'lib/active_record/refined.rb', line 676

def bit_count(expr)
  dialect.bit_count(expr, @model)
end

#bit_or(column) ⇒ AST::Function

BIT_OR(column), an aggregate. PostgreSQL and MySQL have it.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#bit_xor(column) ⇒ AST::Function

BIT_XOR(column), an aggregate. PostgreSQL and MySQL have it.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#case(operand = nil) ⇒ AST::Case

CASE, in either shape: with an operand each when is compared against, or without one, each when carrying its own condition. case is a keyword, so this one is reached as self.case; the shorthands :age.when(...) and #case_when need no receiver.

Examples:

self.case(:age).when(10).then(1).else(0)
self.case.when { :age >= 60 }.then { :age - 60 }

Returns:



765
766
767
 
# File 'lib/active_record/refined.rb', line 765

def case(operand = nil)
  AST::Case.new(operand)
end

#case_when(value = nil, &block) ⇒ AST::Case::When

The searched CASE, started at its first when: each when is a condition, as a value or a block, and then and else give the values.

Examples:

Author.select { case_when { :age >= 60 }.then("senior").else("adult").as(:band) }
Author.select { sum(case_when { :age >= 60 }.then(1).else(0)).as(:seniors) }

Returns:



776
777
778
 
# File 'lib/active_record/refined.rb', line 776

def case_when(value = nil, &block)
  AST::Case.new.when(value, &block)
end

#cast(expr, type) ⇒ AST::Cast

CAST(expr AS type). The type is the adapter's own name for it -- decimal(10,2), double precision -- and has to look like one; whether it exists is the database's to say.

Examples:

Post.select { cast(:price, "decimal(10,2)").as(:price) }

Parameters:

  • type (Symbol, String)

Returns:



557
558
559
 
# File 'lib/active_record/refined.rb', line 557

def cast(expr, type)
  AST::Cast.new(expr, type)
end

#ceil(x) ⇒ AST::Function

CEIL(x): CEILING on SQL Server.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#char_length(string) ⇒ AST::Function

CHAR_LENGTH(string): LENGTH on SQLite and Oracle, LEN on SQL Server.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#coalesce(*values) ⇒ AST::Function

COALESCE(a, b, ...): the first that is not NULL.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#concat(*strings) ⇒ AST::Function

CONCAT(a, b, ...). Oracle's takes exactly two.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#cos(x) ⇒ AST::Function

COS(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#count(column, distinct: false) ⇒ AST::Aggregate

COUNT(column); :* for COUNT(*), distinct: true for COUNT(DISTINCT column). Every aggregate takes AST::Aggregate#filter for the rows it is taken over, and AST::Windowing#over for a window.

Examples:

Author.group { :country }.having { count(:*) > 1 }
Post.select { count(:author_id, distinct: true) }
Author.select { count(:*).filter { :age < 50 }.as(:young) }

Parameters:

Returns:



211
212
213
 
# File 'lib/active_record/refined.rb', line 211

def count(column, distinct: false)
  AST::Aggregate.new(column, :count, distinct: distinct)
end

#cube(*columns) ⇒ AST::GroupingSets

GROUP BY CUBE (a, b): every subtotal there is. PostgreSQL has it; the others do not.

Examples:

Sale.group { cube(:region, :product) }

Returns:



543
544
545
 
# File 'lib/active_record/refined.rb', line 543

def cube(*columns)
  grouping(:cube, columns)
end

#cume_dist ⇒ AST::WindowFunction

CUME_DIST(); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#current_date ⇒ AST::DatetimeValueFunction

CURRENT_DATE, today in the session's zone -- UTC where Active Record has set it so. Takes no precision. SQL Server has none.

Examples:

Task.where { :due_on < current_date }

Returns:



470
471
472
 
# File 'lib/active_record/refined.rb', line 470

def current_date
  AST::DatetimeValueFunction.new(dialect.function_name(:current_date, @model))
end

#current_time(precision = nil) ⇒ AST::DatetimeValueFunction

CURRENT_TIME. SQL Server has none.

Returns:



461
462
463
 
# File 'lib/active_record/refined.rb', line 461

DATETIME_VALUE_FUNCTIONS = %i[
  current_date current_time current_timestamp localtime localtimestamp
].freeze

#current_timestamp(precision = nil) ⇒ AST::DatetimeValueFunction

CURRENT_TIMESTAMP, the server's clock in the session's zone; the portable spelling of what #now means. A precision -- current_timestamp(3) -- goes into parentheses, which SQLite and SQL Server refuse.

Examples:

Post.where { :published_at <= current_timestamp }
Post.where { :created_at > current_timestamp - 7.days }

Returns:



461
462
463
 
# File 'lib/active_record/refined.rb', line 461

DATETIME_VALUE_FUNCTIONS = %i[
  current_date current_time current_timestamp localtime localtimestamp
].freeze

#date_trunc(field, timestamp) ⇒ AST::Function

date_trunc('day', timestamp). PostgreSQL has it; the others do not.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#degrees(x) ⇒ AST::Function

DEGREES(x). Oracle has none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#dense_rank ⇒ AST::WindowFunction

DENSE_RANK(); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#excluded(column) ⇒ AST::Node

The row an upsert could not insert, in the block upsert_all takes: "excluded"."column" on PostgreSQL and SQLite, VALUES(column) on MySQL.

Examples:

Tally.upsert_all(rows, unique_by: :page) { { hits: :hits + excluded(:hits) } }

Parameters:

  • column (Symbol)

Returns:



750
751
752
 
# File 'lib/active_record/refined.rb', line 750

def excluded(column)
  dialect.excluded(column, @model)
end

#exists?(relation) ⇒ AST::Exists

EXISTS (subquery). The subquery is a relation, which may refer to the outer row through a qualified column.

Examples:

Author.where { exists?(Post.where { :posts[:author_id] == :authors[:id] }) }

Parameters:

  • relation (ActiveRecord::Relation)

Returns:



689
690
691
 
# File 'lib/active_record/refined.rb', line 689

def exists?(relation)
  AST::Exists.new(relation)
end

#exp(x) ⇒ AST::Function

EXP(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#extract(field, expr) ⇒ AST::Extract

EXTRACT(field FROM expr): a year, a month, a day of a date. The field is a keyword and has to be a plain name. SQLite and SQL Server have none. The field is a keyword, not a value, so it has to be a plain name; the node checks it. SQLite spells all of this as strftime formats, which no renaming carries, so it raises there -- after the node is built, so that a bad field is an ArgumentError on every adapter.

Examples:

Post.where { extract(:year, :created_at) == 2026 }

Parameters:

  • field (Symbol, String) —

    :year, :month, :day, :hour, ...

Returns:



501
502
503
504
505
506
507
508
 
# File 'lib/active_record/refined.rb', line 501

def extract(field, expr)
  node = AST::Extract.new(field, expr)
  unless dialect.extract_supported?
    raise NotImplementedError,
      "extract has no equivalent on #{@model.connection_db_config.adapter}"
  end
  node
end

#first_value(expr) ⇒ AST::WindowFunction

FIRST_VALUE(expr); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#floor(x) ⇒ AST::Function

FLOOR(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#fn(name, *args) ⇒ AST::Function

Any function by name: fn(:date_part, "year", :created_at). The name is written as given -- so a case-sensitive one can be spelled exactly -- and has to be a plain name, optionally qualified by a schema; the arguments are quoted as values unless they are columns or expressions. The name is emitted as written, so a case-sensitive one can be spelled exactly, and for that reason it has to be a plain name, optionally qualified by a schema; anything else is refused rather than written into the SQL.

Examples:

Post.select { fn(:date_part, "year", :created_at).as(:year) }

Parameters:

  • name (Symbol, String)

Returns:



646
647
648
649
 
# File 'lib/active_record/refined.rb', line 646

def fn(name, *args)
  AST::Function.new(
    AST.check_name(name, AST::FUNCTION_NAME, "function name").to_s, args)
end

#format(template, *values) ⇒ AST::Function

printf-style FORMAT(template, ...). PostgreSQL and SQLite have it; MySQL's FORMAT is a different function, reached through #fn.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#greatest(*values) ⇒ AST::Function

GREATEST(a, b, ...): MAX on SQLite.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#grouping_sets(*sets) ⇒ AST::GroupingSets

GROUP BY GROUPING SETS ((a), (b), ()): several groupings in one query, an empty set for the grand total. PostgreSQL has it; the others do not. Arel has the nodes and writes them for PostgreSQL alone, so what it would raise elsewhere says nothing; this says it here, as extract does, while the block is being read.

Examples:

Sale.group { grouping_sets([:region], [:product], []) }

Parameters:

Returns:



524
525
526
 
# File 'lib/active_record/refined.rb', line 524

def grouping_sets(*sets)
  grouping(:grouping_sets, sets)
end

#json_array(*values) ⇒ AST::JsonBuild

A JSON array built in the row from the values given. SQL Server spells the pair its own way and is not carried yet.

Examples:

Post.select { json_array(:title, :likes).as(:pair) }

Returns:



256
257
258
 
# File 'lib/active_record/refined.rb', line 256

def json_array(*values)
  AST::JsonBuild.new(:array, values)
end

#json_arrayagg(value) ⇒ AST::JsonAggregate

The rows of a group gathered into one JSON array, a value from each: jsonb_agg on PostgreSQL, json_group_array on SQLite, JSON_ARRAYAGG on the MySQL family and Oracle; SQL Server has none. What it gives is JSON, which compares as a dug value does.

Examples:

Post.group { :author_id }.select { json_arrayagg(:title).as(:titles) }

Returns:



222
223
224
 
# File 'lib/active_record/refined.rb', line 222

def json_arrayagg(value)
  AST::JsonAggregate.new(:arrayagg, [value])
end

#json_object(pairs = {}) ⇒ AST::JsonBuild

A JSON object built in the row from a hash whose values are expressions. SQL Server spells the pair its own way and is not carried yet.

Examples:

Post.select { json_object(title: :title, stars: :meta.dig(:stars)).as(:doc) }

Parameters:

  • pairs (Hash{Symbol, String => Object}) (defaults to: {})

Returns:



267
268
269
 
# File 'lib/active_record/refined.rb', line 267

def json_object(pairs = {})
  AST::JsonBuild.new(:object, pairs)
end

#json_objectagg(key, value) ⇒ AST::JsonAggregate

The rows of a group gathered into one JSON object, a key and a value from each, named as #json_arrayagg is; SQL Server has none.

Examples:

Post.select { json_objectagg(:title, :meta.dig(:stars)).as(:stars) }

Returns:



231
232
233
 
# File 'lib/active_record/refined.rb', line 231

def json_objectagg(key, value)
  AST::JsonAggregate.new(:objectagg, [key, value])
end

#lag(expr, offset = 1, default = nil) ⇒ AST::WindowFunction

LAG(expr, offset, default): the value offset rows before this one; needs over. The offset is written out rather than left to default, so that a default value cannot end up where the offset belongs.

Examples:

Post.select { (:likes - lag(:likes).over.order(:created_at)).as(:gain) }

Returns:



618
619
620
 
# File 'lib/active_record/refined.rb', line 618

def lag(expr, offset = 1, default = nil)
  AST::WindowFunction.new("LAG", default.nil? ? [expr, offset] : [expr, offset, default])
end

#last_value(expr) ⇒ AST::WindowFunction

LAST_VALUE(expr); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#lead(expr, offset = 1, default = nil) ⇒ AST::WindowFunction

LEAD(expr, offset, default): the value offset rows after this one; needs over.

Returns:



625
626
627
 
# File 'lib/active_record/refined.rb', line 625

def lead(expr, offset = 1, default = nil)
  AST::WindowFunction.new("LEAD", default.nil? ? [expr, offset] : [expr, offset, default])
end

#least(*values) ⇒ AST::Function

LEAST(a, b, ...): MIN on SQLite.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#length(string) ⇒ AST::Function

LENGTH(string): LEN on SQL Server. What it counts is the family's own -- bytes on MySQL, characters elsewhere, and LEN leaves trailing spaces out; #char_length is the portable count.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#ln(x) ⇒ AST::Function

LN(x): LOG on SQL Server.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#localtime(precision = nil) ⇒ AST::DatetimeValueFunction

LOCALTIME. SQLite and SQL Server have none.

Returns:



461
462
463
 
# File 'lib/active_record/refined.rb', line 461

DATETIME_VALUE_FUNCTIONS = %i[
  current_date current_time current_timestamp localtime localtimestamp
].freeze

#localtimestamp(precision = nil) ⇒ AST::DatetimeValueFunction

LOCALTIMESTAMP. SQLite and SQL Server have none.

Returns:



461
462
463
 
# File 'lib/active_record/refined.rb', line 461

DATETIME_VALUE_FUNCTIONS = %i[
  current_date current_time current_timestamp localtime localtimestamp
].freeze

#log(base, x) ⇒ AST::Function

LOG(base, x). SQL Server takes the arguments the other way round, and is refused.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#log10(x) ⇒ AST::Function

LOG10(x). Oracle has none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#log2(x) ⇒ AST::Function

LOG2(x). PostgreSQL and Oracle have none -- log(2, x) is their spelling -- and SQL Server has neither.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#lower(string) ⇒ AST::Function

LOWER(string).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#ltrim(string) ⇒ AST::Function

LTRIM(string).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#max(column) ⇒ AST::Aggregate

MAX(column).

Returns:



185
186
187
 
# File 'lib/active_record/refined.rb', line 185

AGGREGATE_FUNCTIONS = {
  sum: :sum, avg: :average, min: :minimum, max: :maximum,
}.freeze

#min(column) ⇒ AST::Aggregate

MIN(column).

Returns:



185
186
187
 
# File 'lib/active_record/refined.rb', line 185

AGGREGATE_FUNCTIONS = {
  sum: :sum, avg: :average, min: :minimum, max: :maximum,
}.freeze

#mod(x, y) ⇒ AST::Function

MOD(x, y). SQL Server has only the % operator.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#now ⇒ AST::Function

NOW(). SQLite, Oracle and SQL Server have none; #current_timestamp reaches all three.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#nth_value(expr, nth) ⇒ AST::WindowFunction

NTH_VALUE(expr, nth); needs over.

Returns:



606
607
608
 
# File 'lib/active_record/refined.rb', line 606

def nth_value(expr, nth)
  AST::WindowFunction.new("NTH_VALUE", [expr, nth])
end

#ntile(buckets) ⇒ AST::WindowFunction

NTILE(buckets); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#nullif(x, y) ⇒ AST::Function

NULLIF(x, y): NULL where the two are equal, x otherwise.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#op(operator, left, right) ⇒ AST::Operation

Any binary operator by its spelling: op("&&", :tags, "{ruby,sql}"). The operator has to be made of operator characters; the operands are quoted as values unless they are columns or expressions, and parenthesized, since the operator's precedence is not known.

Examples:

Post.where { op("&&", :tags, "{ruby,sql}") }   # PostgreSQL arrays

Parameters:

  • operator (String)

Returns:



659
660
661
 
# File 'lib/active_record/refined.rb', line 659

def op(operator, left, right)
  AST::Operation.new(operator, left, right)
end

#percent_rank ⇒ AST::WindowFunction

PERCENT_RANK(); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#pi ⇒ AST::Function

PI(). Oracle has none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#power(x, y) ⇒ AST::Function

POWER(x, y).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#radians(x) ⇒ AST::Function

RADIANS(x). Oracle has none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#rand ⇒ AST::Function

RAND(), a random number per row: RANDOM() on PostgreSQL and SQLite. Oracle and SQL Server have none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#rank ⇒ AST::WindowFunction

RANK(); needs over.

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#replace(string, from, to) ⇒ AST::Function

REPLACE(string, from, to).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#rollup(*columns) ⇒ AST::GroupingSets

GROUP BY ROLLUP (a, b): subtotals up the list and a grand total. PostgreSQL has it, and the MySQL family as WITH ROLLUP trailing the group list, which the node spells there.

Examples:

Sale.group { rollup(:region, :product) }

Returns:



534
535
536
 
# File 'lib/active_record/refined.rb', line 534

def rollup(*columns)
  grouping(:rollup, columns)
end

#round(x, places = 0) ⇒ AST::Function

ROUND(x, places).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#row_number ⇒ AST::WindowFunction

ROW_NUMBER(). Means nothing without AST::Windowing#over, and says so.

Examples:

Author.select { row_number.over.partition(:country).order(:age.desc).as(:rank) }

Returns:



596
597
598
 
# File 'lib/active_record/refined.rb', line 596

%i[row_number rank dense_rank percent_rank cume_dist].each do |name|
  define_method(name) { AST::WindowFunction.new(name.to_s.upcase, []) }
end

#rtrim(string) ⇒ AST::Function

RTRIM(string).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#sign(x) ⇒ AST::Function

SIGN(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#sin(x) ⇒ AST::Function

SIN(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#sql(statement, *binds) ⇒ AST::Sql

SQL as written, the one way a string means SQL inside a block. ? and :name placeholders take quoted values, as where takes them.

Examples:

Post.where { sql("length(title) > ?", 10) }
Post.select { sql("count(*) FILTER (WHERE score > 0) AS positive") }

Parameters:

  • statement (String)

Returns:



728
729
730
 
# File 'lib/active_record/refined.rb', line 728

def sql(statement, *binds)
  AST::Sql.new(statement, binds)
end

#sqrt(x) ⇒ AST::Function

SQRT(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#string_agg(value, separator = ",") ⇒ AST::StringAggregate

The strings of a group joined into one, a separator between: STRING_AGG on PostgreSQL and SQL Server, group_concat on SQLite, GROUP_CONCAT on MySQL, LISTAGG on Oracle. Takes AST::StringAggregate#order for the order they are joined in.

Examples:

Post.group { :author_id }.
  select { string_agg(:title, ", ").order(:title).as(:titles) }

Parameters:

  • separator (String) (defaults to: ",") —

    the comma GROUP_CONCAT defaults to, unless given

Returns:



244
245
246
 
# File 'lib/active_record/refined.rb', line 244

def string_agg(value, separator = ",")
  AST::StringAggregate.new(value, separator)
end

#substr(string, from, length = nil) ⇒ AST::Function

SUBSTR(string, from, length): SUBSTRING on SQL Server, which insists on the length.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#sum(column, distinct: false) ⇒ AST::Aggregate

SUM(column), or SUM(DISTINCT column).

Returns:



185
186
187
 
# File 'lib/active_record/refined.rb', line 185

AGGREGATE_FUNCTIONS = {
  sum: :sum, avg: :average, min: :minimum, max: :maximum,
}.freeze

#tan(x) ⇒ AST::Function

TAN(x).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#trim(string) ⇒ AST::Function

TRIM(string).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#trunc(x, places = 0) ⇒ AST::Function

TRUNC(x, places): TRUNCATE on MySQL, which insists on the places. SQL Server has none.

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#upper(string) ⇒ AST::Function

UPPER(string).

Returns:



418
419
420
421
422
423
 
# File 'lib/active_record/refined.rb', line 418

SCALAR_FUNCTIONS = %i[
  abs acos asin atan atan2 ceil coalesce concat cos exp floor length ln
  log lower ltrim mod nullif power replace round rtrim sign sin sqrt
  substr tan trim upper degrees radians pi char_length greatest least
  log2 log10 trunc now bit_and bit_or bit_xor date_trunc rand format
].freeze

#value(literal) ⇒ AST::Value

A literal where an expression is expected, quoted like any other value. A number or a string takes as for itself -- 0.as(:depth) -- so this is the spelling for the rest: true, nil, a date.

Examples:

Node.select { [:id, value(0).as(:depth)] }
Post.select { [:title, value(nil).as(:score)] }

Returns:



739
740
741
 
# File 'lib/active_record/refined.rb', line 739

def value(literal)
  AST::Value.new(literal)
end