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

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

Overview

Query API for SQL::Relation

Instance Method Summary collapse

Instance Method Details

#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



307
308
309
 
# File 'lib/rom/sql/relation/reading.rb', line 307

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

#count ⇒ Relation

Return relation count

Examples:

users.count
# => 12

Returns:



34
35
36
 
# File 'lib/rom/sql/relation/reading.rb', line 34

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:



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

def distinct(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
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:



359
360
361
 
# File 'lib/rom/sql/relation/reading.rb', line 359

def exclude(*args, &block)
  new(dataset.__send__(__method__, *args, &block))
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



21
22
23
 
# File 'lib/rom/sql/relation/reading.rb', line 21

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) 


47
48
49
 
# File 'lib/rom/sql/relation/reading.rb', line 47

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:



658
659
660
661
662
663
664
665
666
667
668
 
# File 'lib/rom/sql/relation/reading.rb', line 658

def group(*args, &block)
  if block
    if args.size > 0
      group(*args).group_append(&block)
    else
      new(dataset.__send__(__method__, *schema.group(&block)))
    end
  else
    new(dataset.__send__(__method__, *schema.project(*args).canonical))
  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:



720
721
722
 
# File 'lib/rom/sql/relation/reading.rb', line 720

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:



697
698
699
700
701
702
703
704
705
706
707
 
# File 'lib/rom/sql/relation/reading.rb', line 697

def group_append(*args, &block)
  if block
    if args.size > 0
      group_append(*args).group_append(&block)
    else
      new(dataset.group_append(*schema.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, 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:



396
397
398
399
400
401
402
 
# File 'lib/rom/sql/relation/reading.rb', line 396

def having(*args, &block)
  if block
    new(dataset.having(*args).having(self.class.schema.restriction(&block)))
  else
    new(dataset.__send__(__method__, *args, &block))
  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:



416
417
418
 
# File 'lib/rom/sql/relation/reading.rb', line 416

def invert
  new(dataset.invert)
end

#join(dataset, join_conditions) ⇒ Relation #join(dataset, join_conditions, options) ⇒ Relation #join(relation) ⇒ 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

Returns:



546
547
548
 
# File 'lib/rom/sql/relation/reading.rb', line 546

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) 


60
61
62
 
# File 'lib/rom/sql/relation/reading.rb', line 60

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



586
587
588
 
# File 'lib/rom/sql/relation/reading.rb', line 586

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:



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

def limit(*args)
  new(dataset.__send__(__method__, *args))
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



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

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



293
294
295
 
# File 'lib/rom/sql/relation/reading.rb', line 293

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



279
280
281
 
# File 'lib/rom/sql/relation/reading.rb', line 279

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:



507
508
509
 
# File 'lib/rom/sql/relation/reading.rb', line 507

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:



451
452
453
454
455
456
457
 
# File 'lib/rom/sql/relation/reading.rb', line 451

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

#pluck(name) ⇒ Array

Pluck values from a specific column

Examples:

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

Returns:

  • (Array) 


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

def pluck(name)
  map(name)
end

#prefix(name = Dry::Core::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: Dry::Core::Inflector.singularize(schema.name.dataset)) —

    The prefix

Returns:



77
78
79
 
# File 'lib/rom/sql/relation/reading.rb', line 77

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

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



92
93
94
 
# File 'lib/rom/sql/relation/reading.rb', line 92

def qualified
  schema.qualified.(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>) 


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

def qualified_columns
  schema.qualified.map(&:to_sym)
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:



788
789
790
 
# File 'lib/rom/sql/relation/reading.rb', line 788

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:



158
159
160
 
# File 'lib/rom/sql/relation/reading.rb', line 158

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

#reverse(*args, &block) ⇒ Relation

Reverse the order of the relation

Examples:

users.order(:name).reverse

Returns:



467
468
469
 
# File 'lib/rom/sql/relation/reading.rb', line 467

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



625
626
627
 
# File 'lib/rom/sql/relation/reading.rb', line 625

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

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

    Parameters:

Returns:



215
216
217
 
# File 'lib/rom/sql/relation/reading.rb', line 215

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

#select_append(*args, &block) ⇒ Relation

Append specific columns to select clause

Returns:

See Also:



227
228
229
 
# File 'lib/rom/sql/relation/reading.rb', line 227

def select_append(*args, &block)
  schema.merge(self.class.schema.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:



735
736
737
738
 
# File 'lib/rom/sql/relation/reading.rb', line 735

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) 


265
266
267
 
# File 'lib/rom/sql/relation/reading.rb', line 265

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

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

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:



756
757
758
 
# File 'lib/rom/sql/relation/reading.rb', line 756

def union(relation, options = EMPTY_HASH, &block)
  new(dataset.__send__(__method__, relation.dataset, options, &block))
end

#unique?(criteria) ⇒ TrueClass, FalseClass

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) 


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

def unique?(criteria)
  where(criteria).count.zero?
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:



339
340
341
342
343
344
345
346
347
 
# File 'lib/rom/sql/relation/reading.rb', line 339

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