Class: ROM::SQL::Function

Inherits:
Attribute
  • Object
show all
Defined in:
lib/rom/sql/function.rb

Overview

Specialized attribute type for defining SQL functions

Instance Method Summary collapse

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(meth, *args) ⇒ Object (private)

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. 



243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/rom/sql/function.rb', line 243

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

Instance Method Details

#aliased(alias_name) ⇒ SQL::Function Also known as: as

Return a new attribute with an alias

Examples:

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

Returns:



53
54
55
 
# File 'lib/rom/sql/function.rb', line 53

def aliased(alias_name)
  super.with(name: name || alias_name)
end

#case(mapping) ⇒ ROM::SQL::Attribute

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

Examples:

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

Parameters:

  • mapping (Hash) —

    mapping between boolean SQL expressions to arbitrary SQL expressions

Returns:



171
172
173
174
175
176
177
178
 
# File 'lib/rom/sql/function.rb', line 171

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)) ⇒ ROM::SQL::Attribute

Convert an expression result to another data type

Examples:

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

Parameters:

  • expr (ROM::SQL::Attribute) —

    Expression to be cast

  • db_type (String) (defaults to: TypeSerializer[:default].call(type)) —

    Target database type (usually can be inferred from the target data type)

Returns:



156
157
158
 
# File 'lib/rom/sql/function.rb', line 156

def cast(expr, db_type = TypeSerializer[:default].call(type))
  Attribute[type].meta(sql_expr: ::Sequel.cast(expr, db_type))
end

#filter(condition = Undefined) {|block| ... } ⇒ SQL::Function

Add a FILTER clause to aggregate function (supported by PostgreSQL 9.4+) Filter aggregate using the specified conditions

Examples:

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

Parameters:

Yields:

  • (block) —

    A block with restrictions

Returns:

See Also:



195
196
197
198
199
200
201
202
203
204
 
# File 'lib/rom/sql/function.rb', line 195

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) ⇒ Object

See Also:



91
92
93
94
95
 
# File 'lib/rom/sql/function.rb', line 91

def is(other)
  ::ROM::SQL::Attribute[::ROM::SQL::Types::Bool].meta(
    sql_expr: ::Sequel::SQL::BooleanExpression.new(:'=', func, other)
  )
end

#not(other) ⇒ Object

See Also:



100
101
102
 
# File 'lib/rom/sql/function.rb', line 100

def not(other)
  !is(other)
end

#over(partition: nil, order: nil, frame: nil) ⇒ SQL::Function

Add an OVER clause making a window function call

Examples:

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

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

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

Parameters:

  • :partition (Hash) —

    a customizable set of options

  • :order (Hash) —

    a customizable set of options

  • :frame (Hash) —

    a customizable set of options

Returns:

See Also:



140
141
142
 
# File 'lib/rom/sql/function.rb', line 140

def over(partition: nil, order: nil, frame: nil)
  super(partition: partition, order: order, frame: WINDOW_FRAMES[frame])
end

#within_group(*args) {|block| ... } ⇒ SQL::Function

Add a WITHIN GROUP clause to aggregate function (supported by PostgreSQL) Establishes an order for an ordered-set aggregate, see the docs for more details

Examples:

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

Parameters:

  • A (Array) —

    list of expressions for sorting within a group

Yields:

  • (block) —

    A block for getting the expressions using the Order DSL

Returns:

See Also:



220
221
222
223
224
225
226
227
228
 
# File 'lib/rom/sql/function.rb', line 220

def within_group(*args, &block)
  if block
    group = args + ::ROM::SQL::OrderDSL.new(schema).(&block)
  else
    group = args
  end

  super(*group)
end