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 Method Summary collapse

Instance Method Summary collapse

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. 



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

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
 
# 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| "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
    define_method(:by_pk) do |pk|
      where(schema[primary_key] => pk)
    end
  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



32
33
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 32

def self.inherited(klass)
  super

  klass.class_eval do
    schema_dsl SQL::Schema::DSL

    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_sym)).order(*schema.project(*schema.primary_key_names).qualified.map(&:to_sym))
        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. 



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

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:



125
126
127
 
# File 'lib/rom/sql/relation.rb', line 125

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


134
135
136
 
# File 'lib/rom/sql/relation.rb', line 134

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:

#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:

#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: