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

API:

  • public

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.

API:

  • private

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

Methods included from AttributeWrapping

#wrapped, #wrapped?

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.

API:

  • private



281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/rom/sql/function.rb', line 281

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.

API:

  • private



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:

API:

  • public



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 between boolean SQL expressions to arbitrary SQL expressions

Returns:

API:

  • public



200
201
202
203
204
205
206
207
 
# File 'lib/rom/sql/function.rb', line 200

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:

  • Expression to be cast

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

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

Returns:

API:

  • public



185
186
187
 
# File 'lib/rom/sql/function.rb', line 185

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:

  • (defaults to: Undefined)

    Conditions

Yields:

  • (block) —

    A block with restrictions

Returns:

See Also:

API:

  • public



224
225
226
227
228
229
230
231
232
233
 
# File 'lib/rom/sql/function.rb', line 224

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

  super(conditions)
end

#is(other) ⇒ Object

See Also:

API:

  • public



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.

API:

  • private



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

def name
  self.alias || super
end

#new ⇒ 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.

API:

  • private



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

def new(&)
  case func
  when ::Sequel::SQL::Function
    meta(func: ::Sequel::SQL::Function.new!(func.name, func.args.map(&), func.opts))
  else
    meta(func: func)
  end
end

#not(other) ⇒ Object

See Also:

API:

  • public



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:

  • a customizable set of options

  • a customizable set of options

  • a customizable set of options

Returns:

See Also:

API:

  • public



168
169
170
 
# File 'lib/rom/sql/function.rb', line 168

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:

API:

  • private



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:

See Also:

API:

  • private



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:

API:

  • private



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.

API:

  • private



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:

  • A list of expressions for sorting within a group

Yields:

  • (block) —

    A block for getting the expressions using the Order DSL

Returns:

See Also:

API:

  • public



249
250
251
252
253
254
255
256
257
 
# File 'lib/rom/sql/function.rb', line 249

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

  super(*group)
end