Module: ROM::SQL::Relation::Reading

Included in:
ROM::SQL::Relation
Defined in:
lib/rom/sql/relation/reading.rb

Overview

Query API for SQL::Relation

Constant Summary collapse

ROW_LOCK_MODES =

Row-level lock modes

Hash.new(update: 'FOR UPDATE'.freeze).update(
  # https://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE
  postgres: {
    update: 'FOR UPDATE'.freeze,
    no_key_update: 'FOR NO KEY UPDATE'.freeze,
    share: 'FOR SHARE'.freeze,
    key_share: 'FOR KEY SHARE'.freeze
  },
  # https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html
  mysql: {
    update: 'FOR UPDATE'.freeze,
    share: 'LOCK IN SHARE MODE'.freeze
  }
).freeze

Instance Method Summary collapse

Instance Method Details

#as_hash(attribute = primary_key) ⇒ Hash

Returns hash with all tuples being the key of each the provided attribute

Examples:

default use primary_key

users.as_hash
# {1 => {id: 1, name: 'Jane'}}

using other attribute

users.as_hash(:name)
# {'Jane' => {id: 1, name: 'Jane'}}

Returns:

  • (Hash) 


982
983
984
 
# File 'lib/rom/sql/relation/reading.rb', line 982

def as_hash(attribute = primary_key)
  dataset.as_hash(attribute)
end

#avg(*args) ⇒ Object

Returns a result of SQL AVG clause.

Examples:

users.avg(:age)

Parameters:

  • args (Array<Symbol>) —

    A list with column names

Returns:

  • Number



330
331
332
 
# File 'lib/rom/sql/relation/reading.rb', line 330

def avg(*args)
  dataset.__send__(__method__, *args)
end

#count ⇒ Relation

Return relation count

Examples:

users.count
# => 12

Returns:



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

def count
  dataset.count
end

#distinct(*columns) ⇒ Relation #distinct(&block) ⇒ Relation

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:



274
275
276
 
# File 'lib/rom/sql/relation/reading.rb', line 274

def distinct(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
end

#each_batch(size: 1000) {|| ... } ⇒ Object

Process the dataset in batches. The method yields a relation restricted by a primary key value. This means it discards any order internally and uses the PK sort. Currently, works only with a single-column primary key.

Examples:

update in batches

users.each_batch do |rel|
  rel.
    command(:update).
    call(name: users[:first_name].concat(users[:last_name])
end

Parameters:

  • [Integer] (Hash) —

    a customizable set of options

Yield Parameters:



945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
 
# File 'lib/rom/sql/relation/reading.rb', line 945

def each_batch(size: 1000)
  pks = schema.primary_key

  if pks.size > 1
    raise ArgumentError, 'Composite primary keys are not supported yet'
  end

  source = order(pks[0]).limit(size)
  rel = source

  loop do
    ids = rel.pluck(primary_key)

    break if ids.empty?

    yield(rel)

    break if ids.size < size

    rel = source.where(pks[0] > ids.last)
  end
end

#exclude(*args, &block) ⇒ Relation

Restrict a relation to not match criteria

Examples:

users.exclude(name: 'Jane')

Parameters:

  • args (Hash) —

    A hash with conditions for exclusion

Returns:



384
385
386
 
# File 'lib/rom/sql/relation/reading.rb', line 384

def exclude(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
end

#exist?(*args, &block) ⇒ TrueClass, FalseClass

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] args Optional restrictions to filter the relation @yield An optional block filters the relation using where DSL

Returns:

  • (TrueClass, FalseClass) 


839
840
841
 
# File 'lib/rom/sql/relation/reading.rb', line 839

def exist?(*args, &block)
  !where(*args, &block).limit(1).count.zero?
end

#exists(other, condition = nil) ⇒ SQL::Relation

Restrict with rows from another relation. Accepts only SQL relations and uses the EXISTS clause under the hood

Examples:

using associations

users.exists(tasks)

using provided condition

users.exists(tasks, tasks[:user_id] => users[:id])

Parameters:

  • other (SQL::Relation) —

    The other relation

  • condition (Hash, Object) (defaults to: nil) —

    An optional join condition

Returns:



924
925
926
927
 
# File 'lib/rom/sql/relation/reading.rb', line 924

def exists(other, condition = nil)
  join_condition = condition || associations[other.name].join_keys
  where(other.where(join_condition).dataset.exists)
end

#fetch(pk) ⇒ Relation

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



40
41
42
 
# File 'lib/rom/sql/relation/reading.rb', line 40

def fetch(pk)
  by_pk(pk).one!
end

#first ⇒ Hash

Get first tuple from the relation

Examples:

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

Returns:

  • (Hash) 


66
67
68
 
# File 'lib/rom/sql/relation/reading.rb', line 66

def first
  limit(1).to_a.first
end

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

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:



713
714
715
716
717
718
719
720
721
722
723
 
# File 'lib/rom/sql/relation/reading.rb', line 713

def group(*args, &block)
  if block
    if args.size > 0
      group(*args).group_append(&block)
    else
      new(dataset.__send__(__method__, *schema.canonical.group(&block)))
    end
  else
    new(dataset.__send__(__method__, *schema.canonical.project(*args)))
  end
end

#group_and_count(*args, &block) ⇒ Relation

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:



775
776
777
 
# File 'lib/rom/sql/relation/reading.rb', line 775

def group_and_count(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
end

#group_append(*columns) ⇒ Relation #group_append(*attributes) ⇒ Relation #group_append(*attributes, &block) ⇒ Relation

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:



752
753
754
755
756
757
758
759
760
761
762
 
# File 'lib/rom/sql/relation/reading.rb', line 752

def group_append(*args, &block)
  if block
    if args.size > 0
      group_append(*args).group_append(&block)
    else
      new(dataset.group_append(*schema.canonical.group(&block)))
    end
  else
    new(dataset.group_append(*args))
  end
end

#having(conditions) ⇒ Relation #having(&block) ⇒ Relation

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, integer::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, integer::count(:tasks__id).as(:task_count)] }.
  group(users[:id].qualified).
  having { count(id.qualified) >= 1 }.
  first
# {:id => 1, :name => "Jane", :task_count => 2}

Returns:



421
422
423
424
425
426
427
 
# File 'lib/rom/sql/relation/reading.rb', line 421

def having(*args, &block)
  if block
    new(dataset.having(*args, *schema.canonical.restriction(&block)))
  else
    new(dataset.__send__(__method__, *args))
  end
end

#invert ⇒ Relation

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:



441
442
443
 
# File 'lib/rom/sql/relation/reading.rb', line 441

def invert
  new(dataset.invert)
end

#join(dataset, join_conditions) ⇒ Relation #join(dataset, join_conditions, options) ⇒ Relation #join(relation) ⇒ Relation #join(relation, &block) ⇒ Relation Also known as: inner_join

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

  • #join(relation, &block) ⇒ Relation

    Join with another relation using DSL

    Examples:

    users.join(tasks) { |users:, tasks:|
  tasks[:user_id].is(users[:id]) & users[:name].is('John')
}

    Parameters:

    • relation (Relation) —

      A relation for join

Returns:



581
582
583
 
# File 'lib/rom/sql/relation/reading.rb', line 581

def join(*args, &block)
  __join__(__method__, *args, &block)
end

#last ⇒ Hash

Get last tuple from the relation

Examples:

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

Returns:

  • (Hash) 


79
80
81
 
# File 'lib/rom/sql/relation/reading.rb', line 79

def last
  reverse.limit(1).first
end

#left_join(dataset, left_join_conditions) ⇒ Relation #left_join(dataset, left_join_conditions, options) ⇒ Relation #left_join(relation) ⇒ Relation #join(relation, &block) ⇒ Relation

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

  • #join(relation, &block) ⇒ Relation

    Join with another relation using DSL

    Examples:

    users.left_join(tasks) { |users:, tasks:|
  tasks[:user_id].is(users[:id]) & users[:name].is('John')
}

    Parameters:

    • relation (Relation) —

      A relation for left_join

Returns:



631
632
633
 
# File 'lib/rom/sql/relation/reading.rb', line 631

def left_join(*args, &block)
  __join__(__method__, *args, &block)
end

#limit(num) ⇒ Relation #limit(num, offset) ⇒ Relation

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:



518
519
520
 
# File 'lib/rom/sql/relation/reading.rb', line 518

def limit(*args)
  new(dataset.__send__(__method__, *args))
end

#lock(options) ⇒ SQL::Relation #lock(options) {|relation| ... } ⇒ Object

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) 


896
897
898
899
900
901
902
903
904
905
906
 
# File 'lib/rom/sql/relation/reading.rb', line 896

def lock(**options, &block)
  clause = lock_clause(**options)

  if block
    transaction do
      block.call(dataset.lock_style(clause).to_a)
    end
  else
    new(dataset.lock_style(clause))
  end
end

#map(key = nil, &block) ⇒ Object

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



143
144
145
146
147
148
149
 
# File 'lib/rom/sql/relation/reading.rb', line 143

def map(key = nil, &block)
  if key
    dataset.map(key, &block)
  else
    dataset.map(&block)
  end
end

#max(*args) ⇒ Object

Returns a result of SQL MAX clause.

Examples:

users.max(:age)

Parameters:

  • args (Array<Symbol>) —

    A list with column names

Returns:

  • Number



316
317
318
 
# File 'lib/rom/sql/relation/reading.rb', line 316

def max(*args)
  dataset.__send__(__method__, *args)
end

#min(*args) ⇒ Object

Returns a result of SQL MIN clause.

Examples:

users.min(:age)

Parameters:

  • args (Array<Symbol>) —

    A list with column names

Returns:

  • Number



302
303
304
 
# File 'lib/rom/sql/relation/reading.rb', line 302

def min(*args)
  dataset.__send__(__method__, *args)
end

#offset(num) ⇒ Relation

Set offset for the relation

Examples:

users.limit(10).offset(2)

Parameters:

  • num (Integer) —

    The offset value

Returns:



532
533
534
 
# File 'lib/rom/sql/relation/reading.rb', line 532

def offset(num)
  new(dataset.__send__(__method__, num))
end

#order(*columns) ⇒ Relation #order(*attributes) ⇒ Relation #order(&block) ⇒ Relation

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:



476
477
478
479
480
481
482
 
# File 'lib/rom/sql/relation/reading.rb', line 476

def order(*args, &block)
  if block
    new(dataset.order(*args, *schema.canonical.order(&block)))
  else
    new(dataset.__send__(__method__, *args, &block))
  end
end

#pluck(*names) ⇒ Array

Pluck values from a specific column

Examples:

Single value

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

Multiple values

users.pluck(:id, :name)
# [[1, "Jane"] [2, "Joe"]]

Returns:

  • (Array) 


164
165
166
 
# File 'lib/rom/sql/relation/reading.rb', line 164

def pluck(*names)
  select(*names).map(names.length == 1 ? names.first : names)
end

#prefix(name = Inflector.singularize(schema.name.dataset)) ⇒ Relation

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: Inflector.singularize(schema.name.dataset)) —

    The prefix

Returns:



96
97
98
 
# File 'lib/rom/sql/relation/reading.rb', line 96

def prefix(name = Inflector.singularize(schema.name.dataset))
  schema.prefix(name).(self)
end

#qualified(table_alias = nil) ⇒ Relation

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:



111
112
113
 
# File 'lib/rom/sql/relation/reading.rb', line 111

def qualified(table_alias = nil)
  schema.qualified(table_alias).(self)
end

#qualified_columns ⇒ Array<Symbol>

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


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

def qualified_columns
  schema.qualified.map(&:to_sql_name)
end

#query ⇒ SQL::Attribute

Turn a relation into a subquery. Can be used for selecting a column with a subquery or restricting the result set with a IN (SELECT ...) condtion.

Examples:

adding number of user tasks

tasks = relations[:tasks]
users = relations[:users]
user_tasks = tasks.where(tasks[:user_id].is(users[:id]))
tasks_count = user_tasks.select { integer::count(id) }
users.select_append(tasks_count.as(:tasks_count))

Returns:



998
999
1000
1001
1002
 
# File 'lib/rom/sql/relation/reading.rb', line 998

def query
  attr = schema.to_a[0]
  subquery = schema.project(attr).(self).dataset
  SQL::Attribute[attr.type].meta(sql_expr: subquery)
end

#read(sql) ⇒ SQL::Relation

Return a new relation from a raw SQL string

Examples:

users.read('SELECT name FROM users')

Parameters:

  • sql (String) —

    The SQL string

Returns:



871
872
873
 
# File 'lib/rom/sql/relation/reading.rb', line 871

def read(sql)
  new(dataset.db[sql], schema: schema.empty)
end

#rename(options) ⇒ Relation

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:



181
182
183
 
# File 'lib/rom/sql/relation/reading.rb', line 181

def rename(options)
  schema.rename(options).(self)
end

#reverse(*args, &block) ⇒ Relation

Reverse the order of the relation

Examples:

users.order(:name).reverse

Returns:



492
493
494
 
# File 'lib/rom/sql/relation/reading.rb', line 492

def reverse(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
end

#right_join(dataset, right_join_conditions) ⇒ Relation #right_join(dataset, right_join_conditions, options) ⇒ Relation #right_join(relation) ⇒ Relation #join(relation, &block) ⇒ Relation

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

  • #join(relation, &block) ⇒ Relation

    Join with another relation using DSL

    Examples:

    users.right_join(tasks) { |users:, tasks:|
  tasks[:user_id].is(users[:id]) & users[:name].is('John')
}

    Parameters:

    • relation (Relation) —

      A relation for right_join

Returns:



680
681
682
 
# File 'lib/rom/sql/relation/reading.rb', line 680

def right_join(*args, &block)
  __join__(__method__, *args, &block)
end

#select(*columns) ⇒ Relation #select(*attributes) ⇒ Relation #select(&block) ⇒ Relation #select(*columns, &block) ⇒ Relation Also known as: project

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) { integer::count(id).as(:count) }.group(:id).first
# {:id => 1, :count => 1}

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

    Parameters:

Returns:



238
239
240
 
# File 'lib/rom/sql/relation/reading.rb', line 238

def select(*args, &block)
  schema.project(*args, &block).(self)
end

#select_append(*args, &block) ⇒ Relation

Append specific columns to select clause

Returns:

See Also:



250
251
252
 
# File 'lib/rom/sql/relation/reading.rb', line 250

def select_append(*args, &block)
  schema.merge(schema.canonical.project(*args, &block)).(self)
end

#select_group(*args, &block) ⇒ Relation

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:



790
791
792
793
 
# File 'lib/rom/sql/relation/reading.rb', line 790

def select_group(*args, &block)
  new_schema = schema.project(*args, &block)
  new_schema.(self).group(*new_schema)
end

#sum(*args) ⇒ Integer

Returns a result of SQL SUM clause.

Examples:

users.sum(:age)

Parameters:

  • args (Array<Symbol>) —

    A list with column names

Returns:

  • (Integer) 


288
289
290
 
# File 'lib/rom/sql/relation/reading.rb', line 288

def sum(*args)
  dataset.__send__(__method__, *args)
end

#unfiltered ⇒ SQL::Relation

Discard restrictions in WHERE and HAVING clauses

Examples:

calling .by_pk has no effect

users.by_pk(1).unfiltered

Returns:



1012
1013
1014
 
# File 'lib/rom/sql/relation/reading.rb', line 1012

def unfiltered
  new(dataset.__send__(__method__))
end

#union(relation, options = EMPTY_HASH, &block) ⇒ Object

Adds a UNION clause for relation dataset using second relation dataset

@returRelation]

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.



811
812
813
814
815
816
817
818
819
820
821
822
 
# File 'lib/rom/sql/relation/reading.rb', line 811

def union(relation, options = EMPTY_HASH, &block)
  # We use the original relation name here if both relations have the
  # same name. This makes it so if the user at some point references
  # the relation directly by name later on things won't break in
  # confusing ways.
  same_relation = name == relation.name
  alias_name =  same_relation ? name : "#{name.to_sym}__#{relation.name.to_sym}"
  opts = { alias: alias_name.to_sym, **options }

  new_schema = schema.qualified(opts[:alias])
  new_schema.(new(dataset.__send__(__method__, relation.dataset, opts, &block)))
end

#unique?(criteria) ⇒ TrueClass, FalseClass

Return if a restricted relation has 0 tuples

Examples:

users.unique?(email: 'jane@doe.org') # true

users.insert(email: 'jane@doe.org')

users.unique?(email: 'jane@doe.org') # false

Parameters:

  • criteria (Hash) —

    The condition hash for WHERE clause

Returns:

  • (TrueClass, FalseClass) 


857
858
859
 
# File 'lib/rom/sql/relation/reading.rb', line 857

def unique?(criteria)
  !exist?(criteria)
end

#where(conditions) ⇒ Relation #where(conditions, &block) ⇒ Relation #where(&block) ⇒ Relation

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:



362
363
364
365
366
367
368
369
370
371
372
 
# File 'lib/rom/sql/relation/reading.rb', line 362

def where(*args, &block)
  if block
    where(*args).where(schema.canonical.restriction(&block))
  elsif args.size == 1 && args[0].is_a?(Hash)
    new(dataset.where(coerce_conditions(args[0])))
  elsif !args.empty?
    new(dataset.where(*args))
  else
    self
  end
end

#wrap(*names) ⇒ Wrap

Wrap other relations using association names

Examples:

tasks.wrap(:owner)

Parameters:

  • names (Array<Symbol>) —

    A list with association identifiers

Returns:



1026
1027
1028
1029
 
# File 'lib/rom/sql/relation/reading.rb', line 1026

def wrap(*names)
  others = names.map { |name| associations[name].wrapped }
  wrap_around(*others)
end