class ROM::SQL::Function

Specialized attribute type for defining SQL functions

@api public

Constants

WINDOW_FRAMES

@api private

Private Class Methods

frame_limit(value) click to toggle source

@api private

# File lib/rom/sql/function.rb, line 16
def frame_limit(value)
  case value
  when :current then 'CURRENT ROW'
  when :start then 'UNBOUNDED PRECEDING'
  when :end then 'UNBOUNDED FOLLOWING'
  else
    if value > 0
      "#{ value } FOLLOWING"
    else
      "#{ value.abs } PRECEDING"
    end
  end
end

Public Instance Methods

aliased(alias_name) click to toggle source

Return a new attribute with an alias

@example

string::coalesce(users[:name], users[:id]).aliased(:display_name)

@return [SQL::Function]

@api public

Calls superclass method
# File lib/rom/sql/function.rb, line 53
def aliased(alias_name)
  super.with(name: name || alias_name)
end
Also aliased as: as
as(alias_name)
Alias for: aliased
case(mapping) click to toggle source

Add a CASE clause for handling if/then logic. This version of CASE search for the first branch which evaluates to ‘true`. See SQL::Attriubte#case if you’re looking for the version that matches an expression result

@example

users.select { bool::case(status.is("active") => true, else: false).as(:activated) }

@param [Hash] mapping mapping between boolean SQL expressions to arbitrary SQL expressions @return [ROM::SQL::Attribute]

@api public

# File lib/rom/sql/function.rb, line 190
def case(mapping)
  mapping = mapping.dup
  otherwise = mapping.delete(:else) do
    raise ArgumentError, 'provide the default case using the :else keyword'
  end

  Attribute[type].meta(sql_expr: ::Sequel.case(mapping, otherwise))
end
cast(expr, db_type = TypeSerializer[:default].call(type)) click to toggle source

Convert an expression result to another data type

@example

users.select { bool::cast(json_data.get_text('activated'), :boolean).as(:activated) }
users.select { bool::cast(json_data.get_text('activated')).as(:activated) }

@param [ROM::SQL::Attribute] expr Expression to be cast @param [String] db_type Target database type (usually can be inferred from the target data type)

@return [ROM::SQL::Attribute]

@api public

# File lib/rom/sql/function.rb, line 175
def cast(expr, db_type = TypeSerializer[:default].call(type))
  Attribute[type].meta(sql_expr: ::Sequel.cast(expr, db_type))
end
filter(condition = Undefined, &block) click to toggle source

Add a FILTER clause to aggregate function (supported by PostgreSQL 9.4+) @see www.postgresql.org/docs/current/static/sql-expressions.html

Filter aggregate using the specified conditions

@example

users.project { integer::count(:id).filter(name.is("Jack")).as(:jacks) }.unordered
users.project { integer::count(:id).filter { name.is("John") }).as(:johns) }.ordered

@param condition [Hash,SQL::Attribute] Conditions @yield [block] A block with restrictions

@return [SQL::Function]

@api public

Calls superclass method
# File lib/rom/sql/function.rb, line 214
def filter(condition = Undefined, &block)
  if block
    conditions = schema.restriction(&block)
    conditions = conditions & condition unless condition.equal?(Undefined)
  else
    conditions = condition
  end

  super(conditions)
end
is(other) click to toggle source

@see ROM::SQL::Attribute#is

@api public

# File lib/rom/sql/function.rb, line 110
def is(other)
  ::ROM::SQL::Attribute[::ROM::SQL::Types::Bool].meta(
    sql_expr: ::Sequel::SQL::BooleanExpression.new(:'=', func, other)
  )
end
name() click to toggle source

@api private

Calls superclass method
# File lib/rom/sql/function.rb, line 68
def name
  self.alias || super
end
new(&block) click to toggle source

@api private

# File lib/rom/sql/function.rb, line 91
def new(&block)
  case func
  when ::Sequel::SQL::Function
    meta(func: ::Sequel::SQL::Function.new!(func.name, func.args.map(&block), func.opts))
  else
    meta(func: func)
  end
end
not(other) click to toggle source

@see ROM::SQL::Attribute#not

@api public

# File lib/rom/sql/function.rb, line 119
def not(other)
  !is(other)
end
over(partition: nil, order: nil, frame: nil) click to toggle source

Add an OVER clause making a window function call @see www.postgresql.org/docs/9.6/static/tutorial-window.html

@example

users.select { [id, integer::row_number().over(partition: name, order: id).as(:row_no)] }
users.select { [id, integer::row_number().over(partition: [first_name, last_name], order: id).as(:row_no)] }

@example frame variants

# ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
row_number.over(frame: { rows: [-3, :current] })

# ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
row_number.over(frame: { rows: [-3, 3] })

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
row_number.over(frame: { rows: [:start, :current] })

# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
row_number.over(frame: { rows: [:current, :end] })

@example frame shortcuts

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
row_number.over(frame: :all)

# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
row_number.over(frame: :rows)

# RANGE BETWEEN CURRENT ROW AND CURRENT ROW
row_number.over(frame: { range: :current} )

@option :partition [Array<SQL::Attribute>,SQL::Attribute] A PARTITION BY part @option :order [Array<SQL::Attribute>,SQL::Attribute] An ORDER BY part @option :frame [Hash,Symbol] A frame part (RANGE or ROWS, see examples) @return [SQL::Function]

@api public

Calls superclass method
# File lib/rom/sql/function.rb, line 159
def over(partition: nil, order: nil, frame: nil)
  super(partition: partition, order: order, frame: WINDOW_FRAMES[frame])
end
qualified(table_alias = nil) click to toggle source

@see Attribute#qualified

@api private

# File lib/rom/sql/function.rb, line 75
def qualified(table_alias = nil)
  new { |arg|
    arg.respond_to?(:qualified) ? arg.qualified(table_alias) : arg
  }
end
qualified?(_table_alias = nil) click to toggle source

@see Attribute#qualified?

@api private

# File lib/rom/sql/function.rb, line 103
def qualified?(_table_alias = nil)
  meta[:func].args.all?(&:qualified?)
end
qualified_projection(table_alias = nil) click to toggle source

@see Attribute#qualified_projection

@api private

# File lib/rom/sql/function.rb, line 84
def qualified_projection(table_alias = nil)
  new { |arg|
    arg.respond_to?(:qualified_projection) ? arg.qualified_projection(table_alias) : arg
  }
end
sql_literal(ds) click to toggle source

@api private

# File lib/rom/sql/function.rb, line 59
def sql_literal(ds)
  if name
    ds.literal(func.as(name))
  else
    ds.literal(func)
  end
end
within_group(*args, &block) click to toggle source

Add a WITHIN GROUP clause to aggregate function (supported by PostgreSQL) @see www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Establishes an order for an ordered-set aggregate, see the docs for more details

@example

households.project { fload::percentile_cont(0.5).within_group(income).as(:percentile) }

@param args [Array] A list of expressions for sorting within a group @yield [block] A block for getting the expressions using the Order DSL

@return [SQL::Function]

@api public

Calls superclass method
# File lib/rom/sql/function.rb, line 239
def within_group(*args, &block)
  if block
    group = args + ::ROM::SQL::OrderDSL.new(schema).(&block)
  else
    group = args
  end

  super(*group)
end

Private Instance Methods

func() click to toggle source

@api private

# File lib/rom/sql/function.rb, line 257
def func
  meta[:func]
end
method_missing(meth, *args) click to toggle source

@api private

Calls superclass method
# File lib/rom/sql/function.rb, line 262
def method_missing(meth, *args)
  if func
    if func.respond_to?(meth)
      meta(func: func.__send__(meth, *args))
    else
      super
    end
  else
    meta(func: Sequel::SQL::Function.new(meth.to_s.upcase, *args))
  end
end
schema() click to toggle source

@api private

# File lib/rom/sql/function.rb, line 252
def schema
  meta[:schema]
end