Class: ROM::SQL::Function

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

Overview

Specialized attribute type for defining SQL functions

Constant Summary collapse

WINDOW_FRAMES =

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

Hash.new do |cache, frame|
  type = frame.key?(:rows) ? "ROWS" : "RANGE"
  bounds = frame[:rows] || frame[:range]
  cache[frame] =
    "#{type} BETWEEN #{frame_limit(bounds[0])} AND #{frame_limit(bounds[1])}"
end

Class Method Summary collapse

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. 



263
264
265
266
267
268
269
270
271
272
273
 
# File 'lib/rom/sql/function.rb', line 263

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

Class Method Details

.frame_limit(value) ⇒ Object

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. 



16
17
18
19
20
21
22
23
24
25
26
27
28
 
# 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.positive?
      "#{value} FOLLOWING"
    else
      "#{value.abs} PRECEDING"
    end
  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:



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

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:



191
192
193
194
195
196
197
198
 
# File 'lib/rom/sql/function.rb', line 191

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:



176
177
178
 
# File 'lib/rom/sql/function.rb', line 176

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) }.unordered
users.project { integer::count(:id).filter { name.is("John") }).as(:johns) }.ordered

Parameters:

  • condition (Hash, SQL::Attribute) (defaults to: Undefined) —

    Conditions

Yields:

  • (block) —

    A block with restrictions

Returns:

See Also:



215
216
217
218
219
220
221
222
223
224
 
# File 'lib/rom/sql/function.rb', line 215

def filter(condition = Undefined, &block)
  if block
    conditions = schema.restriction(&block)
    conditions &= condition unless condition.equal?(Undefined)
  else
    conditions = condition
  end

  super(conditions)
end

#is(other) ⇒ Object

See Also:



111
112
113
114
115
 
# File 'lib/rom/sql/function.rb', line 111

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

#name ⇒ Object

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. 



69
70
71
 
# File 'lib/rom/sql/function.rb', line 69

def name
  self.alias || super
end

#new(&block) ⇒ Object

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. 



92
93
94
95
96
97
98
99
 
# File 'lib/rom/sql/function.rb', line 92

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

See Also:



120
121
122
 
# File 'lib/rom/sql/function.rb', line 120

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:



160
161
162
 
# File 'lib/rom/sql/function.rb', line 160

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

#qualified(table_alias = nil) ⇒ Object

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.

See Also:



76
77
78
79
80
 
# File 'lib/rom/sql/function.rb', line 76

def qualified(table_alias = nil)
  new { |arg|
    arg.respond_to?(:qualified) ? arg.qualified(table_alias) : arg
  }
end

#qualified?(_table_alias = nil) ⇒ Boolean

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.

Returns:

  • (Boolean)

See Also:



104
105
106
 
# File 'lib/rom/sql/function.rb', line 104

def qualified?(_table_alias = nil)
  meta[:func].args.all?(&:qualified?)
end

#qualified_projection(table_alias = nil) ⇒ Object

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.

See Also:



85
86
87
88
89
 
# File 'lib/rom/sql/function.rb', line 85

def qualified_projection(table_alias = nil)
  new { |arg|
    arg.respond_to?(:qualified_projection) ? arg.qualified_projection(table_alias) : arg
  }
end

#sql_literal(ds) ⇒ Object

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. 



60
61
62
63
64
65
66
 
# File 'lib/rom/sql/function.rb', line 60

def sql_literal(ds)
  if name
    ds.literal(func.as(name))
  else
    ds.literal(func)
  end
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:

  • args (Array) —

    A list of expressions for sorting within a group

Yields:

  • (block) —

    A block for getting the expressions using the Order DSL

Returns:

See Also:



240
241
242
243
244
245
246
247
248
 
# File 'lib/rom/sql/function.rb', line 240

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

  super(*group)
end

#wrapped(name = source.dataset) ⇒ Attribute Originally defined in module AttributeWrapping

Return attribute type wrapped for the specified relation name

Parameters:

  • name (Symbol) (defaults to: source.dataset) —

    The name of the source relation (defaults to source.dataset)

Returns:

#wrapped? ⇒ Boolean Originally defined in module AttributeWrapping

Return if the attribute type is from a wrapped relation

Wrapped attributes are used when two schemas from different relations are merged together. This way we can identify them easily and handle correctly in places like auto-mapping.

Returns:

  • (Boolean)