Class: ROM::SQL::Relation

Inherits:
Relation
  • Object
show all
Includes:
ROM::SQL, Reading, Writing
Defined in:
lib/rom/sql/relation.rb,
lib/rom/sql/relation/reading.rb,
lib/rom/sql/relation/writing.rb

Overview

Sequel-specific relation extensions

Defined Under Namespace

Modules: Reading, Writing

Class Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Class Attribute Details

.current_gateway ⇒ Object Originally defined in module ROM::SQL

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.

Class Method Details

.associations ⇒ 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. 



107
108
109
 
# File 'lib/rom/sql/relation.rb', line 107

def self.associations
  schema.associations
end

.define_default_views! ⇒ 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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/rom/sql/relation.rb', line 69

def self.define_default_views!
  if schema.primary_key.size > 1
    # @!method by_pk(val1, val2)
    #   Return a relation restricted by its composite primary key
    #
    #   @param [Array] args A list with composite pk values
    #
    #   @return [SQL::Relation]
    #
    #   @api public
    class_eval <<-RUBY, __FILE__, __LINE__ + 1
      def by_pk(#{schema.primary_key.map(&:name).join(', ')})
        where(#{schema.primary_key.map { |attr| "self.class.schema[:#{attr.name}] => #{attr.name}" }.join(', ')})
      end
    RUBY
  else
    # @!method by_pk(pk)
    #   Return a relation restricted by its primary key
    #
    #   @param [Object] pk The primary key value
    #
    #   @return [SQL::Relation]
    #
    #   @api public
    class_eval <<-RUBY, __FILE__, __LINE__ + 1
      def by_pk(pk)
        if primary_key.nil?
          raise MissingPrimaryKeyError.new(
            "Missing primary key for :\#{schema.name}"
          )
        end
        where(self.class.schema[self.class.schema.primary_key_name].qualified => pk)
      end
    RUBY
  end
end

.inherited(klass) ⇒ 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.

Set default dataset for a relation sub-class



34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/rom/sql/relation.rb', line 34

def self.inherited(klass)
  super

  klass.class_eval do
    schema_inferrer -> (name, gateway) do
      inferrer_for_db = ROM::SQL::Schema::Inferrer.get(gateway.connection.database_type.to_sym)
      begin
        inferrer_for_db.new.call(name, gateway)
      rescue Sequel::Error => e
        inferrer_for_db.on_error(klass, e)
        ROM::Schema::DEFAULT_INFERRER.()
      end
    end

    dataset do
      # TODO: feels strange to do it here - we need a new hook for this during finalization
      klass.define_default_views!
      schema = klass.schema

      table = opts[:from].first

      if db.table_exists?(table)
        if schema
          select(*schema.map(&:to_sql_name)).order(*schema.project(*schema.primary_key_names).qualified.map(&:to_sql_name))
        else
          select(*columns).order(*klass.primary_key_columns(db, table))
        end
      else
        self
      end
    end
  end
end

.primary_key_columns(db, table) ⇒ 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. 



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

def self.primary_key_columns(db, table)
  names = db.respond_to?(:primary_key) ? Array(db.primary_key(table)) : [:id]
  names.map { |col| :"#{table}__#{col}" }
end

Instance Method Details

#assoc(name) ⇒ Relation

Return relation that will load associated tuples of this relation

This method is useful for defining custom relation views for relation composition when you want to enhance default association query

Examples:

assoc(:tasks).where(tasks[:title] => "Task One")

Parameters:

  • name (Symbol) —

    The association name

Returns:



132
133
134
 
# File 'lib/rom/sql/relation.rb', line 132

def assoc(name)
  associations[name].(__registry__)
end

#avg(*args) ⇒ Object Originally defined in module Reading

Returns a result of SQL AVG clause.

Examples:

users.avg(:age)

Parameters:

  • *args (Array<Symbol>) —

    A list with column names

Returns:

  • Number

#columns ⇒ Array<Symbol>

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.

Return raw column names

Returns:

  • (Array<Symbol>) 


141
142
143
 
# File 'lib/rom/sql/relation.rb', line 141

def columns
  @columns ||= dataset.columns
end

#count ⇒ Relation Originally defined in module Reading

Return relation count

Examples:

users.count
# => 12

Returns:

#delete(*args, &block) ⇒ Relation Originally defined in module Writing

Delete tuples from the relation

Examples:

users.delete # deletes all
users.where(name: 'Jane').delete # delete tuples
                                   from restricted relation

Returns:

#distinct(*columns) ⇒ Relation #distinct(&block) ⇒ Relation Originally defined in module Reading

Returns a copy of the relation with a SQL DISTINCT clause.

Overloads:

  • #distinct(*columns) ⇒ Relation

    Create a distinct statement from column names

    Examples:

    users.distinct(:country)

    Parameters:

    • columns (Array<Symbol>) —

      A list with column names

  • #distinct(&block) ⇒ Relation

    Create a distinct statement from a block

    Examples:

    users.distinct { func(id) }
# SELECT DISTINCT ON (count("id")) "id" ...

Returns:

#exclude(*args, &block) ⇒ Relation Originally defined in module Reading

Restrict a relation to not match criteria

Examples:

users.exclude(name: 'Jane')

Parameters:

  • *args (Hash) —

    A hash with conditions for exclusion

Returns:

#exist?(*args, &block) ⇒ TrueClass, FalseClass Originally defined in module Reading

Checks whether a relation has at least one tuple

@example
  users.where(name: 'John').exist? # => true

  users.exist?(name: 'Klaus') # => false

  users.exist? { name.is('klaus') } # => false

 @param [Array<Object>] args Optional restrictions to filter the relation
 @yield An optional block filters the relation using `where DSL`

Returns:

  • (TrueClass, FalseClass)

#fetch(pk) ⇒ Relation Originally defined in module Reading

Fetch a tuple identified by the pk

Examples:

users.fetch(1)
# {:id => 1, name: "Jane"}

Returns:

Raises:

  • (ROM::TupleCountMismatchError) —

    When 0 or more than 1 tuples were found

#first ⇒ Hash Originally defined in module Reading

Get first tuple from the relation

Examples:

users.first
# {:id => 1, :name => "Jane"}

Returns:

  • (Hash)

#group(*columns) ⇒ Relation #group(*attributes) ⇒ Relation #group(*attributes, &block) ⇒ Relation Originally defined in module Reading

Group by specific columns

Overloads:

  • #group(*columns) ⇒ Relation

    Return a new relation grouped by provided columns

    Examples:

    tasks.group(:user_id)

    Parameters:

    • columns (Array<Symbol>) —

      A list with column names

  • #group(*attributes) ⇒ Relation

    Return a new relation grouped by provided schema attributes

    Examples:

    tasks.group(tasks[:id], tasks[:title])

    Parameters:

  • #group(*attributes, &block) ⇒ Relation

    Return a new relation grouped by provided attributes from a block

    Examples:

    tasks.group(tasks[:id]) { title.qualified }

    Parameters:

    • attributes (Array<SQL::Attributes>) —

      A list with relation attributes

Returns:

#group_and_count(*args, &block) ⇒ Relation Originally defined in module Reading

Group by specific columns and count by group

Examples:

tasks.group_and_count(:user_id)
# => [{ user_id: 1, count: 2 }, { user_id: 2, count: 3 }]

Parameters:

  • *args (Array<Symbol>) —

    A list of column names

Returns:

#group_append(*columns) ⇒ Relation #group_append(*attributes) ⇒ Relation #group_append(*attributes, &block) ⇒ Relation Originally defined in module Reading

Group by more columns

Overloads:

  • #group_append(*columns) ⇒ Relation

    Return a new relation grouped by provided columns

    Examples:

    tasks.group_append(:user_id)

    Parameters:

    • columns (Array<Symbol>) —

      A list with column names

  • #group_append(*attributes) ⇒ Relation

    Return a new relation grouped by provided schema attributes

    Examples:

    tasks.group_append(tasks[:id], tasks[:title])
  • #group_append(*attributes, &block) ⇒ Relation

    Return a new relation grouped by provided schema attributes from a block

    Examples:

    tasks.group_append(tasks[:id]) { id.qualified }

    Parameters:

Returns:

#having(conditions) ⇒ Relation #having(&block) ⇒ Relation Originally defined in module Reading

Restrict a relation to match grouping criteria

Overloads:

  • #having(conditions) ⇒ Relation

    Return a new relation with having clause from conditions hash

    Examples:

    users.
  qualified.
  left_join(tasks).
  select { [id, name, int::count(:tasks__id).as(:task_count)] }.
  group(users[:id].qualified).
  having(task_count: 2)
  first
# {:id => 1, :name => "Jane", :task_count => 2}

    Parameters:

    • conditions (Hash) —

      A hash with conditions

  • #having(&block) ⇒ Relation

    Return a new relation with having clause created from restriction DSL

    Examples:

    users.
  qualified.
  left_join(tasks).
  select { [id, name, int::count(:tasks__id).as(:task_count)] }.
  group(users[:id].qualified).
  having { count(id.qualified) >= 1 }.
  first
# {:id => 1, :name => "Jane", :task_count => 2}

Returns:

#insert(*args, &block) ⇒ Relation Originally defined in module Writing

Insert tuple into relation

Examples:

users.insert(name: 'Jane')

Parameters:

  • tuple (Hash)

Returns:

#invert ⇒ Relation Originally defined in module Reading

Inverts the current WHERE and HAVING clauses. If there is neither a WHERE or HAVING clause, adds a WHERE clause that is always false.

Examples:

users.exclude(name: 'Jane').invert

# this is the same as:
users.where(name: 'Jane')

Returns:

#join(dataset, join_conditions) ⇒ Relation #join(dataset, join_conditions, options) ⇒ Relation #join(relation) ⇒ Relation Also known as: inner_join Originally defined in module Reading

Join with another relation using INNER JOIN

Overloads:

  • #join(dataset, join_conditions) ⇒ Relation

    Join with another relation using dataset name and join conditions

    Examples:

    users.join(:tasks, id: :user_id)

    Parameters:

    • dataset (Symbol) —

      Join table name

    • join_conditions (Hash) —

      A hash with join conditions

  • #join(dataset, join_conditions, options) ⇒ Relation

    Join with another relation using dataset name and join conditions with additional join options

    Examples:

    users.join(:tasks, { id: :user_id }, { table_alias: :tasks_1 })

    Parameters:

    • dataset (Symbol) —

      Join table name

    • join_conditions (Hash) —

      A hash with join conditions

    • options (Hash) —

      Additional join options

  • #join(relation) ⇒ Relation

    Join with another relation

    Join conditions are automatically set based on schema association

    Examples:

    users.join(tasks)

    Parameters:

    • relation (Relation) —

      A relation for join

Returns:

#last ⇒ Hash Originally defined in module Reading

Get last tuple from the relation

Examples:

users.last
# {:id => 2, :name => "Joe"}

Returns:

  • (Hash)

#left_join(dataset, left_join_conditions) ⇒ Relation #left_join(dataset, left_join_conditions, options) ⇒ Relation #left_join(relation) ⇒ Relation Originally defined in module Reading

Join with another relation using LEFT OUTER JOIN

Overloads:

  • #left_join(dataset, left_join_conditions) ⇒ Relation

    Left_Join with another relation using dataset name and left_join conditions

    Examples:

    users.left_join(:tasks, id: :user_id)

    Parameters:

    • dataset (Symbol) —

      Left_Join table name

    • left_join_conditions (Hash) —

      A hash with left_join conditions

  • #left_join(dataset, left_join_conditions, options) ⇒ Relation

    Left_Join with another relation using dataset name and left_join conditions with additional left_join options

    Examples:

    users.left_join(:tasks, { id: :user_id }, { table_alias: :tasks_1 })

    Parameters:

    • dataset (Symbol) —

      Left_Join table name

    • left_join_conditions (Hash) —

      A hash with left_join conditions

    • options (Hash) —

      Additional left_join options

  • #left_join(relation) ⇒ Relation

    Left_Join with another relation

    Left_Join conditions are automatically set based on schema association

    Examples:

    users.left_join(tasks)

    Parameters:

    • relation (Relation) —

      A relation for left_join

Returns:

#limit(num) ⇒ Relation #limit(num, offset) ⇒ Relation Originally defined in module Reading

Limit a relation to a specific number of tuples

Overloads:

  • #limit(num) ⇒ Relation

    Return a new relation with the limit set to the provided num

    Examples:

    users.limit(1)

    Parameters:

    • num (Integer) —

      The limit value

  • #limit(num, offset) ⇒ Relation

    Return a new relation with the limit set to the provided num

    Examples:

    users.limit(10, 2)

    Parameters:

    • num (Integer) —

      The limit value

    • offset (Integer) —

      The offset value

Returns:

#lock(options) ⇒ SQL::Relation #lock(options) {|relation| ... } ⇒ Object Originally defined in module Reading

Lock rows with in the specified mode. Check out ROW_LOCK_MODES for the list of supported modes, keep in mind available lock modes heavily depend on the database type+version you’re running on.

Overloads:

  • #lock(options) ⇒ SQL::Relation

    Options Hash (options):

    • :mode (Symbol) —

      Lock mode

    • :wait (Boolean, Integer) —

      Controls the (NO)WAIT part

    • :skip_locked (Boolean) —

      Skip locked rows

    • :of (Array, Symbol, String) —

      List of objects in the OF part

    Returns:

  • #lock(options) {|relation| ... } ⇒ Object

    Runs the block inside a transaction. The relation will be materialized and passed inside the block so that the lock will be acquired right before the block gets executed.

    Parameters:

    • options (Hash) —

      The same options as for the version without a block

    Yield Parameters:

    • relation (Array)

#map(key = nil, &block) ⇒ Object Originally defined in module Reading

Map tuples from the relation

Examples:

users.map { |user| user[:id] }
# [1, 2, 3]

users.map(:id).to_a
# [1, 2, 3]

Parameters:

  • key (Symbol) (defaults to: nil) —

    An optional name of the key for extracting values from tuples

#max(*args) ⇒ Object Originally defined in module Reading

Returns a result of SQL MAX clause.

Examples:

users.max(:age)

Parameters:

  • *args (Array<Symbol>) —

    A list with column names

Returns:

  • Number

#min(*args) ⇒ Object Originally defined in module Reading

Returns a result of SQL MIN clause.

Examples:

users.min(:age)

Parameters:

  • *args (Array<Symbol>) —

    A list with column names

Returns:

  • Number

#multi_insert(*args, &block) ⇒ Relation Originally defined in module Writing

Multi insert tuples into relation

Examples:

users.multi_insert([{name: 'Jane'}, {name: 'Jack'}])

Parameters:

  • tuples (Array)

Returns:

#offset(num) ⇒ Relation Originally defined in module Reading

Set offset for the relation

Examples:

users.limit(10).offset(2)

Parameters:

  • num (Integer) —

    The offset value

Returns:

#order(*columns) ⇒ Relation #order(*attributes) ⇒ Relation #order(&block) ⇒ Relation Originally defined in module Reading

Set order for the relation

Overloads:

  • #order(*columns) ⇒ Relation

    Return a new relation ordered by provided columns (ASC by default)

    Examples:

    users.order(:name, :id)

    Parameters:

    • columns (Array<Symbol>) —

      A list with column names

  • #order(*attributes) ⇒ Relation

    Return a new relation ordered by provided schema attributes

    Examples:

    users.order(self[:name].qualified.desc, self[:id].qualified.desc)

    Parameters:

    • attributes (Array<SQL::Attribute>) —

      A list with schema attributes

  • #order(&block) ⇒ Relation

    Return a new relation ordered using order DSL

    Examples:

    using attribute

    users.order { id.desc }
users.order { price.desc(nulls: :first) }

    using a function

    users.order { nullif(name.qualified, `''`).desc(nulls: :first) }

Returns:

#pluck(name) ⇒ Array Originally defined in module Reading

Pluck values from a specific column

Examples:

users.pluck(:id)
# [1, 2, 3]

Returns:

  • (Array)

#prefix(name = Dry::Core::Inflector.singularize(schema.name.dataset)) ⇒ Relation Originally defined in module Reading

Prefix all columns in a relation

This method is intended to be used internally within a relation object

Examples:

users.prefix(:user).to_a
# {:user_id => 1, :user_name => "Jane"}

Parameters:

  • name (Symbol) (defaults to: Dry::Core::Inflector.singularize(schema.name.dataset)) —

    The prefix

Returns:

#qualified ⇒ Relation Originally defined in module Reading

Qualifies all columns in a relation

This method is intended to be used internally within a relation object

Examples:

users.qualified.dataset.sql
# SELECT "users"."id", "users"."name" ...

Returns:

#qualified_columns ⇒ Array<Symbol> Originally defined in module Reading

Return a list of qualified column names

This method is intended to be used internally within a relation object

Examples:

users.qualified_columns
# [:users__id, :users__name]

Returns:

  • (Array<Symbol>)

#read(sql) ⇒ SQL::Relation Originally defined in module Reading

Return a new relation from a raw SQL string

Examples:

users.read('SELECT name FROM users')

Parameters:

  • sql (String) —

    The SQL string

Returns:

#rename(options) ⇒ Relation Originally defined in module Reading

Rename columns in a relation

This method is intended to be used internally within a relation object

Examples:

users.rename(name: :user_name).first
# {:id => 1, :user_name => "Jane" }

Parameters:

  • options (Hash<Symbol=>Symbol>) —

    A name => new_name map

Returns:

#reverse(*args, &block) ⇒ Relation Originally defined in module Reading

Reverse the order of the relation

Examples:

users.order(:name).reverse

Returns:

#right_join(dataset, right_join_conditions) ⇒ Relation #right_join(dataset, right_join_conditions, options) ⇒ Relation #right_join(relation) ⇒ Relation Originally defined in module Reading

Join with another relation using RIGHT JOIN

Overloads:

  • #right_join(dataset, right_join_conditions) ⇒ Relation

    Right_Join with another relation using dataset name and right_join conditions

    Examples:

    users.right_join(:tasks, id: :user_id)

    Parameters:

    • dataset (Symbol) —

      Right_Join table name

    • right_join_conditions (Hash) —

      A hash with right_join conditions

  • #right_join(dataset, right_join_conditions, options) ⇒ Relation

    Right_Join with another relation using dataset name and right_join conditions with additional right_join options

    Examples:

    users.right_join(:tasks, { id: :user_id }, { table_alias: :tasks_1 })

    Parameters:

    • dataset (Symbol) —

      Right_Join table name

    • right_join_conditions (Hash) —

      A hash with right_join conditions

    • options (Hash) —

      Additional right_join options

  • #right_join(relation) ⇒ Relation

    Right_Join with another relation

    Right_Join conditions are automatically set based on schema association

    Examples:

    users.right_join(tasks)

    Parameters:

    • relation (Relation) —

      A relation for right_join

Returns:

#select(*columns) ⇒ Relation #select(*attributes) ⇒ Relation #select(&block) ⇒ Relation #select(*columns, &block) ⇒ Relation Also known as: project Originally defined in module Reading

Select specific columns for select clause

Overloads:

  • #select(*columns) ⇒ Relation

    Project relation using column names

    Examples:

    using column names

    users.select(:id, :name).first
# {:id => 1, :name => "Jane"}

    Parameters:

    • columns (Array<Symbol>) —

      A list of column names

  • #select(*attributes) ⇒ Relation

    Project relation using schema attributes

    Examples:

    using attributes

    users.select(:id, :name).first
# {:id => 1, :name => "Jane"}

    using schema

    users.select(*schema.project(:id)).first
# {:id => 1}

    Parameters:

  • #select(&block) ⇒ Relation

    Project relation using projection DSL

    Examples:

    using attributes

    users.select { id.as(:user_id) }
# {:user_id => 1}

users.select { [id, name] }
# {:id => 1, :name => "Jane"}

    using SQL functions

    users.select { string::concat(id, '-', name).as(:uid) }.first
# {:uid => "1-Jane"}
  • #select(*columns, &block) ⇒ Relation

    Project relation using column names and projection DSL

    Examples:

    using attributes

    users.select(:id) { int::count(id).as(:count) }.group(:id).first
# {:id => 1, :count => 1}

users.select { [id, name] }
# {:id => 1, :name => "Jane"}

    Parameters:

Returns:

#select_append(*args, &block) ⇒ Relation Originally defined in module Reading

Append specific columns to select clause

Returns:

See Also:

#select_group(*args, &block) ⇒ Relation Originally defined in module Reading

Select and group by specific columns

Examples:

tasks.select_group(:user_id)
# => [{ user_id: 1 }, { user_id: 2 }]

Parameters:

  • *args (Array<Symbol>) —

    A list of column names

Returns:

#sum(*args) ⇒ Integer Originally defined in module Reading

Returns a result of SQL SUM clause.

Examples:

users.sum(:age)

Parameters:

  • *args (Array<Symbol>) —

    A list with column names

Returns:

  • (Integer)

#union(relation, options = EMPTY_HASH, &block) ⇒ Relation Originally defined in module Reading

Adds a UNION clause for relation dataset using second relation dataset

Examples:

users.where(id: 1).union(users.where(id: 2))
# => [{ id: 1, name: 'Piotr' }, { id: 2, name: 'Jane' }]

Parameters:

  • relation (Relation) —

    Another relation

  • options (Hash) (defaults to: EMPTY_HASH) —

    Options for union

Options Hash (options):

  • :alias (Symbol) —

    Use the given value as the #from_self alias

  • :all (TrueClass, FalseClass) —

    Set to true to use UNION ALL instead of UNION, so duplicate rows can occur

  • :from_self (TrueClass, FalseClass) —

    Set to false to not wrap the returned dataset in a #from_self, use with care.

Returns:

#unique?(criteria) ⇒ TrueClass, FalseClass Originally defined in module Reading

Return if a restricted relation has 0 tuples

Examples:

users.unique?(email: '[email protected]') # true

users.insert(email: '[email protected]')

users.unique?(email: '[email protected]') # false

Parameters:

  • criteria (Hash) —

    The condition hash for WHERE clause

Returns:

  • (TrueClass, FalseClass)

#update(*args, &block) ⇒ Relation Originally defined in module Writing

Update tuples in the relation

Examples:

users.update(name: 'Jane')
users.where(name: 'Jane').update(name: 'Jane Doe')

Returns:

#upsert(*args, &block) ⇒ Object Originally defined in module Writing

Add upsert option (only PostgreSQL >= 9.5) Uses internal Sequel implementation Default - ON CONFLICT DO NOTHING more options: sequel.jeremyevans.net/rdoc-adapters/classes/Sequel/Postgres/DatasetMethods.html#method-i-insert_conflict

Examples:

users.upsert({ name: 'Jane', email: '[email protected]' },
             { target: :email, update: { name: :excluded__name } }

#where(conditions) ⇒ Relation #where(conditions, &block) ⇒ Relation #where(&block) ⇒ Relation Originally defined in module Reading

Restrict a relation to match criteria

Overloads:

  • #where(conditions) ⇒ Relation

    Restrict a relation using a hash with conditions

    Examples:

    users.where(name: 'Jane', age: 30)

    Parameters:

    • conditions (Hash) —

      A hash with conditions

  • #where(conditions, &block) ⇒ Relation

    Restrict a relation using a hash with conditions and restriction DSL

    Examples:

    users.where(name: 'Jane') { age > 18 }

    Parameters:

    • conditions (Hash) —

      A hash with conditions

  • #where(&block) ⇒ Relation

    Restrict a relation using restriction DSL

    Examples:

    users.where { age > 18 }
users.where { (id < 10) | (id > 20) }

Returns: